How to write SQL Where clauses, and how to evaluate AND/OR

I wrote sql to meet the following conditions, but it doesn't work.What's wrong?
·Created (DATETIME type) is 2016-02 or 2016-01 (in 2016-01, we have embedded $_GET variable)
·The value of bill is 0 or more than 15 days have passed since modified (datetime type).

select id
from contents
where
    DATE_FORMAT(created, 'y-m') = 2016-02 OR
    DATE_FORMAT(created, 'y-m') = adddate(2016-02, interval-1month)AND
    bill = 0 OR
    now()>(`modified`+INTERVAL15DAY)

php mysql sql

2022-09-29 22:36

2 Answers

In addition to the operator precedence problem, the DATE_FORMAT(created, 'y-m') is incorrectly formatted with the string y-m.I think the intent is DATE_FORMAT(created, '%Y-%m').

Also, the string 2016-02 is not string literal when generating SQL with sprintf or string concatenation.It is interpreted as a numerical expression, so it will be 2014 (in that case, it could be a security issue).If you are using placeholders to embed the parameters, there is no problem.

There may be other points that do not reflect the intentions of the question, but I have not checked everything.

If it doesn't work as intended, don't do anything complicated all of a sudden and check the movement little by little by breaking down the output and conditions of the function through the CLI.

2022-09-29 22:36

AND has a higher priority than OR, so OR results (two places) must be parentheses.

select id
  from contents
 where 
     -- enclose in parentheses
     (DATE_FORMAT(created, 'y-m') = 2016-02 ORDATE_FORMAT(created, 'y-m') = adddate(2016-02, interval-1month))
   AND
     -- enclose in parentheses
     (bill=0 OR now()>(`modified`+INTERVAL15DAY))

2022-09-29 22:36

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656