Select MySqli OO with prepared statements

Question

How can a SELECT statement be run with MySqli OO using prepared statements?

I am trying to learn and I could do the INSERT, DELETE, and UPDATE statements, but I have problems with SELECT. I have been searching but I still do not understand SELECT with prepared statements (read the PHP man page for prepared statements).

Without prepared statements, it works well:

$sql = "SELECT title FROM test
        WHERE id = 2";
    $result = $conn->query($sql);

    while($row = $result->fetch_assoc()) {
        echo $row["title"] . "<br>";
    }

    $conn->close();

With prepared statements:

What I tried does not work.
I think I have problems with showing the data in this case. Can someone explain it please?

$stmt = $conn->prepare("SELECT title 
                        FROM test 
                        WHERE id = ?");

$stmt->bind_param("i", $id);
$id = 18;
$stmt->execute();
$stmt->bind_result($title);

while($stmt->fetch()) {
    echo $row["title"] . "<br>";
}

$stmt->close();
$conn->close();

$stmt->bind_param("i", $id); $id = 18; invert these. You're putting the horse before the wagon here, as it were. — Funk Forty Niner
@Jay, Fred the answer of Your Common Sense works well and puts $id after. Can you explain? — segon
Sure it works and I can explain it, but he's the one who should be doing that, not us/me. — Funk Forty Niner

Your Common Sense Your Common Sense · Accepted Answer · 2017-01-09T15:40:39

It makes me wonder why you weren't able make it to the end of example and started devising a syntax of your own. What do you think bind_result($title) is for? And where did you get $row from?

$stmt = $conn->prepare("SELECT title FROM test WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 18;
$stmt->execute();
$stmt->bind_result($title);

while($stmt->fetch()) {
    echo $title . "<br>";
}

Select MySqli OO with prepared statements

With prepared statements:

1 Answers