SQL and conditional logic on Where

Question

I have a very simple SQL search query as follows.

$sql = 
"
SELECT *
FROM suppliers
WHERE                       
product_categories LIKE '%$product_categories1%'                        
AND agentid=?????
";

In the part of the query where agentid=??? I need to get the following conditionality to work.

If some $var is (say) 1 then agentid !=' ' If same $var is (say) 2 then agentid =' '

The purpose of this query is . .. if !='' the query returns ALL rows where there is an agentid value (!='') or SQL returns no rows at all if agentid is set to (=' ').

My problem is how to write the SQL to introduce this simple condition?

PS I am aware of the <> sql alternative.

use php to assign a value to an `$agent_id` variable before the query, then use the variable in the query. That way your `$agent_id` variable will already have the expected value according to the condition. — coderodour, May 09 '17 at 18:44
But can `$var` either be 1 OR 2? Or there can be other values? — Alon Eitan, May 09 '17 at 18:45
so tell me how to assign a value of !=. Obviously one can use a conditional php statement to set a var and then assign it to agentid. That wasnt the question — WChris, May 09 '17 at 18:46
Alon ... $var is arbitrary. It simply used as a way of switching agentid='' or agentid!='' The values could be anything, True false etc — WChris, May 09 '17 at 18:49
@WChris Then you have an answer that look fine, but I strongly suggest to make a safe queries using PDO - Read this https://en.wikipedia.org/wiki/SQL_injection and then this http://php.net/manual/en/pdo.prepare.php — Alon Eitan, May 09 '17 at 18:51

Wrinn · Accepted Answer · 2017-05-09T18:58:19.533

1

If I understand your question correctly, you want to build your SQL query conditionally. You can use control statements to change what your output SQL is:

// ALWAYS ALWAYS ALWAYS sanitize your input.  Use prepared statements to
// build safe queries (see first comment under this answer).  This example
// of using msqli_escape_string is NOT considered "secure", but is used in
// this context to highlight the importance of sanitization.
$product_categories1 = msqli_escape_string($product_categories1);

if ($var == 'condition') {
    $condition = "agentid IS NOT NULL";
}
else {
    $condition = "agentid IS NULL";
}

$sql = "SELECT * FROM suppliers
        WHERE product_categories LIKE '%$product_categories1%'                        
        AND $condition";

edited May 09 '17 at 18:58

answered May 09 '17 at 18:52

Wrinn

98
1
13

Brilliant. The bit I was unware of is that you could do this------> AND $condition – WChris May 09 '17 at 18:54
@AlonEitan I'm aware, hence the comment about prepared statements. But I'll ammend the answer to put more emphasis on using prepared statements instead. – Wrinn May 09 '17 at 18:55
upvoted for that little bit of additional inspiration, Thank you – WChris May 09 '17 at 18:56
1

@Wrinn Cool. When the code is safe (and not only works) then i'll happily upvote, because I don't want the OP to leave SO with bad practice, they may one day use it wrong and then they can get their website hacked. – Alon Eitan May 09 '17 at 18:58

Filipe Martins · Answer 2 · 2017-05-09T18:50:22.503

0

You can try like this:

if ($var == 1)
    $where = "agentid != ''";
else
    $where = "agentid like ''";

$sql = 
" SELECT *
    FROM suppliers
  WHERE                       
    product_categories LIKE '%$product_categories1%'                        
  AND $where ";

edited May 09 '17 at 18:50

answered May 09 '17 at 18:45

Filipe Martins

608
10
23

1

Although your answer seem correct (based on what the OP wrote), I wouldn't upvote it because of the sql injection danger with this code. Sorry – Alon Eitan May 09 '17 at 18:47
And `agentid == ''` is not a valid sql syntax – u_mulder May 09 '17 at 18:48
Wow does that work in mysql Let me try it Filipe. Vote up if it works – WChris May 09 '17 at 18:51
`LIKE` should be used with patterns. Use `=` to compare with a literal string. – Barmar May 09 '17 at 19:12

SQL and conditional logic on Where

2 Answers2