How to create multiple word search? 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! :)

Could you give an example? We tried to use an explode but then we didn't get any results at all.. @t-n-y — jacob assoignon, Jan 09 '17 at 14:26
Don't use `mysql_*` . They have been removed in version 7 for good reason. — apokryfos, Jan 09 '17 at 14:26

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

A simple statement like this would work:

$array = implode("','",explode($_POST['search'], ","));
$query = mysql_query("SELECT * FROM opskrifter WHERE id IN (SELECT opskrifterid FROM ingredienser WHERE ing_name IN ({$array}))") or die("search failed");

First explode your search, then implode it (might not even need to do so). After that make sure the array gets used as the 'in' operator as a string/array.

For more information about this, you could read this question: PHP/MySQL using an array in WHERE clause

The working copy from my local machine was this;

$_POST['search'] = "0, 1, 2";

$array = implode ( "','", explode ( ",", $_POST['search'] ) );
$query = mysql_query("SELECT * FROM users WHERE id IN ('$array')") or die(mysql_error());
var_dump ( $array );
var_dump ( $query );
var_dump ( "SELECT * FROM users WHERE id IN ('$array')" );
var_dump ( mysql_fetch_array ( $query ) );

which actually did return users, so if we would take this example and change it to your code, it would be (the query, at least):

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

Do take note of the changed $array variable too.

edited May 23 '17 at 12:01

Community

1
1

answered Jan 09 '17 at 14:34

Tosfera

512
3
14

strange.. might want to change the implode to something like this; implode("%','%", explode(",", $_POST['search'])). Notice how I changed the order of the explode, will update the question too. – Tosfera Jan 09 '17 at 14:57
When we use mysql_error() we get the error message 'Unknown column'. It interprets that our searchword is a column... – jacob assoignon Jan 09 '17 at 15:15
might have to replace {$array} with '$array' then, it really sucks that I can't test it right now :p – Tosfera Jan 09 '17 at 15:19
Same output :/ sorry – jacob assoignon Jan 09 '17 at 15:20
I got a working copy on my local machine, might have to try and adjust that to your needs. I'll update the answer. – Tosfera Jan 09 '17 at 15:36

score 1 · Answer 2 · edited Jan 09 '17 at 16:16

First you need to convert the text coming from the search field to array with:

 $string = $_POST['search'];
 $array = explode( '"' , $string);

So if you put in the search: test"hello"hi

the array will be:

1 => test,
2 => hello,
3 => hi

After that, you need to use the SQL format:

WHERE column_name IN (value1,value2,...)

So you need to change the array we have created to a string with this format:

$string = implode(',',$array);

So the echo of $string will be:

test,hello,hi

and SQL will be :

WHERE column_name IN ($string)

Andy · Accepted Answer · 2017-01-09T15:00:17.047

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.

Nope it doesn't work when you just type the values comma-seperated... Maybe there is something else we are missing? — jacob assoignon, Jan 09 '17 at 14:59
I updated the answer, I think it was the semicolons which were missing... Isn't that the answer, please show us your table layout (you could do this with `DESC opskrifter;` in your database-shell). — Andy, Jan 09 '17 at 15:01

How to create multiple word search? SQL

3 Answers3