Daily Tip in PHP/SQL?

[TWTCommish]

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?

[Ace Nova]

if you want Javascript, iFroogy made a nice one for me, its pretty simplistic.

[TWTCommish]

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.

[Ace Nova]

Sorry i was just rambling.

[TWTCommish]

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.

[Fabio Dias]

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:

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:

<?
// 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

[TWTCommish]

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?

[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:

<?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!

[jumpthru]

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:

<?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";

[Fabio Dias]

"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:

$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

[TWTCommish]

Alright, made some changes, here's what I have now:

<?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.

[TWTCommish]

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!

[jumpthru]

Hmm, guess I was wrong, but worked when I tried it...weird