Solved

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

Posted on 2012-04-07
9
521 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 110

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 110

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 110

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 110

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 110

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 40
Add a loading gif while php runs server side 15 67
What is this? I've never seen this... 2 29
if statement malfunction 5 23
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

726 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