pagination...

[crawfd]

Trying to get pagination working on a query that will adjust the number of rows via a case method (for a pull down menu). After redefining the search I want to display its respective amount of rows and if over the limit, display 2,3,4 etc. Currently after I redefine the search by selecting an option I get a invalid mysql error. Right now it's somewhat working but doesn't stop displaying >> after the last group record amount and it also isn't displaying page numbers. In the pagination area it's just displaying << and >>.

if (empty($offset)) {
$offset=0;
}

$query = "Select * FROM $table WHERE username = '".$_SESSION['username']."' limit $offset,8";

switch($_POST['sort']) {  

case 1:  

$query.=" ORDER by date DESC";  

break;  

case 2:  

$query.=" AND awareness = 'Low' ORDER by awareness";  

break;  

case 3:  

$query.=" AND awareness = 'Medium' ORDER by awareness";  

break;  

case 4:  

$query.=" AND awareness = 'High' ORDER by awareness";  

break; 

case 5:  

$query.=" AND dreamstatus = 'Past' ORDER by dreamstatus";  

break; 

case 6:  

$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";  

break; 

}

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

function listmenu() {

return "<form name=\"form2\" method=\"post\" action=\"home.php?page=manage&action=entries\"><select name=\"sort\" style=\"font-size: 11px; font-weight: bold; color: 000000;\" onchange=\"document.forms['form2'].submit()\"> 
        <option selected>Sort by Category</option>
        <option value=\"1\">Sort by Date</option> 
        <option value=\"2\">Sort by Awareness(Low)</option> 
        <option value=\"3\">Sort by Awareness(Medium)</option> 
        <option value=\"4\">Sort by Awareness(High)</option> 
        <option value=\"5\">Sort by Past Journals</option> 
        <option value=\"6\">Sort by Recent Journals</option> 
        </select></form>";
		}

print "<table align=\"center\" width=\"95%\" border=\"0\" cellspacing=\"1\" cellpadding=\"2\" class=\"table_border\">\n";
print "<tr><td colspan=\"6\">" . listmenu() . "</td></tr>";

if (mysql_num_rows ($result) == 0) {

print "<tr class=\"cells\"><td colspan=\"6\"><font color=red>No entries found. Please select another search criteria.</font></td></tr>";

} else {

$p .= "<tr class=\"header_cell\">";

$p .= "<td>Date</td><td>Title</td><td>Past or Recent</td><td>Awareness</td><td>Modify</td><td>Delete</td></tr>\n";
echo $p;

while(list($id,$date,$username,$title,$dreamstatus,$entry,$awareness,$pagination) = mysql_fetch_row($result)){

	$d = explode("-",$date);
	$titletrimmed = substr($title, 0, 30);
	$thedate = "$d[2]/$d[1]/$d[0]";
	$print  = "\n<tr class=\"cells\">\n";
	$print .= "<td><li>$date</td><td><b><a href=\"home.php?page=manage&action=modify&entry=$id\">$titletrimmed...</a></b></td>\n";
	$print .= "<td>$dreamstatus</td>";
	$print .= "<td>$awareness</td>";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=modify&entry=$id\">";
	$print .= "Modify</a></td>\n";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=remove&entry=$id\">";
	$print .= "Delete</a></td>\n";
	$print .= "</tr>\n";
	echo $print;

}
}

// NEXT - PREVIOUS LINKS \\
echo "<center>Pages: ";
$pages=intval($numrows/$limit);
if ($numrows%$limit) {
    $pages++;
	if($pages > $limit){
	echo"";
	}
}
if ($offset != 0) {
    $prevoffset=$offset-$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$prevoffset\">&lt;&lt;</a> &nbsp; \n";
}

if ( $pages != 1 ) {

for ($i=1;$i<=$pages;$i++) { 
    $newoffset=$limit*($i-1);
    if ( ((($offset)/$limit)==($i-1)) ) {
        echo "<font class=i>$i</font> | \n";
    } else {
        echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">$i</a> | \n";
    }
}
}    

if (!((($offset)/$limit)+1==$pages) && $pages!=1) {
    $newoffset=$offset+$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">&gt;&gt;</a><p>\n";
}


echo"</table><br></center>";

} 

[bastien]

your query is backwards...the LIMIT clause has to be last

$query = "Select * FROM $table WHERE username = '".$_SESSION['username']."' 

switch($_POST['sort']) {   

case 1:   

$query.=" ORDER by date DESC";   

break;   

case 2:   

$query.=" AND awareness = 'Low' ORDER by awareness";   

break;   

case 3:   

$query.=" AND awareness = 'Medium' ORDER by awareness";   

break;   

case 4:   

$query.=" AND awareness = 'High' ORDER by awareness";   

break;  

case 5:   

$query.=" AND dreamstatus = 'Past' ORDER by dreamstatus";   

break;  

case 6:   

$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";   

break;  

}//end switch

$query .= " limit $offset,8"; 

$result = mysql_query($query,$db) or die ("Can't complete query because ".mysql_error()); 

[crawfd]

That fixed the query issue, but the pagination area still isn't displaying the numbers, nor is it grouping the limit properly (8 per page), it just lists all the rows.

[bubblenut]

Can you echo the query out just before you execute it, it may be not be quite right

[crawfd]

Echoe'd the query just below the case statement and it returned:

Select * FROM entries WHERE username = 'crawfd' ORDER by date DESC

so it doesn't to me reading the limit.....I think.

if (empty($offset)) {
$offset=0;
}

include("manage/modify.php");

$query = "Select * FROM $table WHERE username = '".$_SESSION['username']."'";

switch($_POST['sort']) {  

case 1:  
$query.=" ORDER by date DESC";  
break;  

case 2:  
$query.=" AND awareness = 'Low' ORDER by awareness";  
break;  

case 3:  
$query.=" AND awareness = 'Medium' ORDER by awareness";  
break;  

case 4:  
$query.=" AND awareness = 'High' ORDER by awareness";  
break; 

case 5:  
$query.=" AND dreamstatus = 'Past' ORDER by dreamstatus";  
break; 

case 6:  
$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";  
break; 

$query.=" limit $offset, 8"; 	

}

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

	function listmenu() {

	return "<form name=\"form2\" method=\"post\" action=\"home.php?page=manage&action=entries\"><select name=\"sort\" style=\"font-size: 11px; font-weight: bold; color: 000000;\" onchange=\"document.forms['form2'].submit()\"> 
    <option selected>Sort by Category</option>
	<option value=\"1\">Sort by Date</option> 
    <option value=\"2\">Sort by Awareness(Low)</option> 
    <option value=\"3\">Sort by Awareness(Medium)</option> 
    <option value=\"4\">Sort by Awareness(High)</option> 
    <option value=\"5\">Sort by Past Journals</option> 
    <option value=\"6\">Sort by Recent Journals</option> 
    </select></form>";
	}

	print "<table align=\"center\" width=\"95%\" border=\"0\" cellspacing=\"1\" cellpadding=\"2\" class=\"table_border\">\n";
	print "<tr><td colspan=\"6\">" . listmenu() . "</td></tr>";

	if (mysql_num_rows ($result) == 0) {

	print "<tr class=\"cells\"><td colspan=\"6\"><font color=red>No entries found. Please select another search criteria.</font></td></tr>";

		} else {

		$p .= "<tr class=\"header_cell\">";
		$p .= "<td>Date</td><td>Title</td><td>Past or Recent</td><td>Awareness</td><td>Modify</td><td>Delete</td></tr>\n";
		echo $p;

		while(list($id,$date,$username,$title,$dreamstatus,$entry,$awareness,$pagination) = mysql_fetch_row($result)){

	$d = explode("-",$date);
	$titletrimmed = substr($title, 0, 30);
	$thedate = "$d[2]/$d[1]/$d[0]";
	$print  = "\n<tr class=\"cells\">\n";
	$print .= "<td><li>$date</td><td><b><a href=\"home.php?page=manage&action=modify&entry=$id\">$titletrimmed...</a></b></td>\n";
	$print .= "<td>$dreamstatus</td>";
	$print .= "<td>$awareness</td>";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=modify&entry=$id\">";
	$print .= "Modify</a></td>\n";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=remove&entry=$id\">";
	$print .= "Delete</a></td>\n";
	$print .= "</tr>\n";
	echo $print;

}
}

// NEXT - PREVIOUS LINKS \\

echo "<center>Pages: ";
$pages=intval($numrows/$limit);
if ($numrows%$limit) {
    $pages++;
	if($pages > $limit){
	echo"";
	}
}
if ($offset != 0) {
    $prevoffset=$offset-$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$prevoffset\">&lt;&lt;</a> &nbsp; \n";
}

if ( $pages != 1 ) {

for ($i=1;$i<=$pages;$i++) { 
    $newoffset=$limit*($i-1);
    if ( ((($offset)/$limit)==($i-1)) ) {
        echo "<font class=i>$i</font> | \n";
    } else {
        echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">$i</a> | \n";
    }
}
}    

if (!((($offset)/$limit)+1==$pages) && $pages!=1) {
    $newoffset=$offset+$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">&gt;&gt;</a><p>\n";
}

[bastien]

the ending } from the case statement needs to be before the $query.= " limit....";

otherwise php assumes its part of the case statement...

see my previuous post

case 6:    

$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";    

break;   

}//end switch 

$query .= " limit $offset,8"; 

[crawfd]

Overlooked that, ...now it's selecting the limit from the list of rows. Now the only existing issue is the page numbers. They aren't displaying, however the << and >> are displaying. When I'm at the limit of number of rows, >> still displays and it shouldn't.

[bastien]

some quick problems i noted:

echo "<center>Pages: ";
$pages=intval($numrows/$limit);          //where does the $numrows variable come from?
if ($numrows%$limit) {                   //should be a comparison here ie. if ($numrows%$limit==0)
    $pages++;
    if($pages > $limit){
    echo"";
    }
}

[crawfd]

I added the $numrows variable (which I overlooked), however I need to figure out how to get the number of rows the database contains after a select is made in the case method, and then display the pagination results accordingly with the proper amount of records that match the criteria. Right now after case selection the pagination displays numbers greater than it should, and not recognizing num of rows from query. Pay close attention to '$numrows = mysql_result($result, 0, 0);' below the case statement.

if (empty($offset)) {
$offset=0;
}

include("manage/modify.php");

$query = "Select * FROM $table WHERE username = '".$_SESSION['username']."'";

switch($_POST['sort']) {  

case 1:  

$query.=" ORDER by date DESC";  

break;  

case 2:  

$query.=" AND awareness = 'Low' ORDER by awareness";  

break;  

case 3:  

$query.=" AND awareness = 'Medium' ORDER by awareness";  

break;  

case 4:  

$query.=" AND awareness = 'High' ORDER by awareness";  

break; 

case 5:  

$query.=" AND dreamstatus = 'Past' ORDER by dreamstatus";  

break; 

case 6:  

$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";  

break; 
	}

$limit = 15;
$query.=" limit $offset, $limit"; 	


$result = mysql_query($query,$db);
$numrows = mysql_result($result, 0, 0);


function listmenu() {

return "<form name=\"form2\" method=\"post\" action=\"home.php?page=manage&action=entries\"><select name=\"sort\" style=\"font-size: 11px; font-weight: bold; color: 000000;\" onchange=\"document.forms['form2'].submit()\"> 
        <option selected>Sort by Category</option>
        <option value=\"1\">Sort by Date</option> 
        <option value=\"2\">Sort by Awareness(Low)</option> 
        <option value=\"3\">Sort by Awareness(Medium)</option> 
        <option value=\"4\">Sort by Awareness(High)</option> 
        <option value=\"5\">Sort by Past Journals</option> 
        <option value=\"6\">Sort by Recent Journals</option> 
        </select></form>";
		}

	print "<table align=\"center\" width=\"95%\" border=\"0\" cellspacing=\"1\" cellpadding=\"2\" class=\"table_border\">\n";
	print "<tr><td colspan=\"6\">" . listmenu() . "</td></tr>";

	if (mysql_num_rows ($result) == 0) {

	print "<tr class=\"cells\"><td colspan=\"6\"><font color=red>No entries found. Please select another search criteria.</font></td></tr>";

		} else {

		$p .= "<tr class=\"header_cell\">";
		$p .= "<td>Date</td><td>Title</td><td>Past or Recent</td><td>Awareness</td><td>Modify</td><td>Delete</td></tr>\n";
		echo $p;


		while(list($id,$date,$username,$title,$dreamstatus,$entry,$awareness,$pagination) = mysql_fetch_row($result)){

	$d = explode("-",$date);
	$titletrimmed = substr($title, 0, 30);
	$thedate = "$d[2]/$d[1]/$d[0]";
	$print  = "\n<tr class=\"cells\">\n";
	$print .= "<td><li>$date</td><td><b><a href=\"home.php?page=manage&action=modify&entry=$id\">$titletrimmed...</a></b></td>\n";
	$print .= "<td>$dreamstatus</td>";
	$print .= "<td>$awareness</td>";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=modify&entry=$id\">";
	$print .= "Modify</a></td>\n";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=remove&entry=$id\">";
	$print .= "Delete</a></td>\n";
	$print .= "</tr>\n";
	echo $print;

}
}

// NEXT - PREVIOUS LINKS \\

echo "<center>Pages: ";
$pages=intval($numrows/$limit);
if ($numrows%$limit==0) {
    $pages++;
	if($pages > $limit){
	echo"";
	}
}
if ($offset != 0) {
    $prevoffset=$offset-$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$prevoffset\">&lt;&lt;</a> &nbsp; \n";
}

if ( $pages != 1 ) {

for ($l=1;$l<=$pages;$l++) { 
    $newoffset=$limit*($l-1);
    if ( ((($offset)/$limit)==($l-1)) ) {
        echo "<font class=i>$l</font> | \n";
    } else {
        echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">$l</a> | \n";
    }
}
}    

if (!((($offset)/$limit)+1==$pages) && $pages!=1) {
    $newoffset=$offset+$limit;
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">&gt;&gt;</a><p>\n";
}


echo"</table><br></center>";

[bastien]

won't work...the numrows will likely be the limit amount...what i do is run another query with the same where clause without the limit to get a total resultset back

[crawfd]

I'm not sure I follow, could you give me an example?

[bastien]

If you set the query LIMIT to 10, then the number of rows returned = 10...therefore the pagination scheme dies...so solutions is to duplicate the query without the limit clause to match the total records in the database for the where clause

ie

$limit = 15; 
    $query1 = $query." limit $offset, $limit";      


$result = mysql_query($query,$db); //total results
$total_rows = mysql_num_rows($result);
mysql_free_result($result);

$result1 = mysql_query($query,$db); //actual results
$numrows = mysql_result($result, 0, 0); 

//your code

echo "<center>Pages: "; 
$pages=intval($total_rows/$limit); 
if ($total_rows%$limit!=0) { 
    $pages++; 
}
    if($pages > $limit){ 
    echo""; 
    } 

if ($offset != 0) { 
    $prevoffset=$offset-$limit; 
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$prevoffset\">&lt;&lt;</a> &nbsp; \n"; 
} 

if ( $pages != 1 ) { 

for ($l=1;$l<=$pages;$l++) {  
    $newoffset=$limit*($l-1); 
    if ( ((($offset)/$limit)==($l-1)) ) { 
        echo "<font class=i>$l</font> | \n"; 
    } else { 
        echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">$l</a> | \n"; 
    } 
} 
}     

if (!((($offset)/$limit)+1==$pages) && $pages!=1) { 
    $newoffset=$offset+$limit; 
    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">&gt;&gt;</a><p>\n"; 
} 



} 

[crawfd]

Bastien,

Added the query, but not get the following error message:

"4 is not a valid MySQL result resource" and not displaying any rows.

if (empty($offset)) {
$offset=0;
}

include("manage/modify.php");

$query = "Select * FROM $table WHERE username = '".$_SESSION['username']."'";

switch($_POST['sort']) {  

case 1:  
$query.=" ORDER by date DESC";  
break;  

case 2:  
$query.=" AND awareness = 'Low' ORDER by awareness";  
break;  

case 3:  
$query.=" AND awareness = 'Medium' ORDER by awareness";  
break;  

   case 4:  

    $query.=" AND awareness = 'High' ORDER by awareness";  

    break; 

   case 5:  

    $query.=" AND dreamstatus = 'Past' ORDER by dreamstatus";  

    break; 

case 6:  
$query.=" AND dreamstatus = 'Recent' ORDER by dreamstatus";  
break; 
}

$limit = 10;
$query1 = $query." limit $offset, $limit";    	

$result = mysql_query($query,$db); //total results 
$total_rows = mysql_num_rows($result); 
mysql_free_result($result); 

$result1 = mysql_query($query,$db); //actual results 
$numrows = mysql_result($result, 0, 0); 


function listmenu() {

return "<form name=\"form2\" method=\"post\" action=\"home.php?page=manage&action=entries\"><select name=\"sort\" style=\"font-size: 11px; font-weight: bold; color: 000000;\" onchange=\"document.forms['form2'].submit()\"> 
        <option selected>Sort by Category</option>
		<option value=\"1\">Sort by Date</option> 
        <option value=\"2\">Sort by Awareness(Low)</option> 
        <option value=\"3\">Sort by Awareness(Medium)</option> 
        <option value=\"4\">Sort by Awareness(High)</option> 
        <option value=\"5\">Sort by Past Journals</option> 
        <option value=\"6\">Sort by Recent Journals</option> 
        </select></form>";
		}

	print "<table align=\"center\" width=\"95%\" border=\"0\" cellspacing=\"1\" cellpadding=\"2\" class=\"table_border\">\n";
	print "<tr><td colspan=\"6\">" . listmenu() . "</td></tr>";

	if (mysql_num_rows ($result) == 0) {

	print "<tr class=\"cells\"><td colspan=\"6\"><font color=red>No entries found. Please select another search criteria.</font></td></tr>";

		} else {

		$p .= "<tr class=\"header_cell\">";
		$p .= "<td>Date</td><td>Title</td><td>Past or Recent</td><td>Awareness</td><td>Modify</td><td>Delete</td></tr>\n";
		echo $p;


		while(list($id,$date,$username,$title,$dreamstatus,$entry,$awareness,$pagination) = mysql_fetch_row($result)){

	$d = explode("-",$date);
	$titletrimmed = substr($title, 0, 30);
	$thedate = "$d[2]/$d[1]/$d[0]";
	$print  = "\n<tr class=\"cells\">\n";
	$print .= "<td><li>$date</td><td><b><a href=\"home.php?page=manage&action=modify&entry=$id\">$titletrimmed...</a></b></td>\n";
	$print .= "<td>$dreamstatus</td>";
	$print .= "<td>$awareness</td>";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=modify&entry=$id\">";
	$print .= "Modify</a></td>\n";
	$print .= "<td align=\"center\"><a href=\"home.php?page=manage&action=remove&entry=$id\">";
	$print .= "Delete</a></td>\n";
	$print .= "</tr>\n";
	echo $print;

}
}

// NEXT - PREVIOUS LINKS \\

echo "<center>Pages: ";  

$pages=intval($total_rows/$limit);  

if ($total_rows%$limit!=0) {  

    $pages++;  

} 
    if($pages > $limit){  

    echo"";  

    }  

if ($offset != 0) {  

    $prevoffset=$offset-$limit;  

    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$prevoffset\">&lt;&lt;</a> &nbsp; \n";  

}  

if ( $pages != 1 ) {  

for ($l=1;$l<=$pages;$l++) {   
    $newoffset=$limit*($l-1);  
    if ( ((($offset)/$limit)==($l-1)) ) {  
        echo "<font class=i>$l</font> | \n";  
    } else {  
        echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">$l</a> | \n";  
    }  
}  
}      

if (!((($offset)/$limit)+1==$pages) && $pages!=1) {  

    $newoffset=$offset+$limit;  

    echo "<a class=i href=\"$PHP_SELF?page=manage&action=entries&offset=$newoffset\">&gt;&gt;</a><p>\n";  

}  

[dnast]

It looks like

$result1 = mysql_query($query,$db); //actual results

should be

$result = mysql_query($query1,$db); //actual results

[bastien]

whoops, another fat fingered day

[crawfd]

worked, thanks................

[dnast]

Originally posted by bastien
whoops, another fat fingered day

Don't you just love when that happens? 😉