是否可以将sql表值分配给php中的变量？

Question

Basically what I want is to assign the value of a field in my database to an variable. Can it be done in an effective way? I was thinking something like:

$sql2 = mysql_query("SELECT * FROM rom WHERE idrom = 101");
while ($row = mysql_fetch_array($sql2)) {
$rom1 = $row['idrom'];
$status = $row['status'];
echo $rom1;
echo $status;
}

But this doesn't echo anything.

Edit:

I have gotten a bit longer on the way, now I am looking for a simpler way to assign the values to variables. As we speak I only need 4 values, but this still doesn't look like a very good way to accomplish what I want. Any better suggestions?

Heres what I got now:

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM rom WHERE idrom = 101";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    $room101 = $row["idrom"];
    $status101 = $row["status"];

    echo "This is roomnumber ". $room101 . "!<br >";
    echo "And the status of roomnumber ". $room101 ." is ". $status101 ."<br><br>";
}

} else {
echo "0 results";
}

$sql2 = "SELECT * FROM rom WHERE idrom = 102";
$result2 = $conn->query($sql2);
if ($result2->num_rows > 0) {
// output data of each row
while($row = $result2->fetch_assoc()) {
    $room102 = $row["idrom"];
    $status102 = $row["status"];

    echo "This is roomnumber ". $room102 . "!<br >";
    echo "And the status of roomnumber ". $room102 ." is ". $status102 ."<br><br>";
}

} else {
echo "0 results";
}

Answer 1

You can use bind_result to do that.

Please try this simple example, hope it run well :

$mysqli = mysqli_connect('host', 'user', 'pass','dbase')or die('Could not connect: ' . mysqli_error());

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Because you provide an Id in where clause, you can use it for the new variable
$output = array();

$id = 101;
$sql = "select idrom,status from rom where idrom=?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i',$id);
$stmt->execute();

if ($stmt->errno == 0) {
    $stmt->store_result();
    // $stmt->bind_result($idrom,$status); // way 1
    $stmt->bind_result($output[$id],$output["status".$id]); // way 2

    while ($stmt->fetch()) {
        echo $output[$id]." -> ".$output["status".$id]."<br />"; // So, your output variable will be like $output[101] for way 2
        // or you defined it here like :
        // $output[$idrom] = $idrom;
        // $output["status".$idrom] = $status;      // For way 1
    }
} else {
    return "Error: " . $sql . "<br>" . $stmt->error;
}

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