Oh no, not another boring "Passing variables for search result page numbering" thread

Hi there

Apologies for posting yet another boring page numbering question but I really am at the end of my tether.

In short, my problem is identical to the one found in this thread here:

Passing a variable within a page.

I have a database of chairs which can be searched through using this page. This all works fine.

The problem comes with the page numbering. Initially it works fine, yielding the first six results requested and displaying them in the first page. However, clicking on the link to the second page returns results for the entire table, rather than the next 6 search results. This, I gather, is because I have not passed across the search variables.

Due to my lack of understanding, I am unable to establish how to properly pass the variables between pages using either of the methods suggested in the previous thread covering this topic.

I have tried sessions but couldn’t implement them correctly. I wasn’t sure which page I needed to define the variables on once the various values had been lifted from the chairs database.

I’ve also tried passing on the variables in a link:

if ($pageNum < $totalPages)
{
    echo('<a href="chairlist.php?pageNum='.($pageNum+1).'&cid='.$cid.'">'.
         'Next</a>' );
}

However, I evidently hadn’t defined the variable correctly as the URL always came up:

chairlist.php?pageNum=2&cid=

I gather this is because the variable is only being picked up once by $_POST:

$cid = $_POST['cid'];
if ($cid != '') { // A Chair colour is selected
  $from  .= ', ccolours';
  $where .= " AND ccolours.id='$cid' AND CID=ccolours.ID";
}

rather than being carried over with a $_GET or whatever the correct carrier should be.

If someone could perhaps point me in the direction of a definitive tutorial on this matter, or how to go about coding the passing of variables, I would be most grateful.

For reference, here is the PHP code for the both the search chair page and the result return chair list page I have constructed.

<?php 

//searchchairs.php
$cids = @mysql_query('SELECT id, ccol FROM ccolours');
if (!$cids) {
  exit('<p>Unable to obtain colours list from the database.</p>');
}
$mids = @mysql_query('SELECT id, cmat FROM cmaterials');
if (!$mids) {
  exit('<p>Unable to obtain materials list from the database.</p>');
}
?>
<form action="chairlist.php" method="post">
<p>View chairs satisfying the following criteria:</p>
<label>Chair colour:
<select name="cid" size="1">
  <option selected value="">Any Colour</option>
<?php
while ($cid = mysql_fetch_array($cids)) {
  $ccid = $cid['id'];
  $ccol = htmlspecialchars($cid['ccol']);
  echo "<option value='$ccid'>$ccol</option>\
"; 
}
?>
</select></label><br />
<label>Chair material:
<select name="mid" size="1">
  <option selected value="">Any Material</option>
<?php
while ($mid = mysql_fetch_array($mids)) {
  $cmid = $mid['id'];
  $cmat = htmlspecialchars($mid['cmat']);
  echo "<option value='$cmid'>$cmat</option>\
"; 
}
?>
</select></label><br />
<p>Stacking chair: <input name="stack" type="checkbox" value="yes" /></p>
<p>Available as High stool: <input name="high" type="checkbox" value="yes" /></p>
<p>Upholstered: <input name="uphol" type="checkbox" value="yes" /></p>
<input type="submit" value="Search" />
</form>

//chairlist.php

$numPerPage = 6;

if ( isset($_GET['pageNum']) )
{   $pageNum = $_GET['pageNum'];
}
else
{   $pageNum = 1;
}

$pgOffset = ($pageNum*$numPerPage)-$numPerPage;

// The basic SELECT statement
$totalquery = "SELECT COUNT(*)";
$select = 'SELECT DISTINCT Chairs.ID, Code';
$from   = ' FROM Chairs';
$where  = ' WHERE 1=1';

$cid = $_POST['cid'];
if ($cid != '') { // A Chair colour is selected
  $from  .= ', ccolours';
  $where .= " AND ccolours.id='$cid' AND CID=ccolours.ID";
}

$mid = $_POST['mid'];
if ($mid != '') { // A Chair material is selected
  $from  .= ', cmaterials';
  $where .= " AND cmaterials.id='$mid' AND MID=cmaterials.ID";
}

$stack = $_POST['stack'];
if ($stack != '') { // Stacking is selected
  $where .= " AND Stack='$stack'";
}

$high = $_POST['high'];
if ($high != '') { // High chair is selected
  $where .= " AND High='$high'";
}

$uphol = $_POST['uphol'];
if ($uphol != '') { // Upholstered is selected
  $where .= " AND Uphol='$uphol'";
}

// die if query fails.
if ( !$result2 = mysql_query("$select $from $where LIMIT $pgOffset, $numPerPage") )
{   die('Chair selection, from, where, limit query failed: '.mysql_error());
}

$totalfrom = $from;
$totalquery .= $totalfrom .= $where;

// get total entries in the db
$test  = mysql_query($totalquery);
$total = mysql_result($test, 0, 0);

// Figure out the total number of pages. Always round up using ceil()
$totalPages = ceil($total / $numPerPage);

while ($author = mysql_fetch_array($result2)) {
  $code = $author['Code'];
echo "<li>". 
          "<p class='listing_new'><img src='images/$code.jpg' alt='$code' width='130' height='150' class='left' /></p>".
		  "<p class='listing'>$code</p>".
     "</li>";
} 

if ($pageNum > 1)
{
    echo('<a href="chairlist.php?pageNum='.($pageNum-1).'">'.
         'Previous</a>' );
}

pageNumberLinks($totalPages, $pageNum);

if ($pageNum < $totalPages)
{
    echo('<a href="chairlist.php?pageNum='.($pageNum+1).'">'.
         'Next</a>' );
}

function pageNumberLinks($totalPages, $pageNum)
{   
    for ($i=1; $i<=$totalPages; $i++)
    {   
        echo ' ';
    
        if ($i != $pageNum )
        {  echo '<a href="chairlist.php?pageNum='.$i.'">';
           echo "\
";
        }

        echo $i;

        if ($i != $pageNum )
        {  echo '</a>';
           echo "\
";
        }
    
        echo ' ';
    }
}

You’re sending two (semi)complex queries to the database in order to get the total number of rows plus the results. You can get around this by using MySQL’s SQL_CALC_FOUND_ROWS feature together with a FOUND_ROWS() query. I have a paged_query class at home that does this. I’ll post it later tonight with an example of a pager.

Thanks, that will be great.

I’ll start looking into MySQL’s SQL_CALC_FOUND_ROWS and FOUND_ROWS() in the meantime.

I was planning to put a demo app together and include it here, but it looks like that will have to wait for another day. Here is what I can offer tonight –


<?php
include_once('lib/db.php');
class paged_query
{
	function paged_query($name, $fields, $tables, $where = '', $order_by = '')
	{
		$this->name = $name;
		$this->fields = $fields;
		$this->tables = $tables;
		$this->where = empty($where) ? '' : "WHERE $where";
		$this->order_by = empty($order_by) ? '' : "ORDER BY $order_by";
	}
	function query($limit = 15)
	{
		$this->limit = $limit;
		$this->offset = isset($_GET[$this->name]) ? $_GET[$this->name] : 0;
	        $results = db_query("SELECT SQL_CALC_FOUND_ROWS $this->fields FROM $this->tables $this->where $this->order_by LIMIT $this->offset, $limit");
	        $this->total_results = db_one('SELECT FOUND_ROWS()');
		return $results;
	}
	function set_script($script)
	{
		$this->script = $script;
	}
	function has_pages()
	{
		return (ceil($this->total_results / $this->limit) > 1);
	}
	function show()
	{
		echo $this->get_html();
	}
	function get_html()
	{
		$pages = ceil($this->total_results / $this->limit);
		$html = 'Pages:&nbsp;';
		for ($i = 1; $i <= $pages; $i++)
		{
			$offset = $this->limit * ($i - 1);
			$html .= ($offset == $this->offset) ? "$i&nbsp;" : "<a href=\\"$this->script&$this->name=$offset\\">$i</a>&nbsp;";
		}
		return $html;
	}
}
?>

It would be used like this –


<?php
include_once('lib/paged_query.php');
$pager = new paged_query('p', 'fielda, fieldb', 'tablea a, tableb b', 'a.id = b.id', 'creation_date DESC');
$pager->set_script("somefile.php?id=$id");
$results = $pager->query();
if (db_num_rows($results))
{
  while ($row = fetch_db_row($results)) {// output rows}
}
if ($pager->has_pages()) $pager->show();
?>

Hope that helps. I’ll try to get a demo app together soon. Feel free to ask any questions in the meantime.

Thanks for the input. Having viewed the code you offered up, I decided to read through Kevin Yank’s tutorial Object Oriented PHP: Paging Result Sets.

I can get the code from that tutorial working but only on a limited level when using my search form.

I can get any one option working on its own but as soon as I introduce multiple search outcomes, it ceases to work.

The output code I’m currently using is lifted straight from the OOP tutorial:

<?php
require('pagedresults.php');

$cnx = @mysql_connect('localhost','database','password');
mysql_select_db('jokes',$cnx);
$rs = new MySQLPagedResultSet("select * from Chairs where CID=$cid",
                             6,$cnx);
?>
<html>
<head>
<title>Paged Results Demo</title>
</head>
<body>
<table border="1">
<?php while ($row = $rs->fetchArray()): ?>
<tr><td><?=$row['Code']?></td><td><?=$row['Width']?></td></tr>
<?php endwhile; ?>
</table>
<p><?=$rs->getPageNav("CID=$cid")?></p>
</body>
</html>

working in conjunction with the class supplied in the tutorial.

I gather that adding multiple Where queries to the output code is the way to go but I’ve yet to figure out how to make distinctions between them.

I’ve found another interesting thread on the topic here, and I’m going to keep plugging away until I workout the resolution to my problem. I’ll post whatever I find incase there’s some other poor soul out there working on this problem, pulling as much hair out as me.

I’ve finally got it working. In the end I was closer than I thought, here’s the reply from another forum I posted the problem on:

So, all I needed to do was change the form method from ‘post’ to ‘get’ and change $cid = $_POST[‘cid’]; to $cid = $_GET[‘cid’]; — simple as that. Adding the variables to the link: echo ‘<a href="chairlist.php?pageNum=’.$i.‘&cid=’.$cid.‘&mid=’.$mid.‘&stack=’.$stack.‘&high=’.$high.‘&uphol=’.$uphol.‘">’; finally made it work like a charm.

In the end, because of my lack of understanding, I was trying to over complicate things using the Object Oriented paging method.

Thank you for the help though averagejoeguy, you led me up some alternative lines of enquiry and I’ve learnt a lot in the process. Cheers!

Off Topic:

5 year old thread Rudy? :stuck_out_tongue:

and they all went home for tea

Off Topic:

in joke between me and parsonsgfys

Off Topic:

Hehe, hats off to ye sir!