Pagination Frustration

[kingnutter]

Hi everyone,

 

I've been following the fantastic Pagination tutorial from this website, which makes everything very clear. Sadly when trying to customise for my own project I'm getting a bit mixed up.

 

The results I wish to paginate are from a query of JOINed tables, and I'm not sure if I'm putting my COUNT in the right place. Perhaps I should have something like COUNT ($result).

 

I would be grateful if someone has the time to have a quick scan and provide any pointers, also for potential future hiccups as I haven't proceeded past the errors as yet.

 

Many thanks.

KN

 

<form name="search" method="post" action="search_results.php">
Search for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="track_artist">Track Artist</option>
<Option VALUE="track_title">Track Title</option>
<Option VALUE="all">All</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?php

// includes
include('conf.php');
include('functions.php');

// Grab POST data sent from form
$field = $_POST['field'] ;
$find = $_POST['find'] ;
$searching = $_POST['searching'] ;

//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}

// Otherwise we connect to our Database
// open database connection
$connection = mysql_connect($host, $user, $pass)
or die ('unable to connect!');

//select database
mysql_select_db($db) or die ('unable to select database!');


$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

if ($field=='all')

{
$query = "SELECT COUNT t.moj_id, t.track_artist, t.track_title, m.moj_title

FROM tracks t
JOIN mojocd m ON t.moj_id = m.moj_id

WHERE upper(t.track_artist) LIKE '%$find%'
   OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage";
}
else
//Or we search for our single search term, in the field the user specified
{
$query = "SELECT COUNT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage";
}

$result = mysql_query($query);

$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if


// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

if ($field=='all')

{
$query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title

FROM tracks t
JOIN mojocd m ON t.moj_id = m.moj_id

WHERE upper(t.track_artist) LIKE '%$find%'
   OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage";
}
else
//Or we search for our single search term, in the field the user specified
{
$query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage";
}

$result = mysql_query($query);

// while there are rows to be fetched...

?><table><?php
while($list = mysql_fetch_assoc( $result ))
{

$counter++;
	$background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0');


echo '<tr><td style="background-color:'.$background_color.'">' ?>
<b><?php echo $list['track_title']; ?></b> by 
<b><?php echo $list['track_artist']; ?></b> on
<b><?php echo $list['moj_title']; ?></b></td></td>
<br>
<?php
}
?>
</table>
<?php
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($result);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}

?> 

[alexdemers]

COUNT(*) or COUNT(field_name)

 

Not just COUNT by itself.

[kingnutter]

Thanks. But I'm still confused about the placement.

 

Should it be part of the sql query...

 

$query = "COUNT (moj_id) WHERE SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title

FROM tracks t
JOIN mojocd m ON t.moj_id = m.moj_id

WHERE upper(t.track_artist) LIKE '%$find%'
   OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage";

 

... which just doesn't look right

 

or in this part of the code:

 

$result = mysql_query($query);

$r = mysql_fetch_row($result);
$numrows = $r[0];

 

...which doesn't seem right because is COUNT not an sql query only?

[alexdemers]

MySQL ::  MySQL 5.1 Reference Manual :: 3.3.4.8 Counting Rows

 

SELECT COUNT(*) FROM `table`

[kingnutter]

I sorted this out with mysql_num_rows in the end and it works quite well. For the first page of results anyhow. Then the links to further search pages lead to nothing.

 

I think it's because I should be rePOSTing my search terms. Or perhaps it's something to do with SESSIONS.

 

Can anyone give me some pointers before I go off down the wrong path?

 

<form name="search" method="post" action="search_results.php">
Search for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="track_artist">Track Artist</option>
<Option VALUE="track_title">Track Title</option>
<Option VALUE="all">All</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?php

// includes
include('conf.php');
include('functions.php');

$field = $_POST['field'] ;
$find = $_POST['find'] ;
$searching = $_POST['searching'] ;

//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}

// Otherwise we connect to our Database
// open database connection
$connection = mysql_connect($host, $user, $pass)
or die ('unable to connect!');

//select database
mysql_select_db($db) or die ('unable to select database!');


$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);


// Query database to find out how many rows in total result before pagination. N.B Could experiment with COUNT here.

if ($field=='all')

{
$query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title

FROM tracks t
JOIN mojocd m ON t.moj_id = m.moj_id

WHERE upper(t.track_artist) LIKE '%$find%'
   OR upper(t.track_title) LIKE '%$find%'";
   
$result = mysql_query($query, $connection);
}
else
//Or we search for our single search term, in the field the user specified
{
$query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%'";
$result = mysql_query($query);
}

$result = mysql_query($query, $connection);

$numrows = mysql_num_rows($result);


// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);


// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if


// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;


if ($field=='all')

{
$query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title

FROM tracks t
JOIN mojocd m ON t.moj_id = m.moj_id

WHERE upper(t.track_artist) LIKE '%$find%'
   OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage";
}
else
//Or we search for our single search term, in the field the user specified
{
$query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage";
}

$result = mysql_query($query);

// while there are rows to be fetched...

?><table><?php
while($list = mysql_fetch_assoc( $result ))
{

$counter++;
	$background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0');


echo '<tr><td style="background-color:'.$background_color.'">' ?>
<b><?php echo $list['track_title']; ?></b> by 
<b><?php echo $list['track_artist']; ?></b> on
<b><?php echo $list['moj_title']; ?></b></td></tr>
<br>
<?php
}
?>
</table>
<?php
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($result);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}


/******  build the pagination links ******/
// range of num links to show
$range = 3;



// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

[kingnutter]

It looks like both SESSIONs or re-POSTs could would.

 

Which is the more scalable solution, say when search results reach into the hundreds?