Solved

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

Posted on 2012-04-07
9
499 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:nicojmb
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
Yes it's right.

Either way I ok for me.!

Regards!
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 400 total points
Comment Utility
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
Comment Utility
That's works great.

Thanks!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

763 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

12 Experts available now in Live!

Get 1:1 Help Now