MySQL: JOIN and Conditional Statement

Question

Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol.

I made a query and thought before it was fine but I just realized, just now, the inaccuracy of these. Here are what I've tried:

From a search page, for example we have this variable:

$variable = !empty($_POST["variable"]) ? $_POST['variable'] : "";

QUERY 1

SELECT
 ...
 ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE table2.column LIKE '%$variable%'

RESULT 1

• If $variable has no value, only rows with respective data from table2 are displaying.
• If $variable has a value, rows with respective data from table2 and matching value are displaying.

---

QUERY 2

SELECT
 ...
 ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
 AND table2.column LIKE '%$variable%'

RESULT 2

• If $variable has no value, all rows are displaying.
• If $variable has a value, all rows are still displaying.

---

DESIRED RESULT

• If $variable has no value, all rows will be displayed.
• If $variable has a value, rows with respective data from table2 and matching value will be displayed.

I already tried using IF, CASE and LEFT OUTER JOIN but still no luck. Am I forgetting something with here? Or I'm doing it wrong (which is the truth)?

• Pablo Pazos: The main problem is injecting the variable directly in the query without any checks. This '%$variable%' becomes '%%' which matches anything. In PHP you can construct your query verifying the value first and then adding the condition if the value is not empty. Injecting the variable like that also allows SQL injection, you need to check and escape the value just in case it contains dangerous data that can alter the result of the query. Like what happens if variable is "'", that wiil break the query.

Answer 1

Okay, I've just gave another shot with IF clause and figured out the simplest query that I can think of. It's now giving the desired result, here's my query:

SELECT
 ...
 ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE IF('$variable' != '', table2.column = '$variable', 1 = 1)

Successfully, I placed that IF() clause in the right place. If you guys have thoughts about this code or have a very better one than this, kindly share it :)

---

[ADDITIONAL]

This is an answer from @ypercubeTM and I love it!

SELECT
 ...
 ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE table2.column = '$variable' OR '$variable' = ''

• ypercubeᵀᴹ: try FROM table1 LEFT JOIN table2 ON table1.column_id = table2.column_id WHERE (table2.column = '$variable' OR '$variable' = '')
• rhavendc: @ypercubeTM Awesome sauce! That's simpler from what I currently had! I added it to the answer. Thanks :D