Page 1 of 1

Problems querying database

Posted: Thu Apr 15, 2004 8:17 am
by gerrymac
MOD EDIT: PLEASE CAN YOU USE THE BBCODE TO HIGHLIGHT YOUR PHP....THANKS :)

Hi

I am currently developing a booking system. I will be renting holiday homes. On one menu i have a drop down menu where the user select the housename that he/she wishes to stay in and they also enter a start date and an end date. to ensure that there is no double booking ,the system must first check that this house is available for the period of time. I have managed to get the date fields checking against the fields in the database but i am unable to get the house one working Heres an example:

I have two houses, one called houseA and HouseB. HouseA has been booked for the week 2004-02-01 - 2004-02-07. Now if a new user comes along and tries to book this house for that week the system should say "soory house booked". And it does. The problem is that it does the same for houseB as well. The housnames for the drop down menu are obtained by a connection to the house table.

Code: Select all

?php



$connection=mysql_connect("localhost","root","");


if (!$connection)
{
	echo "Could not connect to MySQL server!";
	exit;
}

$db=mysql_select_db("travel_system",$connection);

if (!$db)
{
	echo "Could not change into the database";
 	exit;
}




if($enddate < $startdate)
{
echo "booking out date must be later than the book in date!";
}




if($startdate < $enddate)
              {
              
              $query0 = "select * from custorder
              where '".$housename."' = Housename";
              
              $mysql_result0 = mysql_query($query0);
              $num0 = @mysql_num_rows($mysql_result0);
              
              if ($num0 > 0)
              {
         
              
              
              $query = "select * from custorder
              where '".$startdate."' >= startdate AND '".$startdate."' <= enddate";



              $mysql_result = mysql_query($query);


              $num = @mysql_num_rows($mysql_result);

                if ($num > 0 ){
                $valid = false;
                 echo "sorry this date is not available1";
                 }
               

                 $query2 = "select * from custorder
                 where '".$enddate."' >= startdate AND '".$enddate."' <= enddate";



                 $mysql_result2 = mysql_query($query2);


                 $num = @mysql_num_rows($mysql_result2);

                 if ($num > 0 )
                 {
                  $valid = false;
                   echo "sorry this date is not available2";
                    }


     

                    $query3 = "select * from custorder
                     where '".$startdate."' <= startdate AND '".$enddate."' >= enddate";




                     $mysql_result3 = mysql_query($query3);


                     $num = @mysql_num_rows($mysql_result3);

                      if ($num > 0 ){
                        $valid = false;
                         echo "sorry this date is not available3";

                          }
     
                          }
     else {

     echo "dates available";
     }


     
 
?>
Does anyone have any idea what could be wrong? I have checked that i ma calling te text boxes and drop down boxes the correct name ie $startdate, $enddate and $housename. Basically i think that the problem lies with the first query. The other queries work fine.

Any help would be greatly appreciated.

Thx

Gerry

Posted: Thu Apr 15, 2004 8:30 am
by JayBird
shouldn't this query

Code: Select all

$query0 = "select * from custorder 
              where '".$housename."' = Housename";
be..

Code: Select all

$query0 = "select * from custorder 
              where Housename = '".$housename."'";

Posted: Thu Apr 15, 2004 8:48 am
by gerrymac
it possibly should, does this usually make a difference?

Posted: Thu Apr 15, 2004 8:50 am
by malcolmboston
well unless you have a result in your database called Housename then your not gonna get anything back

$housename does not mean its looking for that result, it means it looking for a value represented by that 'string' ive probably just confused you to death, but its very easy to grasp

Mal

Posted: Thu Apr 15, 2004 8:51 am
by JayBird
yeah a big difference.

You need to get you logic right.

in the WHERE clause, the value on the left of the = sign in the column name, and the value on the right is the value you are comparing.

Mark

Posted: Thu Apr 15, 2004 12:16 pm
by gerrymac
Hi guys ive tried the changes you suggested but to no avail as it still dosent work. Ive established that it is the first query that is causing the problems as if I put ($num == 0) then program enters the if statement and carries out the queries. This results in being unable to book any house if there is a booking for a single house at a time. EG if there is a booking for houseA and you try to book houseB for the same period it will not allow you to. Here is my PHP and html code for entering the information. The PHP code at the top displays a list box with all the housename from the house table. In the html part the user enter the dates they wish to book for.

Code: Select all

<?php
<?php



$connection=mysql_connect("localhost","","");
# use the following connection if you are logging in with no username # / password
# $connection=mysql_connect("localhost","","");

if (!$connection) {
 echo "Could not connect to MySQL server!";
 exit;
}
$db=mysql_select_db("travel_system",$connection);

if (!$db) {
 echo "Could not change into the database";
 exit;
}





$sql="SELECT housename FROM house";

$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result);

if  ( $num_rows == 0 ) {
echo "Sorry there is no information";
}

 else
{

     echo "Please select a house <BR>";
     echo "<SELECT NAME="housename">";
      while ($row=mysql_fetch_array($mysql_result))
      {
            $f_house=$row["housename"];
            # display results
            echo "<OPTION>$f_house";
            }
            echo "</SELECT>";
} # end else
?>




<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>




<body bgcolor="#FFFFFF">




form method="post" action="date.php">


  <p>&nbsp;</p>
  <table width="75%" border="0" cellpadding="2">
    <tr> 
      <td width="29%">Enter Start Date </td>
      <td width="71%"> 
        <input type="text" name="startdate">
      </td>
    </tr>
    <tr> 
      <td width="29%" height="51">Enter End Date </td>
      <td width="71%" height="51"> 
        <input type="text" name="enddate">
      </td>
    </tr>
  </table>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>
    <input type="submit" name="Submit" value="Check Dates">
  </p>
</form>
</body>
</html>
?>
?>
As you can see the option IS called housename. Bascially wht im doing is comparing the value of a option box with the value in the database. Is this allowed in PHP? I have tried this using a text box where the user enters the house and it works as it should. However i could not use this method in the system as users could simply enter a wrong house and the query would not see anything wrong as there would be no dates for that house in the database as it does not exist and the user would end up being booked in a house that does not exist.

Please Help!

Gerry

Posted: Thu Apr 15, 2004 1:10 pm
by magicrobotmonkey
I don't see you closing the <option> tag anywhere - that could be a problem - also, why is the box output before the <html> tags or any other tags?

I think you should have

"<OPTION value=\"$f_house\">$f_house</option>"