What to change this line of code to in mysqli

Question

I am trying to change my code from mysql to mysqli, I am having trouble changing this bit of code though:

while($sqlrow = mysql_fetch_array($sqlresult)) 
{ 
     $room = $sqlrow['Room']; 
     $roomHTML .= "<option value='".$room."'>" . $room . "</option>".PHP_EOL;  
} 


echo $roomHTML;

I do not have the variable $sqlrow in the mysqli code, below, what do I change the line $room = $sqlrow['Room']; to below:

Full code:

    $building = isset($_POST['building']) ? $_POST['building'] : ''; 

  $sql = "SELECT Room FROM Room WHERE Building = ?"; 

 $sqlstmt=$mysqli->prepare($sql);

 $sqlstmt->bind_param("s",$building);


     $sqlstmt->execute(); 

     $sqlstmt->bind_result($dbRoom);


    $roomHTML  = "";  

     while($sqlstmt->fetch()) { 
         $room = $sqlrow['Room']; 
         $roomHTML .= "<option value='".$room."'>" . $room . "</option>".PHP_EOL;  
    } 


    echo $roomHTML; 

     $sqlstmt->execute();

The above code is trying to display all the rooms in a dropdown menu which matches the building they belong in.

Thanks

You are exposing yourself to [SQL injection attacks](http://bobby-tables.com/) that mysqli would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from if you used prepared statements properly. — Quentin, Oct 25 '12 at 15:44
@Quentin Are you saying use bind_param() and use the ? instead of the variable in the query? If so I will change this later on — user1723710, Oct 25 '12 at 15:48
Yes. Absolutely. Don't build SQL by mashing strings together, especially when some of those strings come from outside the system. — Quentin, Oct 25 '12 at 15:55
"the mysqli (PDO) version"?! You use mysqli **or** PDO, one isn't part of the other. — Quentin, Oct 25 '12 at 16:08
@Quentin My mind just drifted off, I meant to put mysqli (Object) as in object orientated style, I don't know why I put PDO in brackets haha. Its ok problem solved, thanks to you upvote comment and to michael — user1723710, Oct 25 '12 at 16:15

score 0 · Accepted Answer · answered Oct 25 '12 at 16:06

0

You aren't using your bound result var $dbRoom.

// Your output value of interest is $dbRoom as you have bound here:
$sqlstmt->bind_result($dbRoom);
$roomHTML  = "";  

 while($sqlstmt->fetch()) { 
     // $dbRoom holds the value fetched from the result set, since that is what you bound above.
     // Note: added htmlentities() to encode quotes if there are any embedded in the room value.
     $roomHTML .= "<option value='". htmlentities($dbRoom, ENT_QUOTES) ."'>" . htmlspecialchars($dbRoom) . "</option>".PHP_EOL;  
}

answered Oct 25 '12 at 16:06

Michael Berkowski

267,341
46
444
390

Oh I undrstand now, the bind result puts all results into a particular field or in this example the "Room" field, If I had a second SELECT field (Department) and add a variable $dbdepartment into the bind_result() code, then all the results from $dbdepartment go into the Department field. Thank you very much, I understand now – user1723710 Oct 25 '12 at 16:11
@user1723710 Yes, you understand correctly. Bound result vars are references just like bound input vars. It is probably the most confusing thing about MySQLi (especially compared to the less verbose PDO) – Michael Berkowski Oct 25 '12 at 16:14

What to change this line of code to in mysqli

1 Answers1