Perfect mySQL one liner?

[ChenXiu]

To internally retrieve only one record from a primary key table (never more than one record), is/are these the
best (fastest) queries that will never generate a PHP error?

$var = $db->query("select price from table where size = 'small'")->fetch_object()->price ?? '';
echo $var;

-or-

$var = $db->query("select price from table where size = 'small'")->fetch_assoc()["price"] ?? '';
echo $var;

Thank you.

[Barand]

If you get back an empty value for the price, how will you know whether it's because the record didn't exist or because there was no price in the record?

Most times, when you need a query like that, the value ('small' in this case) will be from the user via GET or POST. Then you need a prepared query, which is going to upset your applecart.

[ChenXiu]

A prepared query would definitely upset my apple cart 😃
Fortunately these queries are internal queries generated by my own code (more sanitized than a hospital -- Edward Snowden couldn't penetrate).

5 hours ago, Barand said:

how will you know whether...

That is what I am having trouble with. Do I have to "error trap" every step of the way? I have variations of this code all over my site, I wish I could have one bulletproof formula for all these similar internal queries. Is this better:

$begin = $db->query("select price from table where size = 'small'")->fetch_assoc();
$result = $begin ? $begin["price"] : 'no result';
echo $result;

 

[requinix]

If your query is hard-coded without any variables then it should be trivial to verify the syntax. If you go with prepared statements then you can easily verify their syntax too.

When you do that, the "only" reason any of these queries would create errors would be with larger problems: server is down, database doesn't exist, table has the wrong columns, etc.

[Barand]

10 hours ago, ChenXiu said:

Do I have to "error trap" every step of the way?

Neither of those conditions I mentioned are "errors" that would generate an error message. However, how you deal with them could be different.

[ChenXiu]

Thank you. At first I had this:
$var = $db->query("select price from table where size = 'small'")->fetch_object()->price ?? '';
But a PHP error would be generated if the "price" value was missing from the mySQL table.
But when I do this:
$begin = $db->query("select price from table where size = 'small'")->fetch_assoc();
$result = $begin ? $begin["price"] : 'no result';
I then only get errors if something is really wrong, like a missing table.
So unless there is anything wrong with that, I think I'll go with that.

I really like bare bones code. It is almost impossible to find one-liners for anything. All the mySQL tutorials have while loops.

Thank you.

[Barand]

1 hour ago, ChenXiu said:

But a PHP error would be generated if the "price" value was missing from the mySQL table.

Not necessarily...

DATA

    +-------+-------+
    | fruit | price |
    +-------+-------+
    | grape | 5.00  |
    | melon | NULL  |
    +-------+-------+

Code (PDO)

$var = $db->query("select price from fruit where fruit = 'grape'")->fetch()['price'] ?? '';
echo '<br>Grape: ' . $var;

$var = $db->query("select price from fruit where fruit = 'melon'")->fetch()['price'] ?? '';
echo '<br>Melon: ' . $var;

$var = $db->query("select price from fruit where fruit = 'lemon'")->fetch()['price'] ?? '';
echo '<br>Lemon: ' . $var;

Output (No exceptions were thrown during the running of this code)

Grape: 5.00
Melon:            (why no price?)
Lemon:           (why no price?)

Edited August 29, 2021 by Barand