Searching and paging

[wendystewart80]

I am having a problem with combining searchability of my database and paging with PHP/MySQL.
I having a problem with the paging counting the number of results when search terms are involved.
When I have the count query as shown below, the whole amount of results are counted and too many pages are displayed as the search terms are not included:
[php]
$query = "SELECT * FROM project AS p, regions AS r, cats as c
WHERE p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND p.region LIKE '$region' AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
GROUP BY p.id
ORDER BY org
"
.
" LIMIT $offset, $rowsPerPage";
$result1 = mysql_query($query) or die('Error, query1 failed: '.mysql_error());

// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM project
";
$result = mysql_query($query) or die('Error, query failed: '.mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
[/php]
When I put the search criteria in the count query as shown below, the paging returns only 1 page even when there is more than 10 results so there should be multiple pages:
[php]
$query = "SELECT * FROM project AS p, regions AS r, cats as c
WHERE p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND p.region LIKE '$region' AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
GROUP BY p.id
ORDER BY org
"
.
" LIMIT $offset, $rowsPerPage";
$result1 = mysql_query($query) or die('Error, query1 failed: '.mysql_error());

// how many rows we have in database
$query = "SELECT COUNT(p.id) AS numrows FROM project AS p, regions AS r, cats as c
WHERE p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND p.region LIKE '$region' AND c.catid LIKE '$cat'
OR p.region = r.region AND c.id = p.id AND org LIKE '%$search%' AND $region=0 AND '$cat'='0'
GROUP BY p.id
ORDER BY org
";
$result = mysql_query($query) or die('Error, query failed: '.mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
[/php]
How can I make the search criteria in the first query apply to the counting or rows in the second query so the paging is accurate?

[jatar_k]

if you chunk all the results from the various queries all into the same array and then output them in a seperate loop later then your counting will be contiguous.