displaying the contents of a mysql query, only first field works

Question

I am using a mysqli prepard query with php, the code is:

$retreiveQuery = 'SELECT username, firstname, lastname FROM USERS WHERE username = ?';

if ($getRecords = $con->prepare($retreiveQuery)) {
    $getRecords->bind_param("s", $username);

    $getRecords->execute();        
    $getRecords->bind_result($username, $firstname, $lastname);

echo "<h1>".$username."</h1>
<p><strong>First Name: </strong>".$firstname."
<p><strong>Surname: </strong>".$lastname."
} else {
print_r($con->error);

This is quite strange. username is the only field which is displayed. Why would the other columns not be being returned/bound?

If I do

SELECT username, firstname, lastname FROM USERS WHERE username = 'test';

Directly to the database, all fields are displayed, and all contain valid data.

Answer 1

What happens if you use a different variable name for output of username? Like this:

SELECT username as user_name, firstname, lastname FROM USERS WHERE username = 'test';

and then

$getRecords->bind_result($user_name, $firstname, $lastname);

I wonder if it's getting confused by using the same name for both input and output?

Answer 2

Hi,

I haven't run this, but I notice that you are using the same variable, $username for both the bound param and bound result variable.

It may be that the result is not returning ANY of them, but $username has a value displayed because you are assigning it beforehand, not because it's coming from the database.

Try using different varaibles like $param_user and $result_user, and see what happens. My guess is that none of the result will be returned, which will point to a problem with the query or the result binding.

Answer 3

You have to call

$getRecords->fetch();

after bind_result() to actually get the record.

Username was being output because you had already set it, to use as an input parameter.