如何从数据库中使用php自己获取值？

Question

I am trying to access firstname by it self. I have the code below put together:

<?php
$sql = "SELECT firstname, lastname FROM guests WHERE option = $option";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "You Are " . $row["firstname"]. " " . $row["lastname"]. "<br>";
?>

It gives me You are Bob Testing. I need to convert them to self variable so I can use them anywhere. Like $row["firstname"] = $firstname; so then I could echo $firstname; But it won't work if I use $row["firstname"] = $firstname;

I think the issue is somewhere in how I form the result $result = mysqli_query($conn, $sql); Can I say something else here so then I could just use say $row["firstname"] = $firstname; and use like echo $firstname;? Thanks.

</div>

Answer 1

Firstly, if this is your actual code, it's missing a few closing braces.

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "You Are " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    } // this one was missing

} // as was this one

Now, assign a variable "to" the row(s) and not the other way around.

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {

        $first = $row["firstname"];
        $last = $row["lastname"];
    }
        echo "You are " . $first . " " . $last . "<br>";
}

However the above will only echo a single row, therefore you will need to place the echo "inside" the while loop in order to echo all the rows in your table:

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {

        $first = $row["firstname"];
        $last = $row["lastname"];

        echo "You are " . $first . " " . $last . "<br>";
    }

}

---

Something about this though WHERE option = $option";

If $option is a string, it will need to be quoted:

WHERE option = '$option'";

otherwise, MySQL will see that as a syntax error. Check for errors on your query:

• http://php.net/manual/en/mysqli.error.php

It will also be prone to an SQL injection, therefore it is best you use a prepared statement.

• https://en.wikipedia.org/wiki/Prepared_statement

Seeing you may be new to working with MySQL, it's best to learn about protecting yourself against SQL injection. Here is a good article about it on Stack:

• How can I prevent SQL injection in PHP?