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)?

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 @ypercubeᵀᴹ and I love it!

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