PHP MySQLi echo data in array without doing a while loop

Question 1

When using MySQLi, do I have to perform a kind of while loop where the actual data from the query is put into a variable array?

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

// Check if able to connect to database
if ($conn->connect_error) {
  trigger_error("Database connection failed: "  . $conn->connect_error, E_USER_ERROR);
}

$sql = "SELECT name FROM users WHERE email = '$email'";

$rs = $conn->query($sql);
$numRows = $rs->num_rows();

I always do the following:

$rs->data_seek(0);
while($row = $rs->fetch_assoc()) {
    $name = $row['name'];
}

echo $name;

Isn't there a much more convenient way to echo the data form the query when there is only one row?

Question 2

If there is only one row, you don't need the loop. Just do:

$row = $rs->fetch_assoc();
$name = $row['name'];

Question 3

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();

?>

Question 4

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.

Question 5

Wrap the result in another table.

echo "<table>";
$count = 0;
$num_columns = 2;  // or 3
while ($rc = mysql_fetch_array($results_course)) {
    if ($count++ % $num_columns == 0) {
        echo "<tr>";
    }
    echo "<td>";
    // previous table code here
    echo "</td>";
    if ($count % $num_columns == 0) {
      echo "</tr>";
    }
}
if ($count % $num_columns > 0) {
  echo "</tr>";
}
echo "</table>";

Question 6

Oneliner :)

while (e.InnerException != null) e = e.InnerException;

Obviously, you can't make it any simpler.

As said in this answer by Glenn McElhoe, it's the only reliable way.

PHP MySQLi echo data in array without doing a while loop

Answers

php,mysqli