如何使用php和mysql按顺序按id排序 [英] how to make quote of the day using php and mysql in order by id

问题描述

我试图连接MySQL数据库和获取数据
我需要它通过ID（每日）
的顺序获得（数据），并分离两个数据和调用它们由两个不同的变量
数据库看起来像这样：

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)

我不是像上面这样:)它只是为了澄清....和什么是最好的整理和类型的巨大的数据在UTF-8？

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 ?

编辑：

您可以返回5行，而不是1行吗？按ID？例如第一天（1-2-3-4-5）ID和第二天（6-7-8-9-10）等等。

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?

推荐答案

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

然后您选择它：

   $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;

编辑!!：我已删除datediff，因此从日期差异返回的ID是DIRECTLY在WHERE。

这是计算第一天和当前日期之间的差异。所以每天datesiff会大1。
DATEDIFF（CURDATE（）+ 1，'$ firstday'）as datediff 可以解释为

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)

• 昨天是2011-07-06，因此 datediff = 2011-07-07（有+1！） - 2011-07-06 这是 1


• 今天 >
strong>

• 在一个月内将 33


• 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

因此， datediff每天增加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..

编辑＃2：每天5个报价

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

第二次编辑感谢ajreal（ SQL LIMIT语法错误）

second edit thanks to ajreal (SQL LIMIT syntax error)

＃3：5报价一天，可通过变量更改..

选项1：

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

选项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

选项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;

这篇关于如何使用php和mysql按顺序按id排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！