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:

$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'];

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:

$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'];

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?

leatherback

Hi,

You have quite a cluttered long query there!

On your question: There is a tutorial on pagination in the main site. Have a loko at it, it will guide you trhough it step by step.

On the current code: You would also be smart to look at the JOIN syntax, join your three tables, and horten your query by about 75%. Then, use mysql_num_row($result) to retrieve the number of results, instead of a secojnd query, shortening your code again quite a bit. After cleaning all this up, please post again.