MySQL: adding conditions in SELECT

Question

I am having a huge SELECT where I need to make some conditions.

Eg:

SELECT `t`.`title`, `t`.`price_paid`, `t`.`date_creation`, `t`.`date_upload` 
  FROM `table_name` AS `t`
 WHERE `t`.`date_creation` >= "'.$year.'-'.$month.'-01" 
   AND `t`.`date_creation` < "'.$year.'-'.($month+1).'-01"

I declare in PHP the values of $year and $month and I want the rest to be made in MYSQL (I have strong reasons I can detail if needed)

What I need is to make the date_upload empty (null or 0) and price also empty if the date_upload is not from the same interval as t.date_creation

can you dynamically create the SQL statement in PHP and pass a variable in to your query object? — Matt Busche
– Matt Busche, Commented Apr 9, 2013 at 18:02
Let's use parameters for your query with "?" (it's SQL syntax, not PHP). PHP side just add precalculated parameters value with bindValue(). To make them empty you can't use a WHERE but a CASE statement (just THEN NULL if condition is not met) — Adriano Repetti
– Adriano Repetti, Commented Apr 9, 2013 at 18:02
@Matt Busche Sure I can, any ideas? the result of this/of the actual query is an huge array that I use to build an CSV report. I am using some already made libraries for that where I insert the query and the structure — Victor Spinei
– Victor Spinei, Commented Apr 9, 2013 at 18:09
@tadman thanks for the tip still I'm pretty safe as everything is coming within a framework and is not that easy to inject stuff in our data as there are some layers for exactly this type of things :) — Victor Spinei
– Victor Spinei, Commented Apr 9, 2013 at 18:12

Marc B · Accepted Answer · 2013-04-09 18:33:27Z

2

Don't do date math like that. if you're december, you'll be asking for MySQL to look for 2013-13-01 on the high end. Use actual mysql date math:

SELECT ...
WHERE t_date_creation BETWEEN (now() - INTERVAL 1 MONTH) AND (now() + INTERVAL 1 MONTH)

AS for your query, it'd be an UPDATE query:

UPDATE yourtable
SET date_upload = NULL, price = NULL
WHERE date_restriction_logic_here.

answered Apr 9, 2013 at 18:33

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Spinei Over a year ago

good point with the date. I will use SQL there still I need to cover the full last month from 1 to the end of it.

Victor Spinei Over a year ago

The Update is not working in my case. I do the query and I print the result in a CSV report file so no saving in db so far. Other suggestion?

Victor Spinei Over a year ago

seems like IF(condition, value if true, value if false) is the closest to waht I need, i will add the solution bellow

Victor Spinei · Accepted Answer · 2013-04-10 15:44:16Z

The solutions so far seems to be:

-> MySQL Nested "If" in Select Queries

IF(condition, value if true, value if false)

Something like

SELECT t.title, if (t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_upload < "'.$year.'-'.($month+1).'-01" ,t.price_paid, '0') as price_paid, t.date_creation, if (t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_upload < "'.$year.'-'.($month+1).'-01" ,t.date_upload, '') as date_upload FROM table_name AS t WHERE t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_creation < "'.$year.'-'.($month+1).'-01"

and other solutions seems to be Control Flow Functions

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

Found on http://komlenic.com/254/mysql-nested-if-in-select-queries/ http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Collectives™ on Stack Overflow

MySQL: adding conditions in SELECT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related