检索要在PHP代码中使用的数据库记录

Question

I am making code for a studio reservation where customers can reserve for how many hours in a day... for example, a customer inputs Date: October 17, 2011 Time In: 10:30:00 am Time out: 11:30:00 am

In that case, another customer must not be able to input a time in/ timeout between 10:30 to 11:30 am with the same date.

before making a database, i have tried this code:

<?php 
$resttimefrom='10:00:00 am';
$resttimeto='11:00:00 am';
$reserve='11:01:00 pm';
$datedat='2012-10-14';


$st_time    =   strtotime($resttimefrom);
$end_time   =   strtotime($resttimeto);
$reserve   =   strtotime($reserve);
$datedat   =   strtotime($datedat);

print $st_time; echo "<br>";
print $end_time; echo "<br>";
print $reserve; echo "<br>";

if ($reserve => $st_time and $reserve =< $end_time)
{
echo "sorry, not available";
}
else
{
echo "ok!";
}

?>

it can already restrict the time, but not yet the day.

it's just a sample so that I will know what to do if i'll transfer it into database.

my problem is this:

I have a table named reserve with 3 columns... timein, timeout, dateres three records have been inputted,

October 15, 2011, 10:30-11:30 October 15, 2011, 1:00-2:30 October 15, 2011, 5:30-8:30

how can I retrieve these records to use it on my code above? instead of these:

$resttimefrom='10:00:00 am';
    $resttimeto='11:00:00 am';
    $reserve='11:01:00 pm';
    $datedat='2012-10-14';

how can I change 10:00:00 am to all records in my database?

I have very limited knowledge about php and mysql... :( please someone help me. please please please

Answer 1

I would suggest you reconsider the way you deal with the duration of reservations. Rather than having a date_reserved simply have time_in, and time_out as datetimes. I would also use the 24hour format instead of 12hr (with AM & PM). So you would have:

$timeInStr  = '2012-10-14 10:00:00';
$timeOutStr = '2012-10-14 11:00:00';

Just for completeness, here is some code to insert data into the table:

$pdo = new PDO(
        "mysql:dbname=stack_overflow;host=127.0.0.1",
        $username,
        $password);

// prepare the insert statement
$statement = $pdo->prepare("
        INSERT INTO `reserve`(`time_in`, `time_out`)
        VALUES (:time_in, :time_out)");

// bind param values
$statement->bindParam(':time_in', $timeInStr, PDO::PARAM_STR);
$statement->bindParam(':time_out', $timeOutStr, PDO::PARAM_STR);

$statement->execute();

In order to determine whether a reservation request would conflict with a previously set reservation, query the database whether the reservation request (equivalent to your $reserve variable) is between the time_in & time_out of any reservation.

In this case, I would count how many previous reservations would overlap with the requested reservation time.

$statement = $pdo->prepare("
    SELECT COUNT(`id`)
    FROM `reserve`
    WHERE :reserve_request_time BETWEEN `time_in`
    AND `time_out`");

$statement->bindParam(
        ':reserve_request_time',
        '2012-10-14 10:30:00',
        PDO::PARAM_STR);

$statement->execute();
$result = $statement->fetch();

// if an overlapping reservation was found "sorry, not available"
if (0 < (int) $result['COUNT(`id`)']) {
    echo "sorry, not available";
}
else {
    echo "ok!";
}