Search Results ($num_rows) does not match $search results (PHP,MySQL)?

$num_rows shows more results than there are...

[alcheme]

Hello, the $num_rows shows more results than there are...

For example, when I do a database search for a term I get 2 results but the $num_rows says there are 3 results.

I suspect it's because there are similar keywords within different search fields of the SQL statement. But I am stumped as to how I can change this. Any Suggestions?

-----> the code:

$result = mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory", $db);

$num_rows = mysql_num_rows($result);

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

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory WHERE city LIKE '%$search%' ¦¦ wname LIKE '%$search%' ¦¦ title LIKE '%$search%' ¦¦ keywords LIKE '%$search%' ¦¦ description LIKE '%$search%' ¦¦ country LIKE '%$search%' ¦¦ category LIKE '%$search%' ¦¦ url LIKE '%$search%'", $db);

if ($sql)
{
echo "<b>Found <u>".$num_rows."</u> results to match search string: ".$search."</b><br><br>";

<----- End Code

I'll post the final solution when it works.

~Shane

[coopster]

You are running two separate queries. First you have to decide from which result set do you want to get the number or rows returned. mysql_num_rows() [php.net] returns the number or rows for the result set you specify in the function.

[alcheme]

I tried this

$result = mysql_query("SELECT wname FROM directory", $db);

but it did not work... still shows 3 results when there are only 2.

[alcheme]

I just noticed it says I am getting 3 results even if there are no results or any number of results.

Hmmmm....

[alcheme]

Coopster... you totally make sense. Thank you! The $num_rows gets its results from $sql...

THE SOLUTION:

------Begin Code

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

$sql= mysql_query("SELECT wname, url, title, keywords, description, city, country, category FROM directory WHERE city LIKE '%$search%' ¦¦ wname LIKE '%$search%' ¦¦ title LIKE '%$search%' ¦¦ keywords LIKE '%$search%' ¦¦ description LIKE '%$search%' ¦¦ country LIKE '%$search%' ¦¦ category LIKE '%$search%' ¦¦ url LIKE '%$search%'", $db);

$num_rows = mysql_num_rows($sql);
if ($sql)
{
echo "<b>&nbsp;&nbsp;Found <u>".$num_rows."</u> results to match search string: ".$search."</b><br><br>";

------End Code

Take it easy!

~Shane

p.s. Now onto the next post... eek