PHP: Making a search in mysql for multiple AND conditions within combined fields

Question

I´m trying to put together the most elegant way of searching in two fields for multiple (number of words can vary) terms that needs to only provide results when all words are found (AND instead of OR).

The below gives me an SQL syntax error message:

$search = $_GET["search"];
$searcharray = explode('|', $search);
$query=("SELECT username,sender,message,subject,timestamp,threadid,msgtype 
FROM Messages WHERE  ('" . implode("'|'",$searcharray) . "') IN CONCAT 
(message,subject)  ORDER BY timestamp");

I could of course set up a foreach loop for each match on the first word that breaks with an instruction to not add the result if any of the other words are not found in the two fields, but that´s alot more for the PHP script to handle, I think. Any suggestions?

What is the full error message you are getting? It should explain what is causing the problem. I have a feeling it's where you implode the array. I think you will need to have one item in the where clause for each item in the search array. — Sloan Thrasher
– Sloan Thrasher, Commented Jun 10, 2017 at 0:15
You aren't using a regex here, do you mean to be? You are open to SQL injections. You would probably be best off using the full text searching. — chris85
– chris85, Commented Jun 10, 2017 at 0:18
No, if only regexp had AND and not only OR I´d be using that. — Corey Hart
– Corey Hart, Commented Jun 10, 2017 at 0:42

Barmar · Accepted Answer · 2017-06-10 00:49:19Z

1

IN has to be followed by a list of values in parentheses, or a SELECT subquery. You can't use it for pattern matching.

To search a column for a word, you need to use LIKE, with % around the word. And there's no shortcut for searching for multiple words, you have to search for each of them and combine them with AND.

$tests = array_map(function($word) {
    return "CONCAT(message, subject) LIKE '%$word%'";
}, $searcharray);
$where = implode(' AND ', $tests);
$query = "SELECT username,sender,message,subject,timestamp,threadid,msgtype 
        FROM Messages WHERE $where ORDER BY timestamp";

edited Jun 10, 2017 at 0:49

answered Jun 10, 2017 at 0:24

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Corey Hart Over a year ago

Thanks, but this one gave me an Internal Server Error.

Barmar Over a year ago

Typo, missing parenthesis.

Barmar Over a year ago

Also used the wrong variable, it should be $searcharray, not $search.

Corey Hart Over a year ago

This worked, thanks! Any reason to think that this solution is faster or slower than the one provided by Sloan?

Barmar Over a year ago

They're basically the same, except he left out the % characters, so it won't work.

Sloan Thrasher · Accepted Answer · 2017-06-10 08:36:35Z

1

As mentioned in the comments, the code is suseptable to SQL injection. That being said, and since I don't want to re-write all of the code ;-), here's one way to construct the where clause.

$search = $_GET["search"];
$searcharray = explode('|', $search);
$qstr = "SELECT
    `username`,
    `sender`,
    `message`,
    `subject`,
    `timestamp`,
    `threadid`,
    `msgtype`
FROM `Messages`
WHERE ";
$w = array();
foreach($searcharray as $key => $val) {
    $w[] = "CONCAT(`message`,`subject`) LIKE '%" . $val . "%'";
}
$w_str = implode(" AND ",$w);
$qstr .= $w_str . " ORDER BY `timestamp`";

edited Jun 10, 2017 at 8:36

answered Jun 10, 2017 at 0:23

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

5 Comments

Corey Hart Over a year ago

Thanks, but I get an error here: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(searchmessage,subject) LIKE 'test' AND CONCAT(searchmessage,`s' at line 10

Barmar Over a year ago

You need a space after WHERE.

Barmar Over a year ago

You also need to add % characters around $val.

Corey Hart Over a year ago

Ok, with the space I get no results but no error. I get why you need the % wildcard there as you would need it for LIKE to work, but $w[] = "CONCAT(message,subject) LIKE '" . %$val% . "'"; gives me an internal server error.

Barmar Over a year ago

@CoreyHart The % have to be in the string: LIKE '%" . $val . "%'"

Collectives™ on Stack Overflow

PHP: Making a search in mysql for multiple AND conditions within combined fields

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related