PHP MySQL database search script. Complex MySQL search query. Can someone help me?

Question

PHP MySQL database search script. Complex MySQL search query. Can someone help me?

Answer 1

It would help if you told us what it does, and what you'd expect it to do.

Answer 2

//Split post by words and build the query

$words_query = "";

$words = split(' ', $_POST["search"]);

for ($i = 0; $i < count($words); $i++)

{

if ($words_query == "")

$words_query .= "WHERE field LIKE '%" . $words[$i] . "%'";

else

$words_query .= " OR field LIKE '%" . $words[$i] . "%'";

}



//Check here that words_query is valid

if ($words_query != "")

{

$result = mysql_query("SELECT * FROM table " . mysql_real_escape_string($words_query));

while($r=mysql_fetch_array($result))

{

//display results code

}

}



Be VERY carefuly about how you let your user interact with SQL through your site. The function mysql_real_escape_string() will remove any characters that they could use to maliciously affect your website and its data.



As a quick example with your original code, if someone set 'search' as "%'; DELETE * FROM table; --" all your data would have been cleared.

Answer 3

WHERE field REGEXP . . should do it for you.



To protect your database, I recommend you set up a user who has only the privileges you need and then use that user to connect to your database.



Edit: One user, humm, I hope the data is not precious. Of course validating the input should be done too. I would move the mysql_real_escape_string() to the top of your page as the first action on the $_POST[].



Your error is because you do not have a valid value for your array because you have a problem with your $words_query. Try echo and you can see what it contains.



It would be simpler to use REGEXP rather than LIKE in your WHERE as it would match any word in the phrase meaning that you don't need to create $words_query.



A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)