mysql_num_rows reporting wrong values

Anon

Hi,
I have a question some of you may find simple enough to answer. The code below works perfectly (hacked a bit from the PHPBuilder article on pagination) when on a "default" display, in which the SELECT simply lists the tables's records. However, when I submit more than two keywords the script for a search, mysql_num_rows doesn't report the number of resultset rows correctly (usually 1 or 0), so the whole pagination thing breaks down, among other small problems...even though the resulting display will grab the relevant records.

I've tried a number of permutations with the SELECTS, the mysql_num_rows and other calls, but the effect is always the same; upon submitting keywords, the script doesn't use the "prev -- next" pagination or $limit.

Thanks in advance for any help!

Mark

ps. I'm running:
(webserver) Red Hat 6.2, PHP 4.4pl1
(data server) Red Hat 6.2, MySQL 3.23.30-1

== code == appologies if tabs are off ==

require ("connect.php");

$keyword = trim($keywords);
$words = explode(" ",$keyword); 
$num_words = count($words); 
$limit = 2; // small number for testing with a short list
$search = implode($words, "+");

if (empty($offset) || $offset < 0) $offset = 0;
if (empty($index)) $index = 0;

while (list ($key, $val) = each ($words))
{
$numresults = mysql_query("SELECT count(contact_id) FROM contacts WHERE first LIKE '$val%' OR middle LIKE '$val%' OR last LIKE '$val%' OR company LIKE '$val%' GROUP BY contact_id");
$numrows = mysql_num_rows($numresults);
$results = mysql_query("SELECT contact_id,first,middle,last,company FROM contacts WHERE first LIKE '$val%' OR middle LIKE '$val%' OR last LIKE '$val%' OR company LIKE '$val%' GROUP BY contact_id ORDER BY last LIMIT $offset,$limit");
$num_row = mysql_num_rows($results);

$index++;

if ($row = mysql_fetch_array($results))
	{
	do
		{
		// this will show the results, if any

		printf("<a href=\"%s?id=%s\"> %s, %s %s </a>( %s )<br />\n", $PHP_SELF, $row["contact_id"], $row["last"], $row["first"], $row["middle"], $row["company"]);
		}
	while ($row = mysql_fetch_array($results));
	mysql_free_result($results);
	}
else printf("no records found<br/>\n");
} 

if ($offset != 0) 
	{   
	$prevoffset = $offset - $limit; 
        echo   "<a onMouseOver=\"window.status='previous $limit results'; return true\"; href=\"$PHP_SELF?keywords=$search&offset=$prevoffset&index=$prevoffset\">[previous]</a>&nbsp";
	}
else echo "[previous]&nbsp";

$pages = intval($numrows / $limit); 

if ($numrows % $limit) $pages++;

for ($i = 1; $i <= $pages; $i++) 
	{ 
	if (($offset / $limit) == ($i - 1)) echo "$i";
	else 
		{ 
		$newoffset = $limit * ($i - 1); 
		echo  "&nbsp;<a onMouseOver=\"window.status='page $i results'; return true\"; href=\"$PHP_SELF?keywords=$search&offset=$newoffset&index=$newoffset\">$i</a>\n"; 
		}
	}

mysql_free_result($numresults);

if (!((($offset / $limit) + 1) == $pages) && $pages != 1) 
	{
	$newoffset = $offset + $limit;
	echo "&nbsp<a onMouseOver=\"window.status='next $limit results'; return true\"; href=\"$PHP_SELF?keywords=$search&offset=$newoffset&index=$newoffset\">[next]</a><p>\n";
	}
else echo "&nbsp[next]";

printf("<p><form method=\"get\" action=\"$PHP_SELF\"><input type=\"text\" name=\"keywords\" value=\"$keywords\">&nbsp<input type=\"submit\" name=\"submit\" value=\"search\"></p>");

echo "$numrows matching records<br />"; // for testing
echo "$num_row records on page"; // for testing

[deleted]

Looks like a few things are in the wrong order.

First of all get rid of that

if(condition)
{
do
{
}
while(condition)

that's the same as

while (condition)
{
};

Next, I'd have a look at this line:

if ($numrows % $limit) $pages++;

that only increases $pages if there not an exact multiple of $limit results.
If you have $limit=10 and you get 10 results, your $pages will not be increased.

And lastly, you have a while loop that handles all the keywords, and fetches the results and numrows for all the queries with each keywords, but you don't remember anything from that loop.
When the loop is finished, you just take the numrows (which is from the last keyword) and work with it.
So the numrows is correct, but it's only the number of results from the last keyword you checked.

Anon

Hi vincent,

Tried your first two suggestions, to no effect. It did not seem to matter one whit, except to confuse the application even more (if not me as well). The last permutation is:

while (list($key, $val) = each($words))
{
$numresults = mysql_query("SELECT count(contact_id) FROM contacts WHERE first LIKE '$val%' OR middle LIKE '$val%' OR last LIKE '$val%' OR company LIKE '$val%' GROUP BY contact_id");
$numrow = mysql_num_rows($numresults);
$results = mysql_query("SELECT contact_id,first,middle,last,company,count(*) FROM contacts WHERE first LIKE '$val%' OR middle LIKE '$val%' OR last LIKE '$val%' OR company LIKE '$val%' GROUP BY contact_id ORDER BY last LIMIT $offset,$limit");
$row = mysql_fetch_array($results);
$count = mysql_num_rows($results);
$index++;

printf("<a href=\"%s?id=%s\"> %s, %s %s </a>( %s )<br />\n", $PHP_SELF, $row["contact_id"], $row["last"], $row["first"], $row["middle"], $row["company"]);

//else printf("no records found<br/>\n");

mysql_free_result($results);
}

... remaining code is the same.

What I really need is your solution for making the while loop remember the iterations for each keyword, plugging that into the $numrow value. You're intimation makes sense and has to be right, but I don't know how to do it...nor can I find anything on how to do it.

Thankyou.

Mark

[deleted]

Well, what you are trying to do is not exactly simple either.

You want to search for multiple keywords, and do one query for each keyword,
but you still want to be able to put page-numbers on the results.

To get the page-numbers, all you have to do is add all the numrows up to get the total number of results.
Set $numrows=0 at the top, then add to it:

$numrows +=mysql_num_rows(bla);

for each query.

Then after the while loop you can calculate how many pages that will give.

Your biggest problem is that you are not really doing one search for X keywords,
you are doing one search per keyword. That means your results are not just
one set of results, they are X sets, listed after eachother.
That makes it very difficult to put pagenumbers on the results.
If you look at page 4, you could be seeing results from keywords 3,4 and 5.

The way you have it set up now, you use the same LIMIT clause for each of your keyword searches. that means that if you use LIMIT 10,20 and your keyword search only has 4 results, it lists ZERO results.

The best way to proceed I think would be to combine the keyword searches into one big query. Then you'll have one set of results that you can easily use the pagenumbers and LIMIT statement on.

Anon

Hi again,
:you wrote:
Well, what you are trying to do is not exactly simple either.
::

Bizackly! 🙂

::
You want to search for multiple keywords, and do one query for each keyword,
but you still want to be able to put page-numbers on the results.
::

Well, I want to search for multiple keywords, and I want to display potentially multiple results, but I don't necessarily want to replicate the query for each word in the exploded array. I've just used and modified the code which has been freely given, pushing it around in my trials; I don't automatically believe it is the best way.

::
To get the page-numbers, all you have to do is add all the numrows up to get the total number of results.
Set $numrows=0 at the top, then add to it:

$numrows +=mysql_num_rows(bla);

for each query.

Then after the while loop you can calculate how many pages that will give.
::

Thanks, that should help...

::
Your biggest problem is that you are not really doing one search for X keywords,
you are doing one search per keyword....
::

Right, but which condition is appropriate for the while loop - and if only one is used, how to handle the other? I've got multiple keywords, and potentially multiple rows in the result, so some kind of looping is obviously required. So, how do I get that exploded array into a single query, which will then hopefully trigger the needed numrow value, which will then trigger the pagination correctly?

Thanks again.

Mark