-1

Possible Duplicate:
mySQL query multiple - returns error mysql_fetch_array

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='". mysql_real_escape_string($_POST['tipo_quarto']) ."' ".
              " AND quartos.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)) {

    ?>
    <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?

Community
  • 1
  • 1
SerafimSaudade
  • 3
  • 6
  • 1
    You are still not using `mysql_query()`, are you? – Pekka Jan 11 '12 at 11:43
  • Is the `"SELECT id_quarto"` in your query supposed to be referring to `quartos.id_quarto` or `reservas.id_quarto`? And does that query even work without specifying that?! Where are you executing the query? – Mark Byers Jan 11 '12 at 11:45
  • Duplication of http://stackoverflow.com/questions/8817940/mysql-query-multiple-returns-error-mysql-fetch-array. – Ed Heal Jan 11 '12 at 11:45
  • @MarkByers it is reffering to quartos.id_quarto – SerafimSaudade Jan 11 '12 at 11:49
  • You're _still_ not checking the result of the query. – Lightness Races in Orbit Jan 11 '12 at 11:53
  • @SerafimSaudade: If that's what you meant, then why don't you write that? Also, you seem to want rows in one table where there is no matching row in the other, right? So why are you doing a inner join instead of for example a LEFT JOIN or a NOT EXISTS? Does your query work when you run it directly in MySQL? Have you even tried that?! – Mark Byers Jan 11 '12 at 11:56

2 Answers2

1

You missed

$rs=mysql_query($strSQL)

Ofcourse immediately before 

// Loop the recordset $rs
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
0

Strictly you should be saying:

if ($rs=mysql_query($strSQL)) {

// Proceed with while loop

} else echo mysql_error();

The query might be raising an error???

Prof
  • 2,898
  • 1
  • 21
  • 38