Mysql restrictive

0
votes

I have 2 variables in PHP (let's call them 'var1' and 'var2') and a sql command like this:

$sql = "SELECT `id`, `name` FROM `table` WHERE `var` = '{$var1}'"
if ($var2) {
  $sql.= "AND `var`={$var2}"; }

Variable var1 it is always set, but var2 not. I want when var 2 is set to apply WHERE clause only for var2. This means that var2 is more restrictive than var1. How can i do this in SQL language.

2
phpmysqlsql
Not sure I understand, your code looks correct. Are you wanting to change to WHERE ``var`` = '{$var2}'? – Jay Blanchard
Be aware of SQL injection! – MrTux
He has not shown any user input to cause concern for SQL injection @MrTux – Jay Blanchard
It's bad practice to not use prepared statements or escape contents you feed into a SQL query. And based on the context you don't know that it's not input from any untrusted source. @JayBlanchard – MrTux
True, but I also haven't posted the "don't use mysql_" functions warning either because we haven't enough to go on here. We don't know that $var1 and $var2 aren't the result of escaping things @MrTux – Jay Blanchard

2 Answers

2
votes

If I understand you correctly you want to do this - 

$sql = "SELECT `id`, `name` FROM `table` ";
if($var2) {
    $sql .= "WHERE `var` = '{$var2}'";
} else {
    $sql .= "WHERE `var` = '{$var1}'";
}

If you want to do this just using SQL you would use something like this - 

SELECT `id`, `name`, `variable`
    CASE variable
        WHEN 'var1' = variable THEN a = var1
        WHEN 'var2' = variable THEN a = var2
    END
FROM `table`
WHERE `foo` = a

Without knowing more of your code or having sample data to work with it is hard to develop a demo, but this should get you started.

1
votes
if ($var2) {
    $sql = "SELECT `id`, `name` FROM `table` WHERE `var` = '{$var2}'";

} else {
    $sql = "SELECT `id`, `name` FROM `table` WHERE `var` = '{$var1}'";
}