?
Solved

Taking data from two tables

Posted on 2005-04-11
4
Medium Priority
?
198 Views
Last Modified: 2008-03-06
have two tables related to this query: car table and reservation table.
In the car table i have:
RegNo
Model
Make
transmission
pickup
price
status
In the reservation table I have :
OrderID
RegNo
username
pickup
dropoff
datePickUp
dateReturn
Model
Make

I have a page called displaycar2.php. In this page i want to show available cars for the dates chosen. But I don't want any cars that are in the reservation table that interfere with those dates.

Just say have car with reg no 05-TN-5445 reserved for the following dates.
12/04/2005 to 14/04/2005
and
15/04/2005 to 18/04/2005

If a person comes along and enters in dates for rental, lets say, pickup = 13/04/05 to the 17/04/2005. the above car option should not be shown, as it is not available between those dates. Only cars that are not reserved for those dates should be shown.

I have tried implementing it with a complex sql query but cudnt get it to work

i can post code i have at moment if ye want?

0
Comment
Question by:acslater
  • 2
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
rcmwizbit earned 2000 total points
ID: 13752644
Not sure how your date fields are setup, but the following should work at least in MySQL with DATE or DATETIME fields (just format $pickup and $return as yyyy-mm-dd)...

SELECT c.RegNo FROM cars c LEFT JOIN reservations r ON
(r.RegNo=c.RegNo AND ((datePickup BETWEEN '$pickup' AND '$return')
OR (dateReturn BETWEEN '$pickup' AND '$return')
OR (datePickup < '$pickup' AND dateReturn > '$return')))
WHERE r.reservation_id IS NULL;

Should return a list of registration numbers for cars available between dates $pickup and $return.
0
 

Author Comment

by:acslater
ID: 13755552
I have the fields set up yyyy-mm-dd in the database. Will that query though give dates from the reservation table where other cars are booked on?

for example if i want to book from mar 15 to mar 20, it shouldnt display any cars already reserved for them dates
0
 

Author Comment

by:acslater
ID: 13762050
Well i think that worked but there is a problem with it. It is not showing the details of each car. the following is the code the query and the code to show each car and details:

<?
include "db_connection.php";
$connect = db_connect();
if ($connect)
$_SESSION['pickup'] = $pickup;
{



$query = "SELECT c.RegNo FROM car c LEFT JOIN reservation r ON
(r.RegNo=c.RegNo AND ((datePickup BETWEEN '$datepickup' AND '$datedropoff')
OR (dateReturn BETWEEN '$datepickup' AND '$datedropoff')
OR (datePickup < '$datepickup' AND dateReturn > '$datedropoff')))
WHERE r.OrderID IS NULL AND transmission LIKE '%$transmission' AND c.pickup = '$pickup'";
//Selecting all Entries from the database


      $result = mysql_query($query,$connect)or die("invalid query: <br>$query<br>".mysql_error());
      echo $query;
                        
      if(mysql_num_rows($result)==0)
      {
      
      echo"There are no Records with the location You have entered.";?>        <P>  
          <a href="orderpage3.php">
      
      
          <?
      echo"Please Click Back";
      
      }
      $n=0;
      while($row = mysql_fetch_assoc($result)) //;
      {
        ?>       
          </center>
        <?
      if(dateDiff( $pickupdate, $dropoffdate )==0){
   $thisPrice = $row["price"];
}elseif(dateDiff( $pickupdate, $dropoffdate )==1){
$thisPrice = $row["price"] + $row["price"];
}elseif(dateDiff( $pickupdate, $dropoffdate )>1){
   $thisPrice = $row["price"] * dateDiff( $pickupdate, $dropoffdate ) + $row["price"];
}?>
      
          <table border="" cellpadding="0" cellspacing="1" style="border-collapse: collapse; border-width: 0" bordercolor="" width="100%" id="AutoNumber6">
            <!--DWLayoutTable-->
            <b>
            <tr>
              <td width="141" height="74" valign="top">                                                                                    <? echo $row["Model"]; ?><b>
              </b>              
              <td width="41" valign="top">                                          <b>
                <? $filepath=$row["size"]; echo "<img src='$filepath'>";?>
              </b>              
              <td width="59" valign="top">                            <b><b>
              <center><? $filepath=$row["people"]; echo "<img src='$filepath'>";?></center>
              </b></b>              
              <td width="48" valign="top">                            <b>
              <? $filepath=$row["suitcase"]; echo "<img src='$filepath'>";?>
              </b>              
              <td width="72" valign="top">                                                        <? echo $row["Make"]; ?>
            <td width="77" valign="top">                                                        <? echo $row["transmission"]; ?>
              <td width="107" valign="top">                                          <? echo $thisPrice; ?>  
              <td colspan="2" valign="top"><b>
  <input type="radio" name="RegNo" value="<? echo $row['RegNo']; ?>"<? if ($n==0) echo 'checked'; ?>>
                    </b>
            </tr>
                  
                 <?
                  $n++;

                  }
                  }      
mysql_free_result($result);
$period=dateDiff( $pickupdate, $dropoffdate );
?>

In the above i want to show the regno model make transmission and price along 3 pictures that or in the table as well, but im just getting empty rows
0
 
LVL 2

Expert Comment

by:rcmwizbit
ID: 13763124
You should just need to modify the "SELECT c.RegNo FROM" part of the query to include the fields you need - possibly "SELECT c.*, r.* FROM ..." if you need all data from both tables, otherwise something like:

SELECT c.RegNo, c.Make, c.transmission, c.size, c.people, c.suitcase, c.price FROM ...

should get the data you need as far as I can see from the code - assuming "size", "people" and "suitcase" are all fields from the "car" table.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question