Mysql – How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true

MySQLmysql-5.5syntax

How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have empty content, it displays error

1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

(select my_date from ( select adddate('2015-02-01', numlist.id) as `my_date`, 
                 weekday(adddate('2015-02-01', numlist.id)) as day_no, 
                 dayname(adddate('2015-02-01', numlist.id)) as `day_name` from 
                 (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM 
                  num n1 cross join num as n10 cross join num as n100) as numlist 
                 where adddate('2015-02-01', numlist.id) <= '2015-02-28' and 
                 weekday(adddate('2015-02-01', numlist.id)) in( $mon, $tue, $wed, $thu, $fri, $sat) ) A)

Legend:
If selected on the php form..(which is not included but i think not important for now, as it is just passing of variable) the following should contain the value.

$mon=1

$tue=2

$wed=3

$thu=4

and so on.. until sunday.

What I need is when the variable doesn't contain any value, it should not appear in the syntax. I don't know how to insert if syntax inside mysql, (or is what am i thinking possible? it does not change its color so I think its not, or am i missing any syntax, i have no luck with google right now.. :D)

Best Answer

You must do some work in PHP. You have a set of numbers: find the non empty ones, build a string, and put the string into the IN() clause. Sample code:

$a = array();
foreach (array($mon, $tues, ...) as $val)
{
    if (! empty($val)) $a[] = $val;   // add the useful values to the array
}
$str = implode(',', $a);       // turn the array into a comma-list
$sql = "SELECT ... IN($str)";  // interpolate into the query

What I failed to do in that code snippet is to validate the input -- a hacker could destroy your system via "SQL Injection":

if (! empty($val)) $a[] = "'" . addslashes($val) . "'";
Related Question