simple mySQLi question

Maryanne

In my PHP code, I do a simple mySQL query and I notice both of these work:

$conn->query($statement);

-and-

mysqli_query($conn,$statement);

Which is better/more efficient?

When I did a speed test benchmark, #2 came out faster:

1.)
$statement = "insert into table ('ordernumber') values ('555')";
$conn->query($statement);

2.)
$statement = "insert into table ('ordernumber') values ('555')";
mysqli_query($conn,$statement);

Does it matter which one I use? If not, I'll go with the faster one.

NogDog

Unless there is a meaningful performance difference (i.e. who cares about a couple microseconds here or there? 🙂 ), I'd go with whichever one matches the "style" of the way you're coding in general. The $object->method() notation is what you'd use if you are generally using object-oriented PHP, while the mysqli_function_name() notation is procedural in nature. I generally use the PDO extension these days for any PHP database stuff, and it's all object-oriented, so if for some reason I had to use MySQLi, my personal preference would be to stick with object-oriented style for it, too.

Maryanne

haha -- I laughed out loud when you said "who cares about a couple microseconds" because the difference in speed was literally 1/10,000 of a second!

By the way, is there a SIMPLE "one-liner" of code to look up one record? For example, to look up a person's name from an order number, I used to use:

$query = mysql_query("select customer from table where ordernumber = '122345'");
$answer = mysql_result($query,0,0);
print $answer; //Voila!

But now, with mysqli, it looks like I always have to "loop" through results (even if there's only one result) like this:

if ($result = mysqli_query($conn,"select customer from table where ordernumber = '122345'")) {
while ($row = mysqli_fetch_assoc($result)) {
print $row["customer"]); // loops seem a waste.... bring back old mySQL
}
mysqli_free_result($result);
}

Am I stuck with using loops with mysqli?

Thank you!!

Weedpacket

No. Why use a loop if you know you're getting exactly one record?

NogDog

Since mysqli::query() returns a mysqli_result object, you could do a one-liner like this, though whether that's a good thing or a bad thing I'm not sure (very subjective):

$row = $conn->query("select customer from table where ordernumber = '122345'")->fetch_assoc();
print $row['customer'];

Weedpacket

list($customer) = $conn->query('Select customer From table Where ordernumber=12345')->fetch_row();

$customer = $conn->query('Select customer From table Where ordernumber=12345')->fetch_assoc()['customer'];

NogDog

Weedpacket;11058903 wrote:

...

$customer = $conn->query('Select customer From table Where ordernumber=12345')->fetch_assoc()['customer'];

I wasn't sure if PHP (recent-enough-version) would allow that or not, and was too lazy to test it. 🙂

Weedpacket

NogDog;11058913 wrote:
I wasn't sure if PHP (recent-enough-version) would allow that or not, and was too lazy to test it. 🙂

As of v7.1 (released last Thursday) that first alternative can be slightly written as

 [$customer] = $conn->query('Select customer From table Where ordernumber=12345')->fetch_row();

Maryanne

Wow! I just wanted to thank you guys! As a "lurker" I've seen SO MANY people ask questions, and when they get their answer, they just vanish. Like bigfoot..... where did he go? Instead, I stuck around, and am now thanking you both. I love those one liners. I'm going to check what version I'm using, if it's 7.1 I'll use the last one, otherwise the others are great. The "list" one has me intrigued. I'm going to a.) Thank you for now, and b.) go research what/how the "list" function works. Thanks again!!

Weedpacket

You're welcome.

One thing to keep in mind with the one-liner approaches is that they all assume that the query runs perfectly every time: they'll break if, for example, there is no customer with order number 12345.