Fetching one row only with MySQLi

Viewed 73 times

How can I only fetch one INDEXED row with MySQLi? I'm currently doing this:

$row = $result->fetch(MYSQLI_ASSOC);
$row = $row[0];

Is there another way?

I'm aware of mysqli_fetch_row but it doesn't return an associative array.

Answers

Use $row = $result->fetch_assoc(); - it's the same as fetch_row() but returns an associative array.

Tuesday, August 30, 2022

fred.ide-312d07149dae

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Sunday, November 6, 2022

shuhamluvuns-e7688432bcec

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Wednesday, August 24, 2022

fred_read

using mysqli you do it the following way (assuming the mysqli object is already created -- you can also use the procedure methods, just slightly different):

$sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla 
        FROM account ORDER BY pid ASC". $eb["array"]['querylimit'];
$result = $mysqli->query($sql);
$TotalRcount = $result->num_rows;
while($row=$result->fetch_assoc()){
    $col1 = $row['col1'];  // col1 is a placeholder for whatever column you are reading
    //read columns
}

Tuesday, September 13, 2022

rastacraz

In order to see all 27 descriptions, you need to either print them within the loop or add them to an array. When you run a loop and output a single variable only after the loop has ended, it will display the last value assigned.

while ($row1 = $result1->fetch_assoc()) {
    $order_description = $row1['order_description'];
    echo $order_description."n";
}

while ($row1 = $result1->fetch_assoc()) {
    $descriptions[]=$row1['order_description'];
}
print_r($descriptions);

Note

@RiggsFolly rightly noted that you have one extra fetch_array call which gets wasted and hence you will never see the first row. Get rid of that.

Kick the third line of your code

$row1 = mysqli_fetch_array($result1, MYSQLI_ASSOC);

Wednesday, October 5, 2022

necromos

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysqli

Fetching one row only with MySQLi

Answers

php,mysqli