x number of results per page

[creole]

Howdy. this is in relation to the last few posts I have made, but I wanted to keep them seperate.

I have a "updates" page for one of my clients. http://www.plusoneonline.com/home. I have recently put all the updates in a DB and would like to display the 5 most recent on the main page with a generated link to read the older ones in descending chronological order with 5 results per page.

What would be the best way to do this? The fields in the DB are

id (I made sure to enter the oldest ones first)
date (in yyyy-mm-d format)
content

[petesmc]

Here is a quote from someone else's topic that i can't find anymore:

<?
$db = mysql_connect("localhost", "", "");
mysql_select_db("bestof");
//Set this to the number per page
$limit = 10;

//This sets the offset to 0 if it isn't set
if (!isset($offset)) $offset = 0;

//Do a COUNT query to get the total records that we will be working with
$countsql = "SELECT COUNT(*) as totalnum from listings WHERE something = '$something'";
$countresult = mysql_query($countsql);
$countrow = mysql_fetch_array($countresult);
$totalnum = $countrow["totalnum"];

//Do the real query
$realsql = "SELECT * from listings WHERE something = '$something' ORDER BY date DESC LIMIT $offset, $limit";
$result = mysql_query($realsql);
while($row = mysql_fetch_array($result)) {
print $row["Name"]."<br>";
}

//Create the new offset by adding the current offset to the limit
$newoffset = $offset + $limit;

//Check to see if the total number of records is greater than the limit if it is create the next link by creating a new offset which is the current plus the limit and make the link remembering to send the criteria data off to each page in this case $something
if ($totalnum > $newoffset) {
printf('<a href="%s?offset=%s&something=%s">next &gt;&gt;</a><br>', $PHP_SELF, $newoffset, $something);
}

Smae as above except for subtracting the offset to create the back button if the current offset is greater than the limit so it won't be on the first page
if ($offset >= $limit) {
$newoffset = $offset - $limit;
printf('<a href="%s?offset=%s&something=%s">prev &lt;&lt;</a>', $PHP_SELF, $newoffset, $something);
}
?>

Hope this help and thanx to the person that origiunally wrote this..??

[creole]

Thanks Petesmc

[petesmc]

Did it work?

Hope so because i need to use it soon..

LOL

Peter

[freddydoesphp]

Hew sweet Pete that's mine!

[creole]

Freddy...I thought I recognized your code...I have a question about that snippet. I have two pages right now.

One is a "main" page and one is the "old_news" page. Would I use that same piece of code on both pages or would I use on on one page and one on another, or would I even need two pages with that code?

help?

[TWTCommish]

I don't think you'll need more than one page - I'm having trouble reading the code (still learning!), but it looks like there are some Next/Prev links there, so I think it spans multiple pages.

[freddydoesphp]

Sorry I read the post wrong

[freddydoesphp]

This should work you can use it all on the same page and you can change the link text for the prev/next links by just putting whatever you want in there.

<?
$db = mysql_connect("localhost", "", "");
mysql_select_db("bestof");
//Set this to the number per page
$limit = 5;

//This sets the offset to 0 if it isn't set
if (!isset($offset)) $offset = 0;

//Do a COUNT query to get the total records that we will be working with
$countsql = "SELECT COUNT(*) as totalnum from listings WHERE something = '$something'";
$countresult = mysql_query($countsql);
$countrow = mysql_fetch_array($countresult);
$totalnum = $countrow["totalnum"];

//Do the real query
$realsql = "SELECT * from listings WHERE something = '$something' ORDER BY date DESC LIMIT $offset, $limit";
$result = mysql_query($realsql);
while($row = mysql_fetch_array($result)) {
print $row["Name"]."<br>";
}

//Create the new offset by adding the current offset to the limit
$newoffset = $offset + $limit;

//Check to see if the total number of records is greater than the limit if it is create the next link by creating a new offset which is the current plus the limit and make the link remembering to send the criteria data off to each page in this case $something
if ($totalnum > $newoffset) {
printf('<a href="%s?offset=%s&something=%s">next &gt;&gt;</a><br>', $PHP_SELF, $newoffset, $something);
}

Same as above except for subtracting the offset to create the back button if the current offset is greater than the limit so it won't be on the first page
if ($offset >= $limit) {
$newoffset = $offset - $limit;
printf('<a href="%s?offset=%s&something=%s">prev &lt;&lt;</a>', $PHP_SELF, $newoffset, $something);
}
?>

[creole]

Freddy...did you ever know that you're my hero?
You're everything I wish I could beeeeeeeee
I could fly higher than an eagle......
If you were the wind beneath my wings....

ummmmmmm
Did I just wsay that out loud?
enjoy your Karma...again

[freddydoesphp]

Easy now.... if you only really knew me you might say differently!

My cat really helps me a lot

[creole]

Freddy...got a question for you on this script piece.

$countsql = "SELECT COUNT(*) AS totalnum FROM listings WHERE something = '$something'";

Does that have to be like that? What is the something='$something' part?

[freddydoesphp]

That is I don't know how your table is set up so I put that as a sample where clause but since you are grabbing all records, right? You can leave the whole where clause out.

[creole]

ok...that's what I did...I am actually only grabbing 2 of the three fields.

here is my select statement that (currently) gives me what I need.

$result = mysql_query("SELECT DATE_FORMAT(date, '%M %d, %Y') as date_display, content, IF(DATE_SUB(CURDATE(), INTERVAL 3 DAY) < date, 1, 0) AS is_new FROM po_updates ORDER BY date DESC LIMIT $limit;");

The part where I run into trouble is in merging the two select statemtents. The sample one you gave (the real statement) and the one I have now.

[freddydoesphp]

Okay so what you need for the count query is just the total number of records so you would leave everthing off

$result = mysql_query("SELECT count(*) as totalnum FROM po_updates");


Then on the real query

$result = mysql_query("SELECT DATE_FORMAT(date, '%M %d, %Y') as date_display, content, IF(DATE_SUB(CURDATE(), INTERVAL 3 DAY) < date, 1, 0) AS is_new FROM po_updates ORDER BY date DESC LIMIT $offset, $limit;");

[creole]

that worked beautifullly except for one thing. This code:

print "<div align=\"center\">";

//Check to see if the total number of records is greater than the limit
if ($totalnum > $newoffset) {
printf ('<a href="%s?offset=%s" class="mainpage">Older Updates &gt;&gt;&gt;</a>', $PHP_SELF, $newoffset);
}

//Same as above except for subtracting the offset to create the back button
if ($offset >= $limit) {
$newoffset = $offset - $limit;
printf ('<a href="%s?offset=%s" class="mainpage">&lt;&lt;&lt;Newer Updates</a>', $PHP_SELF, $newoffset);
}

print "</div>";

Puts the forward and backwords arrows into this arrangement...

Older Updates >>><<<Newer Updates

I want them like this...

<<<Newer Updates Older Updates >>>

I wil try playing around with it first...please don't post the answer just yet as this should be fairly simple...I tried the obvious of switching the order but that messed up the variables so let me think on it K?

Just as an FYI...the arrows DO work...they just aren't in the right order.

[freddydoesphp]

Swap them out just move the older above the newer, does that make sense?

[creole]

I tried that and it gave me offset of 0 on both arrows...but this did the trick.

//Same as above except for subtracting the offset to create the back button
if ($offset >= $limit) {
$newoffset = $offset - $limit;
printf ('<a href="%s?offset=%s" class="mainpage">&lt;&lt;&lt;Newer Updates</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;', $PHP_SELF, $newoffset);
}

$newoffset = $offset + $limit;

//Check to see if the total number of records is greater than the limit
if ($totalnum > $newoffset) {
printf ('<a href="%s?offset=%s" class="mainpage">Older Updates &gt;&gt;&gt;</a>', $PHP_SELF, $newoffset);
}

I moved that line in bold down below the "newer updates" arrow and it worked...aren't you proud of me?

lol

[freddydoesphp]

I knew you could figure it out so you put the
$newoffset = $offset + $limit;
inside the second if clause?

[creole]

no...it is not in a clause...it is all by it's little lonsome...and it works so I am not complaining...

I really appreciate all your Help by the way Freddy...Thank you.

[freddydoesphp]

Now you gotta make the little

1 2 3 4 5 6 7 8 >> at the bottom of the page instead of just plain old next and prev buttons here is the code

if ($numrows > $limit) {

// calculate number of pages needing links

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



// $pages now contains int of pages needed unless there is a remainder from division

if ($totalnum%$limit) {

// has remainder so add one page

$pages++;

}

if (($offset + $limit) > $numrows) {
$lastnum = $numrows;
}
else {
$lastnum = ($offset + $limit);
}
printf('<tr><td colspan=3 align="center"><span class="listing">Showing records <b>%s - %s</b></span></td></tr>', ($offset + 1), $lastnum);
print '<tr><td colspan=3><table align="center" cellspacing="0" cellpadding="4" border="0"><tr><td><span class="listing">Page </span></td>';

for ($i=1; $i <= $pages; $i++) { // loop thru



$newoffset=$limit*($i-1);

if ($newoffset != $offset) {

printf('<td><a href="%s?offset=%s&show_listings=1&city=%s&type=%s">%s</a></td>%s', $PHP_SELF, $newoffset, $city, $type, $i, "\n");

}

else {

print "<td><span class=offset>". $i ."</span></td>";

}

}
print '</tr></table>';
}

[creole]

I'll keep that for future reference...I only have 31 entries in the updates table right now. I usually add about one every 2-3 days, although sometimes it is every day...