PHP - Get Clashing Bookings

Hey guys,

I couldn't really think of a decent topic title for this but I hope by the end of it you'll know what I mean.

Brief background: I work in an IT department in a school. I am in the process of writing a page to show room and resource booking for the current day and for the next day. There are six blocks that bookings can be made (before school, periods one-four, after school). I have written a query to display all the bookings for a given day. I have looped the six available periods to it has a header for them, and have done the same for containers for the bookings.

What I am having problems with is getting it to show under the correct period for the start time and end time for the booking. For example, if a booking starts and ends period three, I want it to show just for period three. If it is a daily booking, it goes on all day.

My query is this and I am happy with it - phpmyadmin display everything I want and the start time and end time are correct.

Code: [Select]
(
SELECT
`bno` AS `bookingid`,
UPPER(`whofor`) AS `name`,
`timestart`,
`timeend`,
`where` AS `room`,
'' AS `comments`,
TRIM(TRAILING '||' FROM `What1`) AS `equipment`,
IF(`date` = '1', '1', '1') AS `type`
FROM
`booked`
WHERE
UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
AND `active` = 'yes'
AND `timestart` != 'all'
)
UNION (
SELECT `bno` AS `bookingid`,
UPPER(`whofor`) AS `name`,
'1' AS `timestart`,
'6' AS `timeend`,
`where` AS `room`,
'' AS `comments`,
TRIM(TRAILING '||' FROM `What1`) AS `equipment`,
IF(`date` = '1', '1', '1') AS `type`
FROM
`booked`
WHERE
UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
AND `active` = 'yes'
AND `timestart` = 'all'
)
UNION (
SELECT `bid` AS `bookingid`,
UPPER(`name`),
`period`,
`period`,
IF(`room` = '8', 'Lower Laptops', 'Middle Laptops') AS `room`,
`comments` AS `comments`,
'',
IF(`timebooked` = '2', '2', '2') AS `type`
FROM
`bookedrooms`
WHERE
UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
AND `room` IN(8,9)
AND `name` != 'not available'
AND `active` = 'yes'
)
ORDER BY `timestart`, `type` ASC
$time = (isset($_GET['tomorrow'])) ? strtotime('tomorrow 00:00') : strtotime('today 00:00');

Just wondering if anybody here has ever done anything similar to this to give me a hand.

Thanks in advance!

Hi guys,

I am trying to get data from the table "jobs" and insert its id and name into table "bookings"

I can get the job "name" from "jobs", which is fine, using the following:

                           <select class="form-control" id="tour_name" name="tour_name">
                               <option value="Select">== Select Tour or Charter ==</option>
<?php
$sql = "SELECT name FROM jobs";
$result = $con->query($sql);
        while(list($name) = mysqli_fetch_row($result)){
       ?>
       <option value="<?php echo $name ?>"><?php echo $name;?></option>
<?php
}
?>
                           </select>

However, when I click submit to insert into "bookings" everything goes in except "booking_id" which is "id" in "jobs". Hope this makes sense.

I am using the following to insert:

if(isset($_POST['new']) && $_POST['new']==1){

$sql = "SELECT id FROM jobs";
$result = $con->query($sql);
        while(list($id) = mysqli_fetch_row($result)){

}
    $tour_id = isset($_GET['id']) ? $_GET['id'] : '';
    $tour_name = mysqli_real_escape_string($con, $_POST['tour_name']);
    $customer_name = mysqli_real_escape_string($con, $_POST['customer_name']);
    $customer_address = mysqli_real_escape_string($con, $_POST['customer_address']);
    $customer_email = mysqli_real_escape_string($con, $_POST['customer_email']);
    $customer_phone  = mysqli_real_escape_string($con, $_POST['customer_phone']);
    $total_pax = mysqli_real_escape_string($con, $_POST['total_pax']);
    $status = mysqli_real_escape_string($con, $_POST['status']);
    $order_at = mysqli_real_escape_string($con, date("Y-m-d H:i:s"));
    $total_amount = mysqli_real_escape_string($con, $_POST['total_amount']);
    $query="insert into bookings (`tour_id`, `tour_name`, `customer_name`, `customer_address`, `customer_email`, `customer_phone`, `total_pax`, `status`, `order_at`, `total_amount`)values ('$tour_id', '$tour_name', '$customer_name', '$customer_address', '$customer_email', '$customer_phone', '$total_pax', '$status', '$order_at', '$total_amount')";
  	
    mysqli_query($con,$query)
    

    or die(mysqli_error($con));
    if(mysqli_affected_rows($con)== 1 ){
    $message = '<p class="text-success"><i class="fa fa-check"></i> - Record Inserted Successfully</p>';
    }

}

Can anyone please lend a hand with this? 2 Days at it now and ready to hit the Guinness.

 

Cheers,

Dan