根据开始日期和结束日期PHP从DB中排除结果

Question

Array ( [0] => Array ( 
                   [reservationid] => KHAN2016Q221
                   [startdate] => 2016-05-16 
                   [enddate] => 2016-05-21 
                     ) 
        [1] => Array ( 
                   [reservationid] => KHAN2016Q222
                   [startdate] => 2016-05-18 
                   [enddate] => 2016-05-23 
                     ) 
        [2] => Array (
                   [reservationid] => KHAN2016Q223 
                   [startdate] => 2016-05-19
                   [enddate] => 2016-05-21 
                     ) 
        [3] => Array ( [reservationid] => KHAN2016Q224 
                   [startdate] => 2016-05-23 
                   [enddate] => 2016-05-27 
                     ) 
     )

I shall provide a startdate and enddate and I want to return reservationid which exclude the dates on which reservation has been made. Kindly help me please.

Answer 1

Hi,

I guess, it depends on the date format in your database, which you are using. I did some things with AMADEUS API (Travelport) reservation tasks. Normally in very simple form like that:

SELECT * FROM xxxxxxx WHERE :MyDate BETWEEN StartDate AND EndDate

And depending on the database you have to add that too:

AND EndDate >= StartDate

But you have to check the date format and encoding of your database for the string.

Perhaps the answer is too simple but as you know it depends on many system specific parameters, which you did not list yet.

OK - I read your amendment 1 I did not work with CodeIgniter yet. But first of all - what database are you using? OK I see in CodeIgniter - it is MySQL and uses normally UTF-8 - but look inside the config if it set in general like so:

$db['default'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => 'xxxxx',
        'password' => 'xxxxx',
        'database' => 'xxxxx',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => TRUE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array()
);

Look into the database and look what field type is assigned to startdate and enddate. I guess that it is DATETIME for reservation purpose you need it up to seconds. I guess to get the correspondings IDs we need some JOIN which we can find in your Query Builder Class of CodeIgniter. We'll see then further on. I just saw the tag android - is this for android?. This might be of importance. Naturally I don't know your code - but I guess that you make some query like that and, following your description, the result does not provide the reservationID? In your database model isn't there set a primary or foreign key for reservationID? Could you lookup that? You could make something like this to get all reservations except the interval indicated - as I said - I don't know your database layout and scheme etc.

 $this->db->select('*');
    $this->db->from('reservations');
    $this->db->where('startdate <=', $startdate);
    $this->db->where('enddate >=', $enddate);
    $query = $this->db->get();

But I don't understand what you want exactly. Why don't you get no reservationIDs in the result of the query?

Best regards

Axel Arnold Bangert - Herzogenrath 2016