SitePoint Sponsor |
|
User Tag List
Results 1 to 22 of 22
Thread: x number of results per page
-
Jan 15, 2001, 16:19 #1
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
contentAdobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 15, 2001, 16:41 #2
- Join Date
- Nov 2000
- Location
- Hong Kong
- Posts
- 1,508
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 >></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 <<</a>', $PHP_SELF, $newoffset, $something);
}
?>
-
Jan 15, 2001, 16:55 #3
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Petesmc
Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 15, 2001, 16:58 #4
- Join Date
- Nov 2000
- Location
- Hong Kong
- Posts
- 1,508
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Did it work?
Hope so because i need to use it soon..
LOL
Peter
-
Jan 15, 2001, 17:07 #5
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hew sweet Pete that's mine!
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 13:30 #6
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 14:03 #7
- Join Date
- Aug 1999
- Location
- Pittsburgh, PA, USA
- Posts
- 3,910
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 16, 2001, 14:35 #8
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sorry I read the post wrong
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 14:38 #9
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 >></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 <<</a>', $PHP_SELF, $newoffset, $something);
}
?>
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 14:46 #10
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...againAdobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 14:49 #11
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Easy now.... if you only really knew me you might say differently!
My cat really helps me a lotPlease don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 21:23 #12
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 21:28 #13
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 21:32 #14
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 21:39 #15
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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;");
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 22:02 #16
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 >>></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"><<<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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 22:07 #17
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Swap them out just move the older above the newer, does that make sense?
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 22:13 #18
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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"><<<Newer Updates</a> ', $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 >>></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?
lolAdobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 22:18 #19
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I knew you could figure it out so you put the
$newoffset = $offset + $limit;
inside the second if clause?Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 22:22 #20
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Jan 16, 2001, 22:30 #21
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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>';
}
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Jan 16, 2001, 22:43 #22
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
Bookmarks