MySQL Search - WHERE LIKE

Hey all,

I'm making a Flash AMFPHP app that has search functionality, but I'm not quite sure what the PHP search query should look like. This is what I have at the moment and is working:

function doSearch($input) {

//Create SQL query
$sql = "SELECT * FROM nodes WHERE description LIKE ('%$input%')";

//Run query on database
$result = mysql_query($sql);

//Return result
return $result;

Code (markup):

But I want to search more than just the description field. I've tried the following, but it breaks:

function doSearch($input) {

//Create SQL query
$sql = "SELECT * FROM nodes WHERE (name, description, etc) LIKE ('%$input%')";

//Run query on database
$result = mysql_query($sql);

//Return result
return $result;

Code (markup):

Any suggestions? Am I overlooking some quotes somewhere? I've also been looking into the WHERE [arg] AGAINST [arg] method without any success either.

 

Try something like this:

SELECT * FROM nodes WHERE name LIKE '%$input%' OR description LIKE '%$input%'

Code (markup):

 

You should try this also, if you make a fulltext index on name and description.

SELECT * FROM nodes WHERE MATCH (name,description) AGAINST ('%$input%')

Code (markup):

For more info:
Mysql full-text search

 

I tried both of those suggestions including some variations on them without any luck That would leave me to believe it's something I'm overlooking with regards to the db structure. Here's a screenie of my db:

 

Can you post the exact query you're trying to execute?

 

Actually, I just got it to work using:

$sql = "SELECT * FROM nodes WHERE name LIKE ('%$input%') OR description LIKE ('%$input%') OR phone LIKE ('%$input%') OR url LIKE ('%$input%') OR hours LIKE ('%$input%') OR address LIKE ('%$input%') OR email LIKE ('%$input%')";

Code (markup):

I could have sworn I tried this exact thing earlier and it wasn't working. Thanks for the help!

 

I wouldn't do it that way at all.

On your application, I would check to see if any values have been entered in on that peice of the form and only include them into the query if they are necessary. Doing what everyone is suggesting is going to create a very very very slow query becasue % % on both sides of a like statement does not use indexes.

So, it will be like this:

$sql = "SELECT * FROM nodes where";

if ($post['name'] != ""){
   $sql .= "name like '%STUFF%';
}
elseif($post['description'] != "") {
   $sql .= "and description like '%STUFF%'";
}

Code (markup):

and the if statement would just keep going on and on for the columns you want.. The if statements are quick so it won't take you much time to check them.

 

But it's obvious that he wants to search for the same term in all fields.

 

I appreciate the concern RaginBajin, but truth be told, I'm only searching about 15-20 entries, so I'm not too concerned about query times. Besides, I'm just looking for a quick fix (for now)

And you're right, SoKickIt, I am wanting to search for the same term in any of the fields