Http://www.oso.com.cn/forum/read.PHP? Theme_id=7459

Main argument is to be centered in SELECT Something FROM Table
On WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 5; , I experimented a long time, the result still makes mistake, saw the function of MySQL today, came out eventually result, dare not enjoy alone, stick come out to study for everybody, (although technical content is not high, do not throw my persimmon to go, breathe out ah)

The TO_DAYS(DATE) function of MYSQL is such specifications:
The total day that travels a DATE to the Christian era 0 years is counted, I checkedMysql>select To_days(now(0));
--------------------------
| TO_DAYS(NOW() ) |
--------------------------
| 730839 |
--------------------------

Those who come out is the total day number that present time is apart from the Christian era 0 years, then I try to check with the statement above;

Mysql>select TO_DAYS(NOW()) - TO_DAYS(date_col) <= 5;
Occurrence result:
ERROR 1054:UNknown Column 'date_col' In 'field First'

Blind alley, I try to go to 5 generation in Date_col directly

Mysql>select To_days(now()) - To_days(5);
Occurrence result:
---------------------------
| To_days(now()) - To_days(5) |
---------------------------
| NULL |
---------------------------

Ah? Won't? Be also no good so?
I try a command thenMysql>select. . . .

Think of suddenly suddenly, hey, what To_days(now()) comes out is integral, I followed integral operation to go directly, why again Where is To_days(date) ? Experiment immediately

Mysql>select To_days(now()) - 5;
--------------------------
| To_days(now()) -5 |
--------------------------
| 730834 |
--------------------------

OK, banzai, the result that got I want eventually, ah ah below it is namely in PHP code inquired with SELECT

The habit that I retain database all along is DATEANDTIME assign directly with NOW() , when showing need not the format is changed, take directly can use,

The partial structure of my library is below
CREATE TABLE Infomess (Infoid Int(11) NOT NULL Auto_increment, Topic Varchar(255) NOT NULL,
……Email Varchar(50) , Dateandtime Datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (infoid)
) ;

The DATEANDTIME here is date format of the standard, next I should inquire the record inside 5 days, SQL inquiry statement is below
$sql= "select * From Infomess Where To_days(dateandtime) >= (to_days(now()) - 5) Order By Infoid Desc Limit $offset, $psize" ;

Be about a Where To_days(dateandtime) >= (to_days(now()) - 5) is enough from the back is another, here 5 can set for a variable
Previous12 Next