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

Can you please provide with the "transfers" table schema, and specific with "ticket_id" column datatype and constraints? — llouk
– llouk, Commented Feb 3, 2018 at 23:36
What's the ticket_id field's value in the returned resultset? (My guess that it is 0). — Shadow
– Shadow, Commented Feb 3, 2018 at 23:39
I'm interested in the returned value, not the parameter you supplied. That would tell us what's going on. — Shadow
– Shadow, Commented Feb 3, 2018 at 23:49
ah sorry, the returned value you mean is also false as per debug result in the post.. or are you asking another value? — TrOnNe
– TrOnNe, Commented Feb 3, 2018 at 23:50

Joel Hinz · Accepted Answer · 2018-02-13 22:43:14Z

13

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

edited Feb 13, 2018 at 22:43

answered Feb 3, 2018 at 23:40

Joel Hinz

25.4k8 gold badges65 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

TrOnNe Over a year ago

Oh, thanks, I didn't know about the 0, and if I feed it with null?

Joel Hinz Over a year ago

I think it's the same thing but I'm honestly not sure by heart.

TrOnNe Over a year ago

Extra points ;) I added a question to the same topic, how to do to return none if last_ticket is zero or null

Joel Hinz Over a year ago

Your extra question is a little odd - it's a pure if condition, so why don't you just do if ($last_ticket) { do things } else { don't do things }?

Rick James Over a year ago

@JoelHinz - No. A zero matches any string that does not start with digits. In converting the string to a number, it consumes leading digits if they are there.

|

Rick James · Accepted Answer · 2018-02-13 02:17:26Z

2

+25

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.

answered Feb 13, 2018 at 2:17

Rick James

144k15 gold badges144 silver badges254 bronze badges

1 Comment

Paul Spiegel Over a year ago

In laravels query builder you can pass an array of contidions: ->where(['ticket_id' => 7, 'event_id' => 5]). See first comment here. You should even be able to pass an empty array, so you won't need to check if (empty($ands)).

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related