current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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)

share|improve this question

1 Answer 1

up vote 0 down vote

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) . "'";
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.