[SOLVED] pagination results won't get past ten. Why?

[mrelliott]

I've seperated out my search page from my results page.

 

Search page code.

<?php
	$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');
	echo '<form name="form1" method="get" action="searchtitle.php">';
        echo 'Search by Title:';
        echo '<input type="text" name="words" value="'.$searchwords.'">';
        echo '<input type="submit" name="Submit" value="Submit">';
        echo '</form>';
	?>
	<br />
	<?php
	$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');
	echo '<form name="form2" method="get" action="searchalbum.php">';
        echo 'Search by Album:';
        echo '<input type="text" name="words" value="'.$searchwords.'">';
        echo '<input type="submit" name="Submit" value="Submit">';
        echo '</form>';
	?>
	<br />
	<?php
	$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');
	echo '<form name="form3" method="get" action="searchwriter.php">';
        echo 'Search by Writer:'; 
        echo '<input type="text" name="words" value="'.$searchwords.'">';
        echo '<input type="submit" name="Submit" value="Submit">';
        echo '</form>';
	?>

 

Results page for an Album search:

<?php 
// Full-Text Search Example 
// Connect to the database. 
require_once ('./includes/mysql_connect.php'); //connect to database

//Records to display per page.
$display = 10;

//Check if the number of required pages has been determined. 
if (isset($_GET['np'])) {   //Already been determined.
$num_pages = $_GET['np'];   
} else { //Need to determine.

    // Count the number of records
$query = "SELECT COUNT(*) FROM master_list WHERE album='$searchstring' ORDER BY song_id";
//$result = mysql_query($query);
//$num_records = mysql_num_rows($result);
    $result = @mysql_query ($query);
    $row = mysql_fetch_array ($result, MYSQL_NUM);
    $num_records = $row[0];	

//calculate number of pages.
if ($num_records > $display) {   
$num_pages = ceil($num_records/$display);   
} else {
$num_pages = 1;
}

} //end of np if.

//where to start returning results.
if (isset($_GET['s'])) {   
$start = $_GET['s'];
} else {
$start = 0;
} 
     
$searchstring = mysql_escape_string($_GET['words']); 

    { 
        $sql = "SELECT song_id, title, album, writer FROM master_list WHERE album='$searchstring' ORDER BY title LIMIT $start, $display"; 
	//$sql = "SELECT song_id, title, album, writer, MATCH (title, album, writer) AGAINST ('$searchstring') FROM master_list  
               //WHERE MATCH(title, album, writer) AGAINST ('$searchstring') ORDER BY title LIMIT $start, $display"; 
    }  
     
$result = mysql_query($sql) or die (mysql_error()); 
while ($row = mysql_fetch_object($result, MYSQL_ASSOC)) {
//while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
//while($row = mysql_fetch_object($result)){ 
      echo '<font size="-1">Title: '.stripslashes(htmlspecialchars($row->title)).'<br /></font>'; 
      echo '<font size="-1">Album: '.stripslashes(htmlspecialchars($row->album)).'<br /></font>';
      echo '<font size="-1">Writer: '.stripslashes(htmlspecialchars($row->writer)).'<br /></font>'; 
      echo '<hr size="-1" />'; 
    }  
// Make the links to other pages, if necessary.
if ($num_pages > 1) {

    echo '<br /><p>';
    // Determine what page the script is on.
    $current_page = ($start/$display) + 1;

    // If it's not the first page, make a Previous button.
    if ($current_page != 1) {
       echo '<a href="searchalbum.php?s='.($start - $display).'&np='.$num_pages.'&words='.$searchwords .'">Previous</a> ';   
    }

    // Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '<a href="searchalbum.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&words='.$searchwords.'">'.$i.'</a> ';   
} else {
echo $i.' ';
}
}

    // If it's not the last page, make a Next button.
    if ($current_page != $num_pages) {
       echo '<a href="searchalbum.php?s='.($start + $display).'&np='.$num_pages.'&words='.$searchwords.'">Next</a> ';   
    }

    echo '</p>';

} 
// End of links section.
?>

 

The first ten results are displayed and I know there's more. What am I doing wrong here I can't figure it out.

[tjodolv]

This is what's limiting the results to 10 per page:

<?php
//Records to display per page.
$display = 10;
?>

 

To display the rest, you have to work out a pagination function. There are different ways to solve it, but here's a couple of links that might help:

http://www.phpfreaks.com/tutorials/73/1.php

http://www.php-mysql-tutorial.com/php-mysql-paging.php

 

[mrelliott]

In all the other pages that have that same code (and have more then ten results) the first page displays ten and the next page displays another ten (if there is ten more) and so on.

 

Here's an example:

 

<?php # song_list.php
// This script retrieves all the records from the master_list table.

require_once ('./includes/mysql_connect.php'); // Connect to the db.

//Records to display per page.
$display = 10;

//Check if the number of required pages has been determined. 
if (isset($_GET['np'])) {   //Already been determined.
$num_pages = $_GET['np'];   
} else { //Need to determine.

    // Count the number of records
    $query = "SELECT COUNT(*) FROM master_list WHERE album ='Flash Gordon' ORDER BY track";   
    $result = @mysql_query ($query);
    $row = mysql_fetch_array ($result, MYSQL_NUM);
    $num_records = $row[0];

//calculate number of pages.
if ($num_records > $display) {   
$num_pages = ceil ($num_records/$display);   
} else {
$num_pages = 1;
}

} //end of np if.

//where to start returning results.
if (isset($_GET['s'])) {   
$start = $_GET['s'];
} else {
$start = 0;
} 

//Default column links. Removed space between} and? this was the problem with the links.
$link1 = "{$_SERVER['PHP_SELF']}?sort=tracka";
$link2 = "{$_SERVER['PHP_SELF']}?sort=titlea";
$link3 = "{$_SERVER['PHP_SELF']}?sort=lengtha";
$link4 = "{$_SERVER['PHP_SELF']}?sort=writera";

//sorting order determined?
if (isset($_GET['sort'])) { 

//switch conditional.
switch ($_GET['sort']) {
case 'tracka':
$order_by = 'track ASC';
$link1 = "{$_SERVER['PHP_SELF'] }?sort=trackd";
break;
case 'trackd':
$order_by = 'track DESC';
$link1 = "{$_SERVER['PHP_SELF'] }?sort=tracka";
break;
case 'titlea':
$order_by = 'title ASC';
$link2 = "{$_SERVER['PHP_SELF'] }?sort=titled";
break;
case 'titled':
$order_by = 'title DESC';
$link2 = "{$_SERVER['PHP_SELF'] }?sort=titlea";
break;
case 'lengtha':
$order_by = 'length ASC';
$link3 = "{$_SERVER['PHP_SELF'] }?sort=lengthd";
break;
case 'lengthd':
$order_by = 'length DESC';
$link3 = "{$_SERVER['PHP_SELF'] }?sort=lengtha";
break;
case 'writera':
$order_by = 'writer ASC';
$link4 = "{$_SERVER['PHP_SELF'] }?sort=writerd";
break;
case 'writerd':
$order_by = 'writer DESC';
$link4 = "{$_SERVER['PHP_SELF'] }?sort=writera";
break;
default:
$order_by = 'track ASC';
break;
}

// $sort will be appended to the pagination links.
$sort = $_GET['sort'];
} else { //use default sorting order.
$order_by = 'track ASC';  //missing single quote before semicolon.
$sort = 'tracka'; 
} 

// Make the query.
$query = "SELECT track, title, TIME_FORMAT( 
length, '%i:%S') AS length, album, writer, song_id FROM master_list WHERE album ='Flash Gordon' ORDER BY $order_by LIMIT $start, $display";		
$result = @mysql_query ($query); // Run the query.

//Album Titles
define (ALBUM_1, 'Queen (1973)');
define (ALBUM_2, 'Queen II (1974)');
define (ALBUM_3, 'Sheer Heart Attack (1974)');
define (ALBUM_4, 'A Night at the Opera (1975)');
define (ALBUM_5, 'A Day at the Races (1976)');
define (ALBUM_6, 'News of the World (1977)');
define (ALBUM_7, 'Jazz (1978)');
define (ALBUM_8, 'The Game (1980)');
define (ALBUM_9, 'Flash Gordon (1980)');
define (ALBUM_10, 'Hot Space (1982)');
define (ALBUM_11, 'The Works (1984)');
define (ALBUM_12, 'A Kind of Magic (1986)');
define (ALBUM_13, 'The Miracle (1989)');
define (ALBUM_14, 'Innuendo (1991)');
define (ALBUM_15, 'Made in Heaven (1995)');

echo '<center>'.ALBUM_9.'</center>';

// Table header.
echo '<table align="center" cellspacing="0" cellpadding="5"><tr>
<td align="left"><b><font size="-1"><a href="' . $link1 . '">Track</a></font></b></td>
<td align="left"><b><font size="-1"><a href="' . $link2 . '">Song Title</a></font></b></td>
<td align="left"><b><font size="-1"><a href="' . $link3 . '">Song Length</a></font></b></td>
<td align="left"><b><font size="-1"><a href="' . $link4 . '">Writer</a></font></b></td>
</tr>
';

// Fetch and print all the records.
$bg = '#000066'; //Background display.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#000066' ? '#003399' : '#000066'); //switch the bg color.
echo '<tr bgcolor="' . $bg . '">
<td align="left"><font size="-1">' . $row['track'] . '</font></td>
<td align="left"><font size="-1">' . $row['title'] . '</font></td>
<td align="left"><font size="-1">' . $row['length'] . '</font></td>
<td align="left"><font size="-1">' . $row['writer'] . '</font></td>
</tr>
';
}
echo '</table>';

mysql_free_result ($result); // Free up the resources.
mysql_close(); //Close connection.

// Make the links to other pages, if necessary.
if ($num_pages > 1) {

    echo '<br /><p>';
    // Determine what page the script is on.
    $current_page = ($start/$display) + 1;

    // If it's not the first page, make a Previous button.
    if ($current_page != 1) {
       echo '<a href="flashgordon.php?s=' . ($start - $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Previous</a> ';   
    }

    // Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '<a href="flashgordon.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&sort='.$sort.'">' .$i. '</a> ';   
} else {
echo $i.' ';
}
}

    // If it's not the last page, make a Next button.
    if ($current_page != $num_pages) {
       echo '<a href="flashgordon.php?s=' . ($start + $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Next</a> ';   
    }

    echo '</p>';

} 
// End of links section.
?>

 

It displays a list of 18 song titles and other info on two pages. Ten on the first, eight on the next page.

 

Why is this not working with a full text search?

[john010117]

I've had this problem before. Use mysql_real_escape_string() on the string the user's searching for.

[mrelliott]

Do I replace while ($row = mysql_fetch_object($result, MYSQL_ASSOC)) { with this new piece of code?

[JasonLewis]

john meant that for strings your putting in a mysql query use the mysql_real_escape_string() function. it helps prevent sql injection.

[mrelliott]

Can you show me how to do that?

 

[mrelliott]

will this help correct my pagination problem?

[JakeTheSnake3.0]

step 1: whenever a user does a search, store the entire results in a session variable.

 

step 2: when a page loads, reference the session variable to populate your page numbers and such...

[mrelliott]

Could you show me an example?

[JakeTheSnake3.0]

The best way is to make a class out of it; call it something like "Pagination" - and whenever the user does a search, create a new class instance.  The class should hold variables like "numPages" and "currPage"...from this information you can create links in a FOR loop that will do the following:

 

1) if the currPage variable is 1 - meaning the user is on the 1st page, then create links that link to page 2, 3, 4, etc. up until a set limit for the links (see google as an example)

 

2) if the currPage variable is greater than 1, then create links going to PREVIOUS pages...again, see google as an example.

 

This is just a little something to get you started.  The rest is up to you!

[mrelliott]

I'm having a dumb moment. Can you show me a code example. I took an online course on this and for some reason they never covered paginating a user's search. I really wish they would have, if they did I wouldn't be fighting this for the past three months now. I'm looking back on my session notes.

 

I've placed this at the top of my search page:

 

<?php
session_start();
$_SESSION['search'] ='$searchwords';
$_SESSION['results'] ='$searchstring';
?>

 

And this at the top of my results page:

<?php
session_start();
?>

 

I didn't get any error messages, but it didn't help get my results past the first ten.

[chigley]

<?php
session_start();
$_SESSION['search'] = $searchwords;
$_SESSION['results'] = $searchstring;
?>

 

You put the variables in single quotes

[JakeTheSnake3.0]

In all honesty, you shouldn't think that since you didn't learn 'pagination' in school that you're a goner; the simple truth to 'pagination' is just storing simple information in an array.  The 'pagination' part comes due to the fact that you're displaying links to the user.

 

Example:

 

content_file.php

-> contains the full story, un-paginated.

 

main.php

-> opens the content_file.php and 'paginates' it.  This is where most of the work will be done, and why it's best to make a class out of it.  The idea of pagination is to break up one big file into smaller, more manageable chunks.  The biggest hurdle is to break up the string (content_file.php) into an array.  You need to decide how many characters per page you want to display...or in more simpler terms, how many paragraphs per page you want to display.  Let's say you want 4 paragraphs of text per page to display; that means you need to go through your string and break it up into an array by the string "</p>"...provided that the file is properly HTML formatted, this shouldn't cause any problems and you'll now have an array containing portions of the once-larger file.

 

So that is the basics of paginating a file.  Now that you have that array, you can loop through the array; for every loop, create a link with the appropriate page number.  In your main.php, echo() the contents of the array element which corresponds to the page ID in the URL...and there you have it.

[mrelliott]

I was told that I should print out the codes. The only thing I can think of is that I have a problem with the count code.

 

$query = "SELECT COUNT(*) FROM master_list WHERE MATCH(album) AGAINST('$searchwords') ORDER BY song_id";
print $query;
    $result = @mysql_query ($query);
    $row = mysql_fetch_array ($result, MYSQL_NUM);
    $num_records = $row[0];	
print $result;
print $row;
print $num_records;

The result:
SELECT COUNT(*) FROM master_list WHERE MATCH(album) AGAINST('') ORDER BY song_id Resource id #3 Array0 

The code:
$sql = "SELECT song_id, title, album, writer FROM master_list WHERE MATCH(album) AGAINST('$searchwords')ORDER BY title LIMIT $start, $display"; 
print $sql;     
$result = mysql_query($sql) or die (mysql_error()); 
print $result;

The result:
SELECT song_id, title, album, writer FROM master_list WHERE MATCH(album) AGAINST('flash gordon')ORDER BY title LIMIT 0, 10 Resource id #4	

[mrelliott]

It was pointed out to me on another forum that my counting code had an empty string which showed up when I printed the code. The second piece of code was working properly (the string was full). I was looking at a printout of the whole code and deceided to try something. AND IT ACTUALLY WORKED!!!

 

I had to move the following piece of code

$searchwords = mysql_escape_string($_GET['words']);

from the middle of my page, just above the $sql code which was working (because this line was above it) and place it at the start of my code above the counting code which was not working (because this line of code was NOT above it). After three months of wondering what in the world I was doing wrong!!! Finally!!!