[RESOLVED] Pagination of results won't get past 10. Why?

[mrelliott]

I have a small search engine for a database of songs. I've divided the search and results codes into seperate pages. For some reason I can't get my results to go past 10 even thought I know that there are more records then 10. Can anyone tell me what I'm doing wrong here?

Search engine 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 code:

<?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['no problem'])) {   //Already been determined.
$num_pages = $_GET['no problem'];   

} 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 no problem 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).'&no problem='.$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))).'&no problem='.$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).'&no problem='.$num_pages.'&words='.$searchwords.'">Next</a> ';   
}

echo '</p>';

} 
// End of links section.
?>

[jmack159]

in the beginning of your script, you set $display to 10, but you never change it based on what the start is. so in every query, no matter what the start is, your display is still always 10. you should do: $start+$display, for your ending limit.

[mrelliott]

I don't understand what you mean. Is it this section of code you're talking about? It already has $start + $display in it. Isn't that correct? Or do you mean a different section of code.

// 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> ';

}

[cgraz]

Try

print $sql;

to see what kind of query string you're sending mysql. Likely start is not getting set, and is defaulting to zero.

[mrelliott]

Set print for both the count part of the code and the other query. Here's what I get.

  

$query = "SELECT COUNT(*) FROM master_list WHERE album='$searchwords' ORDER BY song_id";

Result:
SELECT COUNT(*) FROM master_list WHERE album='' ORDER BY song_id

$sql = "SELECT song_id, title, album, writer FROM master_list WHERE album='$searchwords' ORDER BY title LIMIT $start, $display";

Result:
SELECT song_id, title, album, writer FROM master_list WHERE album='flash gordon' ORDER BY title LIMIT 0, 10

I'm going to change the count code to WHERE MATCH (album) AGAINST('$searchwords') and see if that helps.

[mrelliott]

I've changed my codes to the following. I may be wrong but it looks like the problem might be the select count code.

$query = "SELECT COUNT(*) FROM master_list WHERE MATCH(album) AGAINST('$searchwords') ORDER BY song_id";

Result:
SELECT COUNT(*) FROM master_list WHERE MATCH(album) AGAINST('') ORDER BY song_id

$sql = "SELECT song_id, title, album, writer FROM master_list WHERE MATCH(album) AGAINST('$searchwords')ORDER BY title LIMIT $start, $display"; 

Result:
SELECT song_id, title, album, writer FROM master_list WHERE MATCH(album) AGAINST('flash gordon')ORDER BY title LIMIT 0, 10

[mrelliott]

I added print to $result, $row and $num_records in the select count code and print $result to the $sql code. Does this mean my count code isn't working?

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

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

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

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

[jmack159]

if i understand your first question correctly, you cannot get past result 10.

in your query, you write:

$sql = "SELECT song_id, title, album, writer FROM master_list WHERE album='$searchstring' ORDER BY title LIMIT $start, $display";

display is previously set to 10:

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

but you never change it based on what the start is, so your query spans from start, to row 10, no matter what the start is. so the query will get all the rows up until row 10. i doesn't matter what start is set to as you have it, it is always limited to row 10

so you need to make your query end at $start + $display. so if your start is 15, your end limit should be 25, not 10.

try this for your sql:

$end = $start + $display;

$sql = "SELECT song_id, title, album, writer FROM master_list WHERE album='$searchstring' ORDER BY title LIMIT $start, $end";

hope this is more clear.

[cgraz]

jmack159 wrote:

but you never change it based on what the start is, so your query spans from start, to row 10, no matter what the start is. so the query will get all the rows up until row 10. i doesn't matter what start is set to as you have it, it is always limited to row 10

so you need to make your query end at $start + $display. so if your start is 15, your end limit should be 25, not 10.

That's not correct. The second parameter tells MySQL how many rows to grab, starting at the start. From the mysql Manual:

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

The problem isn't the 10, but rather than $start is always equal to zero. So either $_GET['s'] is not set, or, it is set to zero.

[jmack159]

sorry about that, now that i thought about it, cgraz is correct. [brain fart :o ]

can you post the html generated for the page navigation (ie the "prev 2 3 4 next")

what cgraz said makes more sense, because in all your examples, the start is always 0. so do the links that are generated, do they list the correct s=? in the url?

[Talguy]

I do plan to migrate it later on but i need to get a cheap comp first

[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!!!

[bradgrafelman]

Glad you got it working! Don't forget to mark this thread resolved.