Taking data from two tables

Posted on 2005-04-11
Last Modified: 2008-03-06
have two tables related to this query: car table and reservation table.
In the car table i have:
In the reservation table I have :

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

Question by:acslater
    LVL 2

    Accepted Solution

    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.

    Author Comment

    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

    Author Comment

    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;
          echo"There are no Records with the location You have entered.";?>        <P>  
              <a href="orderpage3.php">
          echo"Please Click Back";
          while($row = mysql_fetch_assoc($result)) //;
          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">
                  <td width="141" height="74" valign="top">                                                                                    <? echo $row["Model"]; ?><b>
                  <td width="41" valign="top">                                          <b>
                    <? $filepath=$row["size"]; echo "<img src='$filepath'>";?>
                  <td width="59" valign="top">                            <b><b>
                  <center><? $filepath=$row["people"]; echo "<img src='$filepath'>";?></center>
                  <td width="48" valign="top">                            <b>
                  <? $filepath=$row["suitcase"]; echo "<img src='$filepath'>";?>
                  <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'; ?>>

    $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
    LVL 2

    Expert Comment

    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.

    Featured Post

    Easy Project Management (No User Manual Required)

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is a general how to create your own custom plugin system for your PHP application that you designed (or wish to extend a third party program to have plugin functionality that doesn't have it yet).  This is not how to make plugins for existing s…
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now