Binding the results of an sql select query using php

Question

In the code below "$_POST['search']" is entered by a user into a form. If a valid primary key from my database matches their search a row from the table will be displayed. I need to be able to access the actual primary key for another use in my program. For some reason the code below isn't storing anything in my $col variables.

 $stmt2 = mysqli_prepare($link, "SELECT * from classes WHERE course_id LIKE ?");
 mysqli_stmt_bind_param($stmt2,'s', $_POST['search']);
 mysqli_execute($stmt2);

 mysqli_stmt_bind_result($stmt2, $col1, $col2, $col3, $col4, $col5, $col6);
 mysqli_stmt_fetch($stmt2);
 echo "<h1>This should be primary key: " . $col1 . "</h1>";

EDIT: The above code is now correct thanks to David but the solution caused an error in the next lines directly after. These are the error messages:

Warning: mysqli_fetch_field() expects parameter 1 to be mysqli_result, boolean given in

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in

$result = mysqli_stmt_get_result($stmt2);

echo "<br><br><table>";

while($row = mysqli_fetch_field($result)){
    echo "<th>\n";
    echo $row->name . "<br>";
    echo "</th>\n";
}

while($row = mysqli_fetch_array($result, MYSQLI_NUM)){
    echo "<tr>";
    foreach($row as $value){
         echo "<td>";
         echo $value . "<br>";
         echo "</td>\n";
 }

score 1 · Accepted Answer · answered Nov 14 '16 at 02:02

1

You've bound the variables to the result, but haven't fetched the records from the result. Take a look at the documentation examples. Something more like this:

mysqli_stmt_bind_result($stmt2, $col1, $col2, $col3, $col4, $col5, $col6);

while (mysqli_stmt_fetch($stmt2)) {
    echo "<h1>This should be primary key: " . $col1 . "</h1>";
}

Even if you want only the first record in the result, you still need to fetch that record:

mysqli_stmt_bind_result($stmt2, $col1, $col2, $col3, $col4, $col5, $col6);

mysqli_stmt_fetch($stmt2);
echo "<h1>This should be primary key: " . $col1 . "</h1>";

answered Nov 14 '16 at 02:02

David

208,112
36
198
279

Thanks, this fixed my original problem. However this caused an error further down in my program. I edited my original post for details – Omega Collision Nov 14 '16 at 02:10
1

@OmegaCollision: You've neglected to mention what the error is or describe the problem. – David Nov 14 '16 at 02:15
@OmegaCollision: Separate issue entirely. And a duplicate of this question: http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1 (among many others) – David Nov 14 '16 at 02:16

Binding the results of an sql select query using php

1 Answers1