mySQL query multiple - returns error mysql_fetch_array

Question

I have 2 database tables (for a booking system) with the following structures:

quartos:

• id_quarto.
• tipo_quarto.
• vista_quarto.

reservas:

• id_reserva.
• n_cliente.
• id_quarto.
• check_in.
• check_out.

I want the query to return the quartos (rooms) available (with the fields id_quarto / tipo_quarto / vista_quarto from it) which arent already being booked on reservas (reservations) so i write the following query (also picking information from a previous form):

NOTE: At this time i am not considering the check_in and check_out dates factor... this is only a test and therefore i will add the conditions to check it too, but if anyone has some ideas for those conditions i would be grateful. :D

// Connect to database server
mysql_connect("localhost", "root") or die (mysql_error ());
// Select database
mysql_select_db("teste") or die(mysql_error());
// Get data from the database

$strSQL = "SELECT id_quarto,tipo_quarto,vista_quarto ".
          " FROM quartos,reservas ".
          " WHERE quartos.id_quarto!=reservas.id_quarto ".
          " AND quartos.tipo_quarto='". $_POST['tipo_quarto'] ."' ".
          " AND quartos.vista_quarto='". $_POST['vista_quarto'] ."'";

// Loop the recordset $rs
    // Each row will be made into an array ($row) using mysql_fetch_array
    while($row = mysql_fetch_array($rs)) {

?>
<table border="1">
    <tr align="left">
    <td width="75"><?php echo $row['id_quarto']; ?></td>
    <td width="75"><?php echo $row['vista_quarto']; ?></td>
    <td width="75"><?php echo $row['tipo_quarto']; ?></td></tr>
  </table>
 <?php 
 }

   // Close the database connection
//  mysql_close(); ?>

But when I do this it returns an error on Line X, which is the line when i loop the recordset saying that "mysql_fetch_array() expects parameter 1 to be resource, boolean".

Why is this and what can i do to prevent it? how do i write the correct code?

Also, i wanted the results to be featured as a Select (List/Menu) form item so that user the could only choose the valid results. Any idea how to incorporate the results from the recordset with this feature?

Answer 1

You forget about mysql_query, change:

// Select database
mysql_select_db("teste") or die(mysql_error());

// Get data from the database

$strSQL = "SELECT id_quarto,tipo_quarto,vista_quarto FROM quartos,reservas WHERE quartos.id_quarto!=reservas.id_quarto AND quartos.tipo_quarto='". $_POST['tipo_quarto'] ."' AND quartos.vista_quarto='". $_POST['vista_quarto'] ."'";

// Loop the recordset $rs
// Each row will be made into an array ($row) using mysql_fetch_array
while($row = mysql_fetch_array($rs)) {

to:

// Select database
mysql_select_db("teste") or die(mysql_error());

// Get data from the database

$strSQL = "SELECT q.id_quarto, q.tipo_quarto, q.vista_quarto ".
          " FROM quartos q, reservas r".
          " WHERE q.id_quarto != r.id_quarto ".
          " AND q.tipo_quarto = '". mysql_real_escape_string($_POST['tipo_quarto']) ."' ".
          " AND q.vista_quarto = '". mysql_real_escape_string($_POST['vista_quarto']) ."'";

$rs = mysql_query($strSQL);

// Loop the recordset $rs
// Each row will be made into an array ($row) using mysql_fetch_array
while($row = mysql_fetch_array($rs)) {

Added: Prevent SQL injection using mysql_real_escape_string on each parameter from user.

Answer 2

1. You need to call mysql_query to get the resource set. See http://php.net/manual/en/function.mysql-fetch-array.php for an example.
2. Should escape the $POST variables

Answer 3

Compare strings with LIKE (if they aren't the index)

$strSQL = "SELECT id_quarto,tipo_quarto,vista_quarto FROM quartos,reservas WHERE quartos.id_quarto!=reservas.id_quarto AND quartos.tipo_quarto LIKE '". $_POST['tipo_quarto'] ."' AND quartos.vista_quarto LIKE '". $_POST['vista_quarto'] ."'";

---

The while between closing ?> tags can to be done different (it's clearer IMHO):

    while($row = mysql_fetch_array($rs)) :
?>

and

<?php
    endwhile;

and yeah, of course you'll have to do the actual mysql_query, like the others pointed out!