Search accross multiple columns script

[mattc_uk]

Im having problems with this code in my search button script. I get the following error:  Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

 

Here's the code I use on my search form.

 

<form method="post" action="srch_all.php"><input type=text name='search' size=15 maxlength=255><br><input type=submit></form>

 

and here's the code I'm using to perform the search query:

 

if ($search)   // perform search only if a string was entered. 
   { 
    mysql_connect($host, $user, $pass) or die ("Problem connecting to Database"); 

    $srch="%".mysql_real_escape_string($search)."%";

    $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC"; 

$result = mysql_db_query("database_name", $query);
    if(mysql_num_rows($result)==0) {
        print "<h2>Your search returned 0 Results</h2>";
   }

    else if  ($result)
    {

<restult data stuff here>..

Cheers

[sunfighter]

@ mysql_db_query - > This function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.

 

Change $result = mysql_db_query("database_name", $query);

 

To $result = mysql_query($query);

 

And see if that helps

[mattc_uk]

Tried that, still the same error message. It must be to do with the SELECT function because I have used the rest of the code sucessfully when just doing SELECT * from database WHERE column 1 = '$srch' 

 

[update] Just tried your suggested alteration to one of my search scripts that work (searching accross just 1 column) and it comes up with the same error, so maybe something else needs tweaking when adding $result = mysql_query($query);

 

Matt

[sunfighter]

I tried your code on one of my DBs and got a reference to "resource #5" So added a 'mysql_fetch_array' to get to that resource. It prints great for me. See what it does for you.

 

if ($search)   // perform search only if a string was entered. 
   { 
    mysql_connect($host, $user, $pass) or die ("Problem connecting to Database"); 
    $srch="%".mysql_real_escape_string($search)."%";
    $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC"; 

$result = mysql_query($query);
    if(mysql_num_rows($result)==0) {
        print "<h2>Your search returned 0 Results</h2>";
   }	else
{
	while($row = mysql_fetch_array($result))
	{
		echo $row["column1"] . '  ';
		echo $row["column2"] . '  ';
		echo $row["column3"] . '  <br />';
	}
}

[mattc_uk]

Ah then maybe it's a coding error further down becuase I'm outputting my data in to a table as follows...

 

if ($search)   // perform search only if a string was entered. 
   { 
    mysql_connect($host, $user, $pass) or die ("Problem connecting to Database"); 
    $srch="%".mysql_real_escape_string($search)."%";
    $query = "select * from database WHERE column1 LIKE '$srch' or column2 LIKE '$srch' or column3 LIKE '$srch' or column4 LIKE '$srch' ORDER BY column1, column2 DESC, column3 ASC"; 

$result = mysql_query($query);
    if(mysql_num_rows($result)==0) {
        print "<h2>Your search returned 0 Results</h2>";
   }
    else if  ($result)
    { 

$count = 1;	

print "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\"  bordercolor=\"#0000FF\" width=\"900\">";
print "<tr><td width=\"200\" height=\"30\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 1 & 2</font></b></u></td>";
print "<td width=\"80\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 3</font></b></u></td>";
print "<td width=\"200\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 4</font></b></u></td>";
print "<td width=\"200\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 5</font></b></u></td>";
print "<td width=\"80\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 6</font></b></u></td>";
print "<td width=\"100\" align=\"left\"><u><b><font face=\"arial\" size=\"2\" color=\"#800080\">Field 7</font></b></u></td></tr>";

        while ($row = mysql_fetch_array($result)) {     // Begin while 


$data .= "<td width=\"200\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column1']. "</font><br>";

$data .= "<font face=\"arial\" size=\"1\" color=\"#000080\"><i>" .$row['column2']. "</i></font></td>";

$data .= "<td width=\"80\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column3']. "</font></td>";

$data .= "<td width=\"200\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column4']. "</font></td>";

$data .= "<td width=\"200\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column5']. "</font></td>";

$data .= "<td width=\"80\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column6']. "</font></td>";

$data .= "<td width=\"100\" height=\"30\" align=\"left\"><font face=\"arial\" size=\"1\">" .$row['column8']. "</font></td></tr>";

$count++;

        }                  // end while 
        $data .="</table>"; 
echo $data;
     } else { echo "problems...."; }