How can I count the rows in a search query result?

My script almost works...

[Patrick Taylor]

Can anyone help? I'm building a PHP/MySQL search engine and I want to count the rows returned in the search, in addition to the search results themselves. So far, I have this code within the script, and I need the variable $row_count to be the number of rows returned in the main search query:

// Count how many rows there are.
$query = "SELECT COUNT(keywords) FROM suppliers WHERE keywords LIKE '%$search_terms%'";
$result = @mysql_query($query);
if ($row = mysql_fetch_row($result)) $row_count = $row[0];
else $row_count = 0;

// Make the query.
$query = "SELECT CONCAT(name_one, ' ', name_two) AS entries FROM suppliers WHERE keywords LIKE '%$search_terms%' OR name_one = '$search_terms' ORDER BY name_one ASC LIMIT $offset,$page_size";

// Run the query.
$result = @mysql_query ($query);

I'm using the $row_count variable further down on the page, to make a set of links at the bottom, according to the number a pages I need - there are only ten search listing per page max. But I'm not getting this right. The first query seems to be counting the whole number of rows in the 'suppliers' table instead of the number of rows from the 'search' query.

Some assistance or pointers would be most appreciated.

[mbcx9rvt]

mysql_num_rows [uk.php.net] will probably what you need here!

It's a really useful function.

HTH

[mbcx9rvt]

something I've just found out too...

MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause. To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g.

$sql = "Select SQL_CALC_FOUND_ROWS * from table where state='CA' limit 50";
$result = mysql_query($sql);

$sql = "Select FOUND_ROWS()";
$count_result = mysql_query($sql);

You now have the total number of rows in table that match the criteria. This is great for knowing the total number of records when browsing through a list.

[bobnew32]

Or just

$sql= "select * from table where this= 'that'"
$result= mysql_query($sql)
$numrows= mysql_num_rows($result)

Would that not be easier?

[Patrick Taylor]

Thanks for the replies, which were helpful. I've now kind of solved my problem for the time being.

Patrick