Solved

MySQL query: Get records thats have records in a all the date range.

Posted on 2012-04-07
9
519 Views
Last Modified: 2012-04-08
Hi,

I've two tables:

Table Items:

id        -             name
----------------------------------
1         -        example 1
2         -        example 2


Table Booking:

id        -        items_id        -         date                 -          status
----------------------------------------------------------------------------------
1         -        1                     -     2012-04-10         -          2
2         -        1                     -     2012-04-11         -          2
3         -        1                     -     2012-04-12         -          2
4         -        1                     -     2012-04-15         -          2
5         -        1                     -     2012-04-19         -          2
6         -        2                     -     2012-04-10         -          2
7         -        2                     -     2012-04-11         -          2
8         -        2                     -     2012-04-12         -          2
9         -        2                     -     2012-04-13         -          2
10       -        2                     -     2012-04-14         -          2


And I've two dates:

From :  2012-04-10
To:        2012-01-14


I need a query that retrieveme the "id" and "name" that has records in all the date range, for example in this case is the id "2"

Thanks!
0
Comment
Question by:nicojmb
  • 5
  • 4
9 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37819573
Use a WHERE clause like this... WHERE `date` BETWEEN '2012-01-14' AND 2012-04-10'

but check your dates in the example.  Is 2012-01-14 what you really want?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37819580
The whole query might go something like this...
SELECT Items.id, Items.name, Booking.items_id, `date`
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND `date` BETWEEN '2012-04-10' AND '2012-04-14'
0
 

Author Comment

by:nicojmb
ID: 37819598
Opps, no the dates are:

From :  2012-04-10
To:        2012-04-14

The  BETWEEN clause can not user because if a record in this range is missing in the table, the result retrieve the other records.

I need that the query check that all days exist in table, in the example the items_id "2" it's ok, but the items "1" no.

Regards!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37819629
I don't get this part... The  BETWEEN clause can not user because if a record in this range is missing in the table, the result retrieve the other records.

If you want only the item_id for "2" this might be the right query.
SELECT Items.id, Items.name, Booking.items_id, `date`
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND Items.id = 2
AND `date` BETWEEN '2012-04-10' AND '2012-04-14'

Open in new window

0
 

Author Comment

by:nicojmb
ID: 37819647
No,

I need all the items thats has records in the date range, but this records must exists in all date range.

For example the the items_id "1" not exist the day "2012-04-13" and "2012-04-14" and the BETWEEN cluase retrive me the id "1" but it's not correct, because not have all days in the range.

And the items_id "2" it's ok.

Now you understandme?

In need to check that has records with the same items_id in all days of the range...

Regards.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37819675
Let me see if I can paraphrase.  The query needs to find only those items that have rows for ALL of the dates in the range.  The problem is that each row is considered individually by the WHERE clause, but you need to consider the rows collectively.  Is that right?  If so, it might be easier to do this with some PHP as well as a query.
0
 

Author Comment

by:nicojmb
ID: 37819691
Yes it's right.

Either way I ok for me.!

Regards!
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 400 total points
ID: 37821190
I do not have any test data so please allow me to use yours.  The lines prior to about #98 are just the setup to use the data base.  The strategy is to create an array of dates.  As we retrieve the rows from the results set we make a copy of the required dates for each new "bid" value, and we remove each date from our copy of array of dates.  If we exhaust the dates, we have a value that appears in all the require dates, so we save that one in the "good" array and continue until the results set is completed.

See http://www.laprbass.com/RAY_temp_nicojmb.php
<?php // RAY_temp_nicojmb.php
error_reporting(E_ALL);

// REQUIRED PHP 5.1+
date_default_timezone_set('America/Chicago');

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// LIVE DATABASE CREDENTIALS
require_once('RAY_live_data.php');

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES

// CREATING A TABLE
$sql
=
" CREATE TEMPORARY TABLE Items
( id   INT         NOT NULL AUTO_INCREMENT
, name VARCHAR(24) NOT NULL DEFAULT ''
, PRIMARY KEY(id)
)
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// LOAD THIS TABLE
MySQL_Query( "INSERT INTO Items (name) VALUES ('example 1')" );
MySQL_Query( "INSERT INTO Items (name) VALUES ('example 2')" );

// CREATING ANOTHER TABLE
$sql
=
" CREATE TEMPORARY TABLE Booking
( id       INT  NOT NULL AUTO_INCREMENT
, items_id INT  NOT NULL DEFAULT 0
, my_date  DATE NOT NULL DEFAULT '0000-00-00'
, status   INT  NOT NULL DEFAULT 2
, PRIMARY KEY(id)
)
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// LOAD THIS TABLE
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-10')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-11')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-12')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-15')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-19')" );

MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-10')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-11')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-12')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-13')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-14')" );


// TWO DATES
$fm = '2012-04-10';
$to = '2012-04-14';

// CREATE A RANGE OF DATES WITH KEY AND VALUE EQUAL
$d = array();
$a = $fm;
$z = $to;
while ($a <= $z)
{
    $x = date('Y-m-d', strtotime($a));
    $d[$x] = $x;
    $a     = date('Y-m-d', strtotime($a . ' +1 DAY'));
}


// A QUERY TO SELECT THINGS BY DATE
$sql
=
"
SELECT Items.id, Items.name, Booking.items_id AS bid, my_date
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND my_date BETWEEN '$fm' AND '$to'
ORDER BY bid, my_date
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE RESULTS SET TO SEE WHAT WE ARE WORKING WITH
while ($row = mysql_fetch_assoc($res))
{
    print_r($row);
    echo "<br/>" . PHP_EOL;
}

// RESET THE RESULTS
if (!mysql_num_rows($res)) die('NOTHING');
mysql_data_seek($res, 0);

// SINCE WE ORDERED BY bid WE WILL USE THE ITERATOR TO SEE WHICH DATES ARE COVERED
$good = array();
$old  = '?';
while ($row = mysql_fetch_assoc($res))
{
    // INITIAILIZE FOR THIS bid
    if ($row['bid'] != $old)
    {
        // GET A FRESH COPY OF THE ARRAY OF DATES
        $now = $d;
        $old = $row['bid'];
    }

    // REMOVE THIS DATE FROM THE ARRAY OF DATES
    unset($now[$row['my_date']]);

    // IF ALL DATES ARE COVERED THE ARRAY WILL GO TO EMPTY
    if (empty($now)) $good[] = $row['name'];
}

// SHOW WHICH DATA ELEMENTS PASSED THE TEST
echo "<pre>";
var_dump($good);

Open in new window

0
 

Author Closing Comment

by:nicojmb
ID: 37821680
That's works great.

Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

789 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