如何创建多个单词搜索？ SQL

Question

We have made a search field where you can search for ingredients and find recipes. For now you can only type in 1 ingredient:

if (isset($_POST['search'])) {
$searchquery = $_POST['search'];


$query = mysql_query("SELECT * FROM opskrifter WHERE id IN 
(SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$searchquery'))") or die("search failed");

We want to be able to search for multiple ingredients in the same search field by seperating the ingredients with a "," or something like this.

Is there a simple way to make that happen ?

EDIT: We tried to use explode like this without succes.

$searchTerms = explode(' ', $searchquery);
$searchTermBits = array();
foreach ($searchTerms as $term) {
if (!empty($term)) {
    $searchTermBits[] = "ing_name '$term'";
}}

...
$result = mysql_query("SELECT * FROM opskrifter WHERE id IN (SELECT * FROM  WHERE ".implode(' AND ', $searchTermBits)));

Thanks! :)

Answer 1

You could simply get the user to type in his values comma-separated, the the input would be almost in the right syntax for the query. You just have to add semicolons around the values because you search for a string in your table.

You can use PHP's str_replace()-Function:

$vals = $_POST['search'];
$valsFormatted = "'" . str_replace(",", "','", $vals) . "'";

In this code, you replace all the commas of the input with the comma plus semicolons before and behind them in orderto wrap all values of the input with semicolons. You also have to add one at the beginning and at the end of the string. Replace the first comma in the function above with the char you want your users to separate the values with.

After that, you can simply change your query to the following:

$query = "SELECT * FROM opskrifter WHERE id IN 
(SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$valsFormatted'))";

Please also be informed, that your code like this is vulnerable for SQL Injections! Check out this link to learn how to prevent this.