SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: Daily Tip in PHP/SQL?
-
Nov 21, 2000, 22:10 #1
- 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?
-
Nov 21, 2000, 22:45 #2
if you want Javascript, iFroogy made a nice one for me, its pretty simplistic.
-
Nov 21, 2000, 22:58 #3
- 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.
-
Nov 21, 2000, 23:02 #4
Sorry i was just rambling.
-
Nov 22, 2000, 21:05 #5
- 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.
-
Nov 22, 2000, 22:12 #6
- 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
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... ?>
Fabio Dias
-
Nov 22, 2000, 23:09 #7
- 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?
-
Nov 23, 2000, 00:04 #8
- 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"); ?>
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!
-
Nov 23, 2000, 02:28 #9
- 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"); ?>
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!
should be
$tipquery2 = "SELECT * FROM tips ORDER BY tipid LIMIT $tipnumber";
-
Nov 23, 2000, 09:15 #10
- 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'];
BTW, no problem, you can change 60*60*24 -- there's nothing magical about it.
Cheers,
Fabio Dias
-
Nov 23, 2000, 10:34 #11
- 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 admit, I'm confused - that SQL query looks fine to me.
-
Nov 23, 2000, 10:35 #12
- 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!
-
Nov 24, 2000, 00:17 #13
- 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