Where clause when variable is false doesn't consider that into the SQL query?

Question

I have something that I don't quite understand, I have the solution, but I don't know why and it's driving me a bit crazy

Controller

DB::connection()->enableQueryLog();

$transfer = Transfer::where('ticket_id', $last_ticket)->where('event_id', 36)->where('buyer', $user_email)->first();

$queries = DB::getQueryLog();

dd($queries);

Result

array:1 [
  0 => array:3 [
    "query" => "select * from `transfers` where `ticket_id` = ? and `event_id` = ? and `buyer` = ? limit 1"
    "bindings" => array:3 [
      0 => false
      1 => 36
      2 => "[email protected]"
    ]
    "time" => 0.36
  ]
]

Situation: $last_ticket in the DB is unique and never null, empty or false.

In my example $last_ticket is false, but in the DB there's no any false, why do I still get results?

Is it because whenever one where condition is false, it doesn't take that into the equation?

So the question is: How can I do to receive the correct results meaning: if $last_ticket is false then no results, and if it has some data, then return the row result restricted by $last_ticket

One option could be to put if($last_ticket == false){$last_ticket='randomInexistentString_ag4dh&2g32y4t'} so it's not null or zero but I don't think it's the prettier way to go

Answer 1

A zero in MySQL always matches any string that doesn't start with a digit - this is because it wants both sides of the comparison to be of the same type, so it converts the string to a number, and any string that doesn't start with a number evaluates to 0 automatically.

Now, you're not feeding it a 0 - but you're feeding it a false, and in MySQL, that's not a true boolean but actually a 0.

Without knowing exactly what your database looks like, I'd say this is the most likely explanation.

There are ways around this, for instance you could use the ->when() chaining on Eloquent:

Transfer::where('event_id', 36)
        ->where('buyer', $user_email)
        ->when($last_ticket, function ($query) use ($last_ticket) {
            $query->where('ticket_id', $last_ticket);
        })
        ->first();

(Taken from memory, syntax may be a little bit off. Thanks to Rick James for correction in comments.)

Answer 2

Redesign the UI. If something should be left out of the query, then do not include it in the query.

That is, build the WHERE clause dynamically so that, when "false" is supplied, it becomes

WHERE `event_id` = ? and `buyer` = ?

I don't know about Laravel, but in PHP, I would build an array of things to AND together, then construct the WHERE:

$ands = array();
if (...)  $ands[] = "ticket_id = ...";
if (...)  $ands[] = "event_id = ...";
if (...)  $ands[] = "buyer = ...";

if (empty($ands))
    $where = '';
else
    $where = 'WHERE ' . implode(' AND ', $ands);

$sql = "SELECT ... $where ...";

Note that it easily handles any or all or none of the items being omitted.