SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm trying to implement a "Daily Tip" sort of deal on my site - all I need to figure out now is how to get the script to cycle through the tips each day and then start back at the beginning after it's gone through them all.

    Unfortunately: I'm totally stuck. Any suggestions?

  2. #2
    SitePoint Zealot Ace Nova's Avatar
    Join Date
    Aug 2000
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you want Javascript, iFroogy made a nice one for me, its pretty simplistic.
    Ace Nova
    Football Man
    InternetSprite.Com - Coming Soon!

  3. #3
    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'm fine with making use of a JavaScript - but I'm talking about pulling data out of an SQL database mainly. I'm not sure what you mean exactly, but I'm open to whatever works using those technologies.

  4. #4
    SitePoint Zealot Ace Nova's Avatar
    Join Date
    Aug 2000
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry i was just rambling.
    Ace Nova
    Football Man
    InternetSprite.Com - Coming Soon!

  5. #5
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just bumping this up a bit - very anxious to get this figured out! C'mon Freddy - I know you're reading this. Let the cat use the keyboard for a bit.

  6. #6
    SitePoint Member
    Join Date
    Aug 2000
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, grab the tip count, using COUNT() on the table. To make sure you can rotate over all the tips, you'll need some sort of day count.

    You can use (time()/(60*60*24)) to get the number of days since 1st January 1970. Take that modulo the number of tips, so you can get a tip 'number' (ranging from 0 to COUNT(*)-1).

    However, you can't use 'WHERE ID=$something', because IDs are not necessarily consecutive (e.g. if you delete a record, its ID becomes free). You'll have to order by some field, like ID, and get the nth record from the list, so your query should look like this:
    Code:
    SELECT Tips.*
    FROM Tips
    ORDER BY Tips.ID, LIMIT ???, 1
    Once you get that tip, you can output it. Here's what your PHP tip code should look like this:
    Code:
    <?
    // Stuff here...
    
    $tipcountquery = mysql_query("SELECT COUNT(Tips.*) AS TipCount " .
                                 "FROM Tips");
    
    $tipcount = mysql_fetch_array($tipcountquery)['TipCount'];
    
    $tipnumber = (time()/(60*60*24)) % $tipcount;
    
    $tipquery = mysql_query("SELECT Tips.* " .
                            "FROM Tips " .
                            "ORDER BY Tips.ID LIMIT $tipnumber, 1");
    
    $tipcontent = mysql_fetch_array($tipquery);
    
    // Stuff here...
    ?>
    Cheers,

    Fabio Dias

  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 have to admit - I'm a bit confused by that! Looking at it, it looks like it should cycle through - I'll give it a try. Can I get rid of the second "60" and the "24" so that it will update every 60 seconds, so that I can test it without waiting 24 hours?

  8. #8
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, I had to modify it to cut down on the error messages I was getting - it looks like I've done that, however now it seems there's a problem with the $tipnumber variable.

    I added one entry to the "tips" database, and I'm using this code:

    Code:
    <?php
    include("../connect.inc");
    $tipquery = "SELECT COUNT(*) AS tipcount FROM tips";
    $tipresult = mysql_query($tipquery,$db);
    if (!$tipresult) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    $tipcount = mysql_num_rows($tipresult);
    $tipnumber = (time()/(60*60*24)) % $tipcount;
    
    $tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT '$tipnumber', 1";
    $tipresult2 = mysql_query($tipquery2,$db);
    if (!$tipresult2) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    while ($tiprow2 = mysql_fetch_array($tipresult2)) {
      $body = $tiprow2["body"];
    }
    echo("$body");
    ?>
    I get this message:

    Error performing query: You have an error in your SQL syntax near ''0', 1' at line 1

    That caused me to examine the $tipnumber line a bit, and it looks confusing - like it might be in the wrong format.

    Any help anyone can provide would be greatly appreciated, whether it's with this code, or new code - whatever works!

  9. #9
    SitePoint Wizard jumpthru's Avatar
    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    1,008
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by TWTCommish
    Hmmm, I had to modify it to cut down on the error messages I was getting - it looks like I've done that, however now it seems there's a problem with the $tipnumber variable.

    I added one entry to the "tips" database, and I'm using this code:

    Code:
    <?php
    include("../connect.inc");
    $tipquery = "SELECT COUNT(*) AS tipcount FROM tips";
    $tipresult = mysql_query($tipquery,$db);
    if (!$tipresult) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    $tipcount = mysql_num_rows($tipresult);
    $tipnumber = (time()/(60*60*24)) % $tipcount;
    
    $tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT '$tipnumber', 1";
    $tipresult2 = mysql_query($tipquery2,$db);
    if (!$tipresult2) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    while ($tiprow2 = mysql_fetch_array($tipresult2)) {
      $body = $tiprow2["body"];
    }
    echo("$body");
    ?>
    I get this message:

    Error performing query: You have an error in your SQL syntax near ''0', 1' at line 1

    That caused me to examine the $tipnumber line a bit, and it looks confusing - like it might be in the wrong format.

    Any help anyone can provide would be greatly appreciated, whether it's with this code, or new code - whatever works!
    $tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT '$tipnumber', 1";

    should be

    $tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT $tipnumber";

  10. #10
    SitePoint Member
    Join Date
    Aug 2000
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "SELECT COUNT(*) AS tipcount FROM tips" already manages to grab the number of tips, so "$tipcount = mysql_num_rows($tipresult);" won't work, because the query returns only a single row, and the function always returns 1.

    Use instead mysql_fetch_array on the query. It'll return an array with the tip count stored in it. Retrieve it using something like:

    Code:
    $tipcountarray = mysql_fetch_array($tipresult);
    $tipcount = $tipcountarray['tipcount'];
    The LIMIT statement will return a single tip, too (the query will return the ($tipcount)th record), so you don't need that "while ($tiprow2 = mysql_fetch_array($tipresult2))" loop.

    BTW, no problem, you can change 60*60*24 -- there's nothing magical about it .

    Cheers,

    Fabio Dias

  11. #11
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, made some changes, here's what I have now:

    Code:
    <?php
    include("../connect.inc");
    $tipquery = "SELECT COUNT(*) AS tipcount FROM tips";
    $tipresult = mysql_query($tipquery,$db);
    if (!$tipresult) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    $tipcountarray = mysql_fetch_array($tipresult);
    $tipcount = $tipcountarray['tipcount'];
    $tipnumber = (time()/(60*60*24)) % $tipcount;
    
    $tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT '$tipnumber', 1";
    $tipresult2 = mysql_query($tipquery2,$db);
    if (!$tipresult2) {
      echo( "<P>Error performing query: " .
            mysql_error() . "</P>" );
      exit();
    }
    $tiparray = mysql_fetch_array($tipresult2);
    $body = $tiparray["body"];
    echo("$body");
    ?>
    I get the same error as before: Error performing query: You have an error in your SQL syntax near ''0', 1' at line 1

    I admit, I'm confused - that SQL query looks fine to me.

  12. #12
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, I got it! I just took off the single quotes - I didn't think those would be a big deal, but I guess they were.

    I'll mess around with it for a bit - thanks!

  13. #13
    SitePoint Wizard jumpthru's Avatar
    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    1,008
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, guess I was wrong, but worked when I tried it...weird


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
  •