Omega Collision Omega Collision - 23 days ago 6
PHP Question

Binding the results of an sql select query using php

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

Answer

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>";