Prepared语句不提取数据

Question

$id = $_REQUEST['id'];

$stmt = $conn->prepare('SELECT * FROM test WHERE id = ?');
echo $id;
$stmt->bind_param('s', $id);

$stmt->execute();

// This is line 12
while ($row = $stmt->fetch()) {
    $test = $row['test'];
}

Okay here we go. This code doesn't work, cause I basically I get an error that tells me following

#0 /example/example.php(2): require()
#1 {main}
  thrown in /example/example.inc.php on line 12

I don't know what I am doing wrong, but I already tried bind_result() and $stmt->fetch_assoc() which aren't working too. I read a lot of other questions here but they didn't help me.

Here is the connection

<?
$servername = "exase";
$username = "exaus";
$password = "exapw";
$dbname = "exa_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

Answer 1

When using a MySQLi prepared statement, it's a little different than using a standard query. You will need to use mysqli_stmt::bind_result(), or store the result-set with mysqli_stmt::get_result() before you can use the data when you fetch the result. Note that you need the MySQL native driver mysqlnd before you can use get_result() - otherwise you'll need to bind each column manually with bind_result().

Here's an example how to use bind_result(). Note that you need to bind as many columns as there are in the query, and since you do SELECT *, you need to bind everything you have in the table - but, that method will fail if you later add a column to your table. It's therefor better to select just the columns you need, like SELECT id, test FROM...

$id = $_REQUEST['id'];

$stmt = $conn->prepare('SELECT test, id FROM test WHERE id = ?');
$stmt->bind_param('s', $id);

$stmt->execute();
$stmt->bind_result($test, $result_id);
$stmt->fetch()); 

/*
* If there was any matching rows, the variables defined in bind_param()
* now hold the values
* You can then use '$test' and '$result_id'
*/
echo $test;

$stmt->close();

If you however have the MySQL Native Driver installed, you can use get_result() and use it as a "regular" query. Then it doesn't matter all that much if you do SELECT * (although I don't recommend ever selecting everything - you should select the columns you need, and nothing more).

$id = $_REQUEST['id'];

$stmt = $conn->prepare('SELECT * FROM test WHERE id = ?');
$stmt->bind_param('s', $id);

$stmt->execute();
$result = $stmt->get_result(); // $result is now an object of MySQLi resource, and not MySQLi statement
                               // It can now be used as as the result-set of a regular query

$row = $result->fetch_assoc());
$test = $row['test'];
echo $test;

$stmt->close();

• PHP.net on mysqli_stmt::get_result()
• PHP.net on mysqli_stmt::bind_result()