Search Multiple SQL Fields

[chopper]

I have a simple search scrpt that is working searching one field in a SQL database that I want to use in another script to search multiple fields for the search term. This is the code used to specify the search field:

 // Build SQL Query  
    $query = "select * from BOL where name like \"%$trimmed%\"  
    order by name"; // EDIT HERE and specify your table and field names for the SQL query.  
   $numresults=mysql_query($query);
   $numrows=mysql_num_rows($numresults); 

 

 

 

where "name" is what would be the syntax to add say a field called "name2" as well. I even tried a wild card but I received this error

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/results_bol.php on line 44

Any suggestions greatly appreciated.

 

[R0bb0b]

use single quotes, not double.

<?php
$query = "select * from BOL where name like '%$trimmed%' order by name";
?>

[chopper]

Thanks,

I tried this

$query = "select * from BOL where * like '%$trimmed%' order by name";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

But I received this error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/results_bol.php on line 42

[R0bb0b]

You have to specify a column to compare to $trimmed you can't just use *.  If you want to search multiple columns then you can use:

<?php
// all posibilities
$query = "select * from BOL where ".
		"column1 like '%$trimmed%' ".
		"or column2 like '%$trimmed%' ".
		"or column3 like '%$trimmed%' ".
		"or column4 like '%$trimmed%' ".
		"or column5 like '%$trimmed%' ".
		"or column6 like '%$trimmed%' ".
		"or column7 like '%$trimmed%' ".
		"or column8 like '%$trimmed%' ".
		"order by name";	

// found it all columns
$query = "select * from BOL where ".
		"column1 like '%$trimmed%' ".
		"and column2 like '%$trimmed%' ".
		"and column3 like '%$trimmed%' ".
		"and column4 like '%$trimmed%' ".
		"and column5 like '%$trimmed%' ".
		"and column6 like '%$trimmed%' ".
		"and column7 like '%$trimmed%' ".
		"and column8 like '%$trimmed%' ".
		"order by name";	
?>

[DarkWater]

Instead of guessing on what the problem is, R0bb0b, he should add some error checking.

 

Change:

   $numresults=mysql_query($query);

 

To:

   $numresults=mysql_query($query) or die(mysql_error());

 

Show me the new output.

[chopper]

R0bb0b,

That was it.

THANK YOU!!!!

 

[chopper]

DarkWater

Output was the same, it just worked.

What is it I should be learning here?

I'm fairly new to PHP I generally I prefer to hunt dow answeres on my own.

This one I could not find.

[DarkWater]

DarkWater

Output was the same, it just worked.

What is it I should be learning here?

I'm fairly new to PHP I generally I prefer to hunt dow answeres on my own.

This one I could not find.

 

Okay, if you still had an error, it would have killed the script and shown you the MySQL error.  Since you fixed it, no error was shown (obviously).  Keep that piece of code handy for next time.  You should generally use that on every single mysql_query() call so you can catch MySQL errors.

[R0bb0b]

Just make sure you disable them or write them to a log instead before you put the site into production since you want to keep the public viewable errors to a minimum.

[DarkWater]

Just make sure you disable them or write them to a log instead before you put the site into production since you want to keep the public viewable errors to a minimum.

 

Indeed.  You should always have some sort of error logging solution in effect on production sites.

[chopper]

Thanks for the coaching all!!!