PDA

View Full Version : Pagination, limiting dispaly to x per page

Tjobbe
March 20th, 2006, 14:20
I picked up on this post (http://www.iwdn.net/showpost.php?p=42299&postcount=29) and its exactly what I need for a friends site, where he wants to have a limit of 10 news posts per page. --> http://www.severinfurneaux.co.uk/news.php

The original code is:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>

<?php



// DO NOT EDIT THIS

// EDIT THIS AND I CUT YOUR BALLS OFF

// DO NOT EDIT THIS

// DO NOT EDIT THIS



include 'admin/dbconnect.php';



$query = "SELECT * FROM journal ORDER BY ID DESC";

$rs = mysql_query($query);



//do we have results?

if($row = mysql_fetch_array($rs)) {



//loop through records

do {

$id = $row["id"];

$title = $row["title"];

$date = $row["date"];

$intro = $row["intro"];

$image = $row["image"];

$link = $row["link"];

$linktext = $row["linktext"];



echo "<div class='article'>

";

echo "<a href='".$link."'>

<img src='admin/uploads/".$image."' alt='".$title."' /></a>

";

echo "<div class='intro'><div class='date'>

".$date."

</div>

";

echo "".$title."

</div>

";

echo "<div class='content'>

<p>".$intro."</p>

";

echo "<p><a href='".$link."'>".$linktext."</a></p>

</div>

</div>

";





} while($row = mysql_fetch_array($rs));

}

else {

echo "no records";

}



//reclaim resources

mysql_free_result($rs);

?>

</body>

</html>

What I made is:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>

<?php



// DO NOT EDIT THIS

// EDIT THIS AND I CUT YOUR BALLS OFF

// DO NOT EDIT THIS

// DO NOT EDIT THIS



include 'admin/dbconnect.php';



$query = "SELECT * FROM journal LIMIT $start, $per_page ORDER BY ID DESC";

$rs = mysql_query($query);



//do we have results?

if($row = mysql_fetch_array($rs)) {



//loop through records

do {

$id = $row["id"];

$title = $row["title"];

$date = $row["date"];

$intro = $row["intro"];

$image = $row["image"];

$link = $row["link"];

$linktext = $row["linktext"];



echo "<div class='article'>

";

echo "<a href='".$link."'>

<img src='admin/uploads/".$image."' alt='".$title."' /></a>

";

echo "<div class='intro'><div class='date'>

".$date."

</div>

";

echo "".$title."

</div>

";

echo "<div class='content'>

<p>".$intro."</p>

";

echo "<p><a href='".$link."'>".$linktext."</a></p>

</div>

</div>

";





} while($row = mysql_fetch_array($rs));

}

else {

echo "no records";

}



//reclaim resources

mysql_free_result($rs);

?>

<?php
# current page
$page = ($_GET['page'] != '') ? $_GET['page'] : 1;

# total entries
$entries = mysql_fetch_assoc(mysql_query('select count(`id`) as `c` from `journal`'));
$entries = $entries['c'];

# entries per page
$per_page = 10;

# total pages
$pages = ceil( $entries / $per_page );
$pages = range(1,$pages);

# current page start
$start = $page * $per_page;

foreach($pages as $number) {
if($number == $page) {
echo "$number ";
} else {
echo "<a href=\"?page=$number\">$number</a> ";
}
}
?>

</body>

</html>

and it gives me the following error, what have I done wrong here?
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/bagnbox/public_html/sev/news.php on line 49
no records
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/bagnbox/public_html/sev/news.php on line 129
1 2
Dan
March 20th, 2006, 20:00
That usually means that there's an error with your query.

For the one on line 48.. try putting the LIMIT after the ORDER.

Now, maybe the way you're doing it is right, but 99% of the time, you loop through results like:

$query = mysql_query();
while($rs = mysql_fetch_assoc($query)) {
// stuff
}

Also, you define $start and $per_page AFTER it's used in the query, which is a problem. Try moving the pagination to the top, then instead of echo, do $pagination .= 'html'; and then echo $pagination; later on in the document.
Tjobbe
March 21st, 2006, 12:40
I've done what you said, bar the final instruction, and this is the result:

http://www.severinfurneaux.co.uk/news-arc.php

It shows the oldest first, and then only 5 of them, the page numberring is at the very bottom left, and is "1 0"

The error message metions line 7, which is;
$entries = mysql_fetch_assoc(mysql_query('select count(`id`) as `c` from `journal`'));

Its definelty doing something, but not sure what its doing what it is?

heres the full page;



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>
<?php
# current page
$page = ($_GET['page'] != '') ? $_GET['page'] : 1;

# total entries
$entries = mysql_fetch_assoc(mysql_query('select count(`id`) as `c` from `journal`'));
$entries = $entries['c'];

# entries per page
$per_page = 10;

# total pages
$pages = ceil( $entries / $per_page );
$pages = range(1,$pages);

# current page start
$start = $page * $per_page;
?>


<?php



// DO NOT EDIT THIS

// EDIT THIS AND I CUT YOUR BALLS OFF

// DO NOT EDIT THIS

// DO NOT EDIT THIS



include 'admin/dbconnect.php';



$query = "SELECT * FROM journal ORDER BY ID DESC LIMIT $start, $per_page";

$rs = mysql_query($query);



//do we have results?

if($row = mysql_fetch_array($rs)) {



//loop through records

do {

$id = $row["id"];

$title = $row["title"];

$date = $row["date"];

$intro = $row["intro"];

$image = $row["image"];

$link = $row["link"];

$linktext = $row["linktext"];



echo "<div class='article'>

";

echo "<a href='".$link."'>

<img src='admin/uploads/".$image."' alt='".$title."' /></a>

";

echo "<div class='intro'><div class='date'>

".$date."

</div>

";

echo "".$title."

</div>

";

echo "<div class='content'>

<p>".$intro."</p>

";

echo "<p><a href='".$link."'>".$linktext."</a></p>

</div>

</div>

";





} while($row = mysql_fetch_array($rs));

}

else {

echo "no records";

}



//reclaim resources

mysql_free_result($rs);



foreach($pages as $number) {
if($number == $page) {
echo "$number ";
} else {
echo "<a href=\"?page=$number\">$number</a> ";
}
}

?>

</body>

</html>
Christopher Lee
March 21st, 2006, 16:46
As always, just looked, haven't tested...

I don't see you explicitly passing a connection to it, and I don't see the connection (or the db selection) in the current page. I imagine, though, that those two items are being performed in one of the include files. Even so, explicitly setting that in the mysql_query as an experiment will at least eliminate that as a suspect. Also, to get more detailed error messages, I often use (on dev and test environments):


$rs = @mysql_query($strSQL, $myconn) or die("<p>Couldn't run query</p> <p><strong>$strSQL</strong></p> <p>". mysql_error() . "</p>");


Where $myconn is a my db connection. Of course, you don't want to use this in a production environment, but it will help as you develop your application.

However, if you want to keep your code close as possible to what you currently have you could always try something like:


# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}
Christopher Lee
March 21st, 2006, 16:50
D'oh...

Another case of me not reading the entire thread through! Sorry.

It looks like your database include is below that section in the document. Try putting it above.

Again apologies.


include 'admin/dbconnect.php';
Tjobbe
March 21st, 2006, 16:56
Aaah, thats better!

Now its going a bit mad though, playing about with DESC and ASC and it goes a bit crazy, right now its set to sort by id ASC, which means most recent first right? well, the bottom post is the most recent, and the older posts, which should be on page two are not being displayed..

heres the link;
http://www.severinfurneaux.co.uk/news-arc.php

and the code;



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'admin/dbconnect.php';
include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>
<?php
# current page
$page = ($_GET['page'] != '') ? $_GET['page'] : 1;

# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}

# entries per page
$per_page = 10;

# total pages
$pages = ceil( $entries / $per_page );
$pages = range(1,$pages);

# current page start
$start = $page * $per_page;
?>


<?php



// DO NOT EDIT THIS

// DO NOT EDIT THIS

// DO NOT EDIT THIS







$query = "SELECT * FROM journal ORDER BY ID DESC LIMIT $start, $per_page";

$rs = mysql_query($query);



//do we have results?

if($row = mysql_fetch_array($rs)) {



//loop through records

do {

$id = $row["id"];

$title = $row["title"];

$date = $row["date"];

$intro = $row["intro"];

$image = $row["image"];

$link = $row["link"];

$linktext = $row["linktext"];



echo "<div class='article'>

";

echo "<a href='".$link."'>

<img src='admin/uploads/".$image."' alt='".$title."' /></a>

";

echo "<div class='intro'><div class='date'>

".$date."

</div>

";

echo "".$title."

</div>

";

echo "<div class='content'>

<p>".$intro."</p>

";

echo "<p><a href='".$link."'>".$linktext."</a></p>

</div>

</div>

";





} while($row = mysql_fetch_array($rs));

}

else {

echo "no records";

}



//reclaim resources

mysql_free_result($rs);



foreach($pages as $number) {
if($number == $page) {
echo "$number ";
} else {
echo "<a href=\"?page=$number\">$number</a> ";
}
}

?>

</body>

</html>
Christopher Lee
March 21st, 2006, 17:51
Another quick glance:


# current page start
$start = $page * $per_page;


The logic in this is wrong. if page is two and per_page is ten, then the start would be from record 20->29, not record six. I'm not sure why only five records are being displayed, or why page two shows logic wise, but I wanted to get this up. More to come...

EDIT:
Geez, the logic is supposed to be ten records at a time, I think. Still working through it.
Tjobbe
March 21st, 2006, 17:59
Thanks Christopher, I'm all out of ideas myself, it completely fried my mind!

Many thanks for all your suggestions.
Christopher Lee
March 21st, 2006, 18:03
Aaah, thats better!

Now its going a bit mad though, playing about with DESC and ASC and it goes a bit crazy, right now its set to sort by id ASC, <snip>
<snip>


$query = "SELECT * FROM journal ORDER BY ID DESC LIMIT $start, $per_page";

</snip>


...still working on the logic of it. Hopefull will have more to report. Check the case on 'id' as well. I think DESC is the correct one in this case, tho.
Christopher Lee
March 21st, 2006, 18:20
Do me a favor, on your development copy, under where you fill the variable $entries, verify that it has a value by:

echo '<p>Entries has the value: ' . $entries . '</p>';

let me know if it has anything in it.
Tjobbe
March 21st, 2006, 18:34
Not quite sure what you mean there, but here are all the files;

dbconnect.php;

<?php

$c = mysql_connect ('localhost', 'USERNAME', 'PASSWORD');

if (!$c) die("Crap.. not again " . mysql_error());

mysql_select_db('DBNAME', $c) or die(" Grrrr " . mysql_error());

?>


Submit.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Add a new article</title>
<link href="../display.css" rel="stylesheet" type="text/css" />
</head>

<body>
<form enctype="multipart/form-data" name="form" method="post" action="store.php">
<table border="0" align="center" cellpadding="4" cellspacing="2" bgcolor="#FFFFFF">
<tr>
<td width="135">Title:</td>
<td>
<p>
<input name="title" id="title" value="Article Title" />
</p>
</td>
</tr>
<tr>
<td>Date:</td>
<td>
<p>
<input name="date" id="date" value="Todays Date" />
</p>
</td>
</tr>
<tr>
<td width="135">Introduction:</td>
<td rowspan="2">
<textarea name="intro" cols="35" rows="5" id="intro">Brief Description</textarea>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />Choose a file to upload:
</td>
<td>
<input name="uploadedfile" type="file" />
</td>
</tr>
<tr>
<td>Links to URL: </td>
<td>
<input name="link" id="link" value="http://" />
</td>
</tr>
<tr>
<td>Anchor Text (link name) </td>
<td>
<input name="linktext" id="linktext" value="e.g: Click Here" />
</td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value="Submit" />
</div>
</td>
</tr>
</table>
</form>
</body>
</html>



Store.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<?php

$target_path = "uploads/";

$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded";
} else{
echo "There was an error uploading the file, please try again!";
}
include 'dbconnect.php';



$sqlquery = "INSERT INTO `journal` ( `id`, `title`, `date`, `intro`, `image`, `link`, `linktext`) VALUES('$id', '$title', '$date', '$intro', '". basename( $_FILES['uploadedfile']['name']). "', '$link', '$linktext')";


$results = mysql_query($sqlquery);

mysql_close();

print "<html><body><center>";
print "<p>You have just entered this record<p>";
print "<strong>title :</strong> $title<br>";
print "<strong>date :</strong> $date<br>";
print "<strong>intro :</strong> $intro<br>";
print "<strong>linktext :</strong> $description<br>";
print "<strong>image :</strong> $image<br>";
print "</body></html>";
?>

</body>
</html>


news.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'admin/dbconnect.php';
include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>
<?php
# current page
$page = ($_GET['page'] != '') ? $_GET['page'] : 1;

# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}

# entries per page
$per_page = 10;

# total pages
$pages = ceil( $entries / $per_page );
$pages = range(1,$pages);

# current page start
$start = $page * $per_page;
?>


<?php



// DO NOT EDIT THIS

// EDIT THIS AND I CUT YOUR BALLS OFF

// DO NOT EDIT THIS

// DO NOT EDIT THIS







$query = "SELECT * FROM journal ORDER BY ID ASC LIMIT $start, $per_page";

$rs = mysql_query($query);



//do we have results?

if($row = mysql_fetch_array($rs)) {



//loop through records

do {

$id = $row["id"];

$title = $row["title"];

$date = $row["date"];

$intro = $row["intro"];

$image = $row["image"];

$link = $row["link"];

$linktext = $row["linktext"];



echo "<div class='article'>

";

echo "<a href='".$link."'>

<img src='admin/uploads/".$image."' alt='".$title."' /></a>

";

echo "<div class='intro'><div class='date'>

".$date."

</div>

";

echo "".$title."

</div>

";

echo "<div class='content'>

<p>".$intro."</p>

";

echo "<p><a href='".$link."'>".$linktext."</a></p>

</div>

</div>

";





} while($row = mysql_fetch_array($rs));

}

else {

echo "no records";

}



//reclaim resources

mysql_free_result($rs);



foreach($pages as $number) {
if($number == $page) {
echo "$number ";
} else {
echo "<a href=\"?page=$number\">$number</a> ";
}
}

?>

</body>

</html>

Hope thats what your looking for?
Christopher Lee
March 21st, 2006, 18:38
Sorry, I was vague.

Where you have:


# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}


in news.php, add the line above so it looks like:



# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}

echo '<p>Entries has the value: ' . $entries . '</p>';



Then run it, and let me know what that line looks like on your page.
Tjobbe
March 21st, 2006, 18:43
doing it now ;o)
Tjobbe
March 21st, 2006, 18:44
http://www.severinfurneaux.co.uk/news-arc.php

"Entries has the value: 15"
Christopher Lee
March 21st, 2006, 20:02
Right under my eyes:
Change:

# current page start
$start = $page * $per_page;

To:

# current page start
$start = ($page-1) * $per_page;
Tjobbe
March 21st, 2006, 20:08
Beautifull, many, many thanks CL, thats fixed it!
Christopher Lee
March 21st, 2006, 20:22
I basically had to rewrite the page to find the error with my own eyes. A couple of quick notes. Make sure you always filter your values before putting said values into a query of any kind. Store.php appears to rely on register_globals, and doesn't appear to sanitize the variables with mysql_real_escape_string(). Always check that the variable is the right type you are looking for, and that its contents are not poisonous.

Here is my rewrite, in case you are interested:


<?php

include 'admin/dbconnect.php'; //mysql db stuff


function EchoNeat($str, $spaces=0, $newlines=1){
if( !is_int($spaces) ){
die('EchoNeat Error: "spaces" must be an integer!'); //bleah
}
if( !is_int($newlines) ){
die('EchoNeat Error: "newlines" must be an integer!'); //bleah
}

if( $spaces > 0 ){
$str = str_repeat(" ", $spaces) . $str;
}

if( $newlines > 0 ){
$str .= str_repeat("\n", $newlines);
}

echo $str;

}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Severin Furneaux > News</title>

<link href="news_display.css" rel="stylesheet" type="text/css" />

<script src="menu.js" language="javascript"type="text/javascript"></script>

</head>



<body <?php include 'news_onload.php' ;?> >

<div class="menu"><?php include 'news_menu.php' ;?></div>
<?php
# current page, filter these values to avoid nasties
#done off the top of my head, so make sure to double check the logic!
//$page = ($_GET['page'] != '') ? $_GET['page'] : 1;
$page = $_GET['page'];
if( empty($page) || !is_numeric($page) ){
$page = 1;
}elseif( !ctype_digit($page) ){
$page = 1;
}

# total entries
$rs = mysql_query('select count(`id`) from `journal`');
if( is_resource($rs) ){
$entries = mysql_result($rs, 0, 0);
}else{
die('An error occurred' . mysql_error());
}

//reclaim resources
mysql_free_result($rs);
unset($rs);

# entries per page
$per_page = 10;

# total pages
if( $entries > 0 ){
$pages = ceil( $entries / $per_page );
$pages = range(1,$pages);
#sanity checking.
if( $page > count($pages) ){
$page = 1;
}
}

# current page start
$start = ($page-1) * $per_page;

//note the case change of the id field. note the backtick of the tablename
$query = "SELECT * FROM `journal` ORDER BY `id` DESC LIMIT $start, $per_page";

$rs = mysql_query($query);



//do we have results? Note the change. Some people despise mysql_num_rows, but I don't think
//load concerns will be at issue here.
if( is_resource($rs) ){
if( mysql_num_rows($rs) > 0 ){
//loop through records
while( $row = mysql_fetch_array($rs) ){
EchoNeat('<div class="article">');
EchoNeat('<a href="' . $row['link'] . '">',0 , 3);
EchoNeat('<img src="admin/uploads/' . $row['image'] . '" alt="' . $row['title'] . '" /></a>');
EchoNeat('<div class="intro"><div class="date">', 0, 3);
EchoNeat($row['date'], 4, 3);
EchoNeat('</div>');
EchoNeat($row['title'], 4);
EchoNeat('</div>');
EchoNeat('<div class="content">');
EchoNeat('<p>' . $row['intro'] . '</p>');
EchoNeat('<p><a href="' . $row['link'] . '">' . $row['linktext'] . '</a></p>');
EchoNeat('</div>');
EchoNeat('</div>');
}
}else{
EchoNeat($query);
EchoNeat("no records");
}
//reclaim resources
mysql_free_result($rs);
}else{
EchoNeat('An error occurred processing this query. Please notify the administrator');
}

EchoNeat('<div id="pages">');
EchoNeat('<a href="?page=1">&lt; First</a> ');
foreach($pages as $number){
if($number == $page){
echo "$number ";
}else{
echo "<a href=\"?page=$number\">$number</a> ";
}
}
if( is_array($pages) && count($pages) > 0 ){
EchoNeat(' Pages <a href="?page=' . count($pages) . '">Last &gt;</a> (Total Records: ' . $entries .')');
}
EchoNeat('</div>');

?>

</body>

</html>