how to make quote of the day using php and mysql i

I'm trying to connect MySQL database and getting out datas I need it to get only (Data) in order by ID(daily) and separate both datas and calling them by two different variables the database looks something like this:

------------------------------
| ID |   Data  |  Data2
------------------------------
| 1  |  tea    |   hot
| 2  |  milk   |   hot
| 3  | pepsi   |  cold

and output will be only one line (one data)

I don't make it as above :) it's just for clarifying....and what's best collation and type for huge data in UTF-8 ?

Edit:
Can you make it return 5 rows instead of 1? by ID? for e.g. for first day (1-2-3-4-5)ID's and for second day (6-7-8-9-10) and so on?

回答1:

    quotes
    ----------------------------------
    | id | data        | data2
    ----------------------------------
    | 1  | first quote | translated quote
    | 2  | second...   | bla bla

And then you select it like:

   $firstday="2011-06-06";
    $getquote = mysql_query("SELECT * FROM quotes WHERE id=(DATEDIFF(CURDATE()+1, '$firstday'))");
$quote = mysql_fetch_object($getquote);
echo $quote->data . $quote->data2;

EDIT!!: I have eliminated datediff, so the ID returned from date difference is DIRECTLY in WHERE.

What this does is calculate difference between first day and current date. So each day that datediff will be 1 bigger. DATEDIFF(CURDATE()+1, '$firstday') as datediff can be interpreted as

datediff = differenceBetween(Currentday +1 and firstDay)

Yesterday was 2011-07-06, therefore datediff = 2011-07-07 (there is +1!) - 2011-07-06 which is 1
today, it's 2011-07-08 - 2011-07-06 which is 2
tomorrow 2011-07-09 - 2011-07-06 which is 3
day after tomorrow 2011-07-10 - 2011-07-06 which is 4
in one month it will be 2011-08-08 - 2011-07-06 which is 33

so, datediff is each day bigger by 1

quotes
-------------------------
|id| data
-------------------------
|1| quote          day 1 (because date difference from start == 1)
|2| quote 2        day 2 (datediff == 2)
|3| quote 3        day 3 (datediff == 3)
|4| quote 4        day 4
.....

Or shortly: Each day will be a different quote, starting with ID 1 forward.

I can't explain more then this..

EDIT #2: 5 quotes a day

$offset = date_diff(new DateTime('now'), new DateTime('2011-08-29'))->format('%d');
$getquote = "SELECT * FROM quotes LIMIT {$offset},5";

second edit thanks to ajreal (SQL LIMIT syntax error)

EDIT #3: 5 quotes a day, changeable by variable..

option 1:

$choose=0; //statically defined, only first of that day will pop out

option 2:

$choose = mysql_real_escape_string($_GET["qid"]); //which one will be defined in url.. (watch out, people can figure it out and browse through all quotes

option 3:

$choose = rand(0,4); //will choose it randomly from those 5 daily quotes

So pick one of those options you like, and add it before this:

$offset = 5*date_diff(new DateTime('now'), new DateTime('2011-08-29'))->format('%d') + $choose;
$getquote = mysql_query("SELECT * FROM quotes WHERE id = '$offset'");
$quote = mysql_fetch_object($getquote);
echo $quote->data . $quote->data2;

回答2:

If you want to have a fixed quote through all the day just have the first client of the day do a RAND() on the table and choose a random quote.

Then mark that quote as the quote for the current day and serve it for all other users.

Repeat.