SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Wizard creole's Avatar
    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)
    content
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    <? echo "Kick me"; ?> petesmc's Avatar
    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 &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..??

  3. #3
    SitePoint Wizard creole's Avatar
    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

  4. #4
    <? echo "Kick me"; ?> petesmc's Avatar
    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

  5. #5
    Dumb PHP codin' cat
    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.

  6. #6
    SitePoint Wizard creole's Avatar
    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

  7. #7
    SitePoint Wizard TWTCommish's Avatar
    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.

  8. #8
    Dumb PHP codin' cat
    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.

  9. #9
    Dumb PHP codin' cat
    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 &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);
    }
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Wizard creole's Avatar
    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...again
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  11. #11
    Dumb PHP codin' cat
    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 lot
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Wizard creole's Avatar
    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

  13. #13
    Dumb PHP codin' cat
    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.

  14. #14
    SitePoint Wizard creole's Avatar
    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

  15. #15
    Dumb PHP codin' cat
    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.

  16. #16
    SitePoint Wizard creole's Avatar
    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 &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.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  17. #17
    Dumb PHP codin' cat
    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.

  18. #18
    SitePoint Wizard creole's Avatar
    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">&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
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  19. #19
    Dumb PHP codin' cat
    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.

  20. #20
    SitePoint Wizard creole's Avatar
    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

  21. #21
    Dumb PHP codin' cat
    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.

  22. #22
    SitePoint Wizard creole's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •