1

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?

Omer
  • 17
  • 1
  • 7
  • What is your question? You can use `TEXT` for your column type and the `utf8_unicode_ci` collation. What else do you want to know? – Michael Mior Jul 06 '11 at 18:25
  • I am so bad on asking a question sorry..I save a lots of data(7000-10000 lines)that's why I've chosen MySQL. – Omer Jul 06 '11 at 18:44

2 Answers2

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;
Community
  • 1
  • 1
Bojan Kogoj
  • 5,321
  • 3
  • 35
  • 57
  • Hi, it's me again :( There was a few errors...I fixed some problems but there's another error: 'mysql_fetch_object(): supplied argument is not a valid MySQL result resource' I've echoed it and here's the source of the error:'Query failed: Unknown column 'datediff' in 'where clause' Actual query:' – Omer Jul 09 '11 at 00:50
  • @Omer edited, i hope it works. I had to get a bit help on this one – Bojan Kogoj Aug 31 '11 at 20:09
  • thank you so much Bojan K. but can I separate each of the quotes? – Omer Sep 01 '11 at 06:12
  • @Omer, how would you like to separate them? Each of them on their own page (with id in url) or in a list one after another? If you know any php&mysql you can do that yourself.. – Bojan Kogoj Sep 01 '11 at 08:29
  • by php variable if possible. point me the to the right direction – Omer Sep 01 '11 at 09:22
  • I see. but can you explain a little bit more, what I mean is how I can separate each of them. sorry but I don't have enough skill to do that. :( – Omer Sep 01 '11 at 18:49
  • @Omer (forgot 5*, added) Alright, with the #3 edit, you have 5 quotes each day. You have `$offset`, which is `date_difference*5 + $choose`. That `$choose` is to move through them, 0 is the first one, 4 is the last one. If it's more than 4 you will access next days quote. You can set that `$choose` from `$_GET` or some other way, you can also generate `$choose` randomly – Bojan Kogoj Sep 01 '11 at 19:13
  • I'm still can't get this to work I tried a lot.can you write an example of one id? does the mysql_fetch_object remain as before?? because every time it return errors – Omer Sep 01 '11 at 20:11
  • mysql_fetch_object(): supplied argument is not a valid MySQL result resource – Omer Sep 01 '11 at 20:46
  • @Omer, i forgot mysql_query... fixed – Bojan Kogoj Sep 01 '11 at 21:14
  • if you show me an example with the $choose that would be great. but what will happen to echo $quote->data . $quote->data2; ? – Omer Sep 01 '11 at 21:24
  • @Omer i really really hope this works coz I'm sort of sick of it :D – Bojan Kogoj Sep 01 '11 at 21:44
  • I feel that way too, sorry :) – Omer Sep 02 '11 at 09:12
0

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.

Frankie
  • 24,627
  • 10
  • 79
  • 121
  • but I need it in order by id not random! – Omer Jul 06 '11 at 18:29
  • @Omer sorry but the way you asked your question... makes it hard to understand what you're aiming for. You want to feed the user a quote for the day, correct? Assuming you'll have more than 30 quotes... you'll have to select them randomly or sequentially. If you want to select the quotes in a sequential fashion just have the first client of the day move the quote to the new one. – Frankie Jul 06 '11 at 18:32
  • My aim is for a daily sayings and and translated sayings(data and Data2) and to show it one by one(daily) I am showing both of sayings and translated sayings. but in order(by table ID) sorry for my bad questioning – Omer Jul 06 '11 at 18:38