Prepared statement not working when retrieving multiple columns

Question

I'm trying to get the name and email from users table where the userid is the session['id'] which I set somewhere else. BTW, is this a good practice, putting user id in session variables?

EDIT: Upon further testing, this problem only occurs when I retrieve things from the users table, single or multiple columns. Using WHERE email/username/name/etc = other than WHERE userid = gives the same bind_param() error. But this only happens when using prepared statement. And there's no typo.

The prepared statement gives me Fatal error: Call to a member function bind_param() on a non-object. The same code (in the same php file, just below the following code) when retrieving one column works just fine.

$sessionid = $_SESSION['id'];

// Prepare
$stmt = $conn->prepare('SELECT name, email FROM users WHERE userid = ?');

// Set Parameters
$ps_userid   = $sessionid;

// Bind
$stmt->bind_param("i", $ps_userid);

// Execute
$stmt->execute();
$stmt->bind_result($mail_name, $mail_email);
$stmt->fetch();

`The code below works just fine

$data       = mysqli_query($conn, 'SELECT name, email FROM users WHERE userid='.$_SESSION["id"]);
$row        = mysqli_fetch_array($data, MYSQL_ASSOC);
$mail_name  = $row['name'];
$mail_email = $row['email'];

Answer 1

This code should do better. I put in comments why the change is there.

// first test your connection is valid
if ($conn->connect_error) {
    die('Connect Error: ' . $conn->connect_error);
}
// Prepare, without parentheses around question mark. 
$stmt = $conn->prepare('SELECT name, email FROM users WHERE userid = ?')
      or die($conn->error);

// First Set Parameters(!)
$userid   = $_SESSION['id'];

// Only then: Bind
$stmt->bind_param('i', $userid);

// Execute and show error if any
$stmt->execute() or die($stmt->error);
$stmt->bind_result($mail_name, $mail_email); // needs to be this way
$stmt->fetch() or die ("no results");  // fetch is OK, but make sure you have result.

// output variables have received the result values:
echo $mail_name;
echo $mail_email;

So there are in fact the following issues:

• The input variables need to have their values before you bind them, otherwise it comes too late
• The output variables you bind need to be listed as separate arguments; an array is not what is expected here. Then you can address these bound output variables without further processing.
• Parentheses around the placeholder (?) are not needed.
• Do some error checking, displaying error messages.

Answer 2

I am almost sure the problem is in this code:

// Bind
$stmt->bind_param('i', $userid);
// Set Parameters
$userid   = $_SESSION['id'];

You can't bind undefined variable because bind_param takes a reference to it and there can not be reference to undefined variable, just swap the lines.

// Set Parameters
$userid   = $_SESSION['id'];
// Bind
$stmt->bind_param('i', $userid);

also your comment is right about binding result, use $stmt->bind_result($mail_name, $mail_email);

If you are new to mysqli documentation on php.net is your great friend. Just scroll down to some examples

Answer 3

Try assigning $userid value before binding the param, set then bind

// Set Parameters
$userid   = $_SESSION['id'];

// Bind
$stmt->bind_param('i', $userid);