0

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?

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
SerafimSaudade
  • 3
  • 6
  • Thank you MGA for the edit. It´s easier to read now. :) – SerafimSaudade Jan 11 '12 at 10:50
  • 2
    Welcome to Stack Overflow! The code you show is vulnerable to [SQL injection](http://php.net/manual/en/security.database.sql-injection.php). Use the proper sanitation method of your library (like `mysql_real_escape_string()` in your case), or switch to PDO and prepared statements. – Pekka Jan 11 '12 at 10:51
  • 1
    @v01pe both (`!=`, `<>`) are fine and the same: [<>, != on MySQL manual](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_not-equal). – Michał Powaga Jan 11 '12 at 10:53
  • I forgot to add the mysql_query on the code above displayed but i had it on the original one. It still returns the same error on the same line. Please help. – SerafimSaudade Jan 11 '12 at 10:58
  • @Michał Powaga: OK cool, since when is this? I remember having an issue with this once, but good to know! Deleted the wrong comment… – v01pe Jan 11 '12 at 11:02
  • @v01pe since when? Don't know but this is described in [MySQL 3.23, 4.0, 4.1 Reference Manual - "!=, <>"](http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#operator_not-equal). – Michał Powaga Jan 11 '12 at 13:04

3 Answers3

2

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.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • 1
    This is the right direction, but it is not doing any error checking in the query, so if anything goes wrong, it will break if the query fails. How to do this is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) - also, this is still vulnerable to SQL injection – Pekka Jan 11 '12 at 10:52
  • @Pekka but this is a bit different topic described on page about `mysql_query` (link provided in answer). – Michał Powaga Jan 11 '12 at 10:57
  • 1
    I would at least fix the SQL injection. Having that in an accepted answer is not a good thing. – Pekka Jan 11 '12 at 10:58
  • I am a newbie to SQL and PHP, so i am doing this and learning in the process. I will attend the SQL injection security problem. Is there a more newbie-friendly topic or link for SQL injection? – SerafimSaudade Jan 11 '12 at 11:07
  • So everytime i do a POST from a previous form, i have to use the mysql_real_escape_string function? What does this really do, in a more simple newbie-language for me to understand the concept? – SerafimSaudade Jan 11 '12 at 11:10
  • @MichałPowaga The code still doesnt work. Heeeeeelp! Please... :D – SerafimSaudade Jan 11 '12 at 11:17
  • @MichałPowaga found the error. The problem was that on table quartos i have id_quarto and also the same on table reservas. So when i tried to return the results from the id_quarto field it said it was ambiguous, so i renamed the field in reservas to another name and it was fixed. – SerafimSaudade Jan 12 '12 at 12:43
  • @SerafimSaudade the better way than renaming column in table are aliases (for tables) or just table names with column name. Listed just after `select`. If same column is in both tables then use `table_name.col` (or `alias.col`) to specify which column exactly is required. As in my edited version. – Michał Powaga Jan 12 '12 at 13:17
0
  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
Ed Heal
  • 59,252
  • 17
  • 87
  • 127
-1

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!

v01pe
  • 1,096
  • 2
  • 11
  • 19
  • About the `:` and `endwhile`, that's simply not true. Please make sure your suggestions are accurate by trying them first. – Lightness Races in Orbit Jan 11 '12 at 11:55
  • @Lightness Races in Orbit: OK, thanks for clearification, I didn't know this eighter. And yes, the string needed a `"`. Damn that post was a mess, sorry for that! – v01pe Jan 11 '12 at 13:39