SELECT multiple columns using mySQL prepared statement

Question

This successfully retrieves the data from table, but this does not use a mySQL prepared statement.

$sql = "SELECT email, created_at, firstname, lastname FROM users WHERE id ='" . $_SESSION['id'] . "'";
$result = mysqli_query($link, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $email_address = $row["email"];
        $creatiom_time = $row["created_at"];
        $fname = $row["firstname"];
        $lname = $row["lastname"];
    }
}

How can I convert this into a prepared statement as to prevent SQL injection? My attempt at converting it does not retrieve the data.

$stmt = mysqli_prepare($link, "SELECT email, created_at, firstname, lastname FROM users WHERE id=?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->bind_result($email_address, $creatiom_time, $fname, $lname);
$stmt->close();

What am I doing wrong? The email is unique, so this should only return one row; however, nothing is returned when I use the above prepared statement. Thank you.

Nick · Accepted Answer · 2019-05-26 02:16:41Z

3

You need to actually store the row data into the bound result variables with fetch:

$stmt = mysqli_prepare($link, "SELECT email, created_at, firstname, lastname FROM users WHERE id=?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->bind_result($email_address, $creatiom_time, $fname, $lname);
$stmt->fetch();
$stmt->close();
echo $email_address; // etc.

Note that you really should be checking the return status from these calls, particularly the prepare, execute and fetch.

edited May 26, 2019 at 2:16

answered May 26, 2019 at 2:09

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SELECT multiple columns using mySQL prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related