Prepared statement doesn't fetches data

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

What's the actual error message? (the line above that stack trace) — rickdenhaan, Sep 01 '17 at 14:40
You should bind the param as an int, not a string. `$stmt->bind_param('i', $id);` not sure if that will fix your issue or not, but you aren't doing it properly at the moment. — GrumpyCrouton, Sep 01 '17 at 14:40
Jay waot O cam ad the connection but I have several other scripts, and the all work fine — onebooredguy, Sep 01 '17 at 14:41
GrumpyCrouton you are answered my last questions about the switch statement :D no the id isn't a number it contains only letters — onebooredguy, Sep 01 '17 at 14:43
Possible duplicate? https://stackoverflow.com/questions/750648/select-from-in-mysqli — GrumpyCrouton, Sep 01 '17 at 14:47

clearshot66 · Answer 1 · 2017-09-01T14:50:13.067

0

You're not doing prepared statements right, see this:

 $id = $_REQUEST['id'];

    $stmt = $conn->prepare('SELECT col1,col2,col3 FROM test WHERE id = ?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->bind_result($col1,$col2,$col3);  // you need to bind the result when using prepared statements
    $stmt->store_result();  // store it so if the number of rows retrieved is large they won't be dropped during loop or cause the overload error


    while ($stmt->fetch()) {
        $test = $col1;
         echo $col2;
    }

$stmt->close();

edited Sep 01 '17 at 14:50

answered Sep 01 '17 at 14:43

clearshot66

2,292
1
8
17

I need all columns – onebooredguy Sep 01 '17 at 14:44
What's the problem with selecting *? – GrumpyCrouton Sep 01 '17 at 14:46
Related https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select https://stackoverflow.com/questions/487578/performance-issue-in-using-select/487612#487612 https://stackoverflow.com/questions/262450/why-is-using-to-build-a-view-bad - thanks @clearshot66 I didn't know this :) – GrumpyCrouton Sep 01 '17 at 14:48
I guess you could but it's not typical and you STILL need to bind_result, you don't use $row associative array when using $stmt->fetch – clearshot66 Sep 01 '17 at 14:49

Qirel · Accepted Answer · 2017-09-01T15:10:37.973

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

one thing I don't get is, why I have to make a while if already got the results in bind_result, or isn't this so ? — onebooredguy, Sep 01 '17 at 14:56
Because that will just bind the result for each time `fetch()` is called (if you have multiple rows). They don't contain the value until you fetch it. If you just have one row matching that ID, you can drop the `while`, but you still need to `fetch()` it before the variables defined in `bind_result()` hold any value from the database. — Qirel, Sep 01 '17 at 14:57
the id column has a primary key so it is unique, but how can I fetch it ? — onebooredguy, Sep 01 '17 at 15:00
If it's unique, you can drop the `while`, but you still need to fetch before you can use the variables. — Qirel, Sep 01 '17 at 15:00
but I mean how to fetch them ? shoul I put the variables into the fetch ? like $stmt->fetch($test1, $test2); — onebooredguy, Sep 01 '17 at 15:06
Like it shows in my answer and in the manual, `bind_result()` binds the variables that will hold the values when the query is executed and fetched. When you do `fetch()`, those variables will have their value - but not before you do `fetch()`. The `fetch()` method doesn't have *any* arguments, you don't pass anything to it. — Qirel, Sep 01 '17 at 15:08

Prepared statement doesn't fetches data

2 Answers2