Solved

PHP Compare from 2 tables and show data missing

Posted on 2014-02-03
18
220 Views
Last Modified: 2014-02-26
Need help with MYSQL statement that will show me what days of the week George, Tim, etc is not scheduled to work if table 1 has his current schedule and table 2 has the possible days for the schedule. I would then want a list that shows me who is available.

Table 1 has 2 fields

George | Monday
George | Tuesday
Tim | Tuesday
Tim | Thursday
Tim | Friday

Table 2 has 1 field

Monday
Tuesday
Wednesday
Thursday
Friday

Results shows me what days each employee is not scheduled.

George | Wednesday
George | Thursday
George | Friday
Tim| Monday
Tim | Wednesday

Tried:

SELECT DISTINCT employee_id, scheduled_day, week_days
FROM table_2 LEFT JOIN table_1 ON week_days = scheduled_day
WHERE scheduled_day IS NULL

Shows no results. Help please.
0
Comment
Question by:smtpuser2
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829189
what about this
SELECT employee_id, scheduled_day, week_days
FROM table_2 d
JOIN ( select t1.employee_id friom table_1 t1 group by t1.employee_id ) e
  ON 1 = 1
LEFT JOIN table_1 es
   ON es.scheduled_day = d.week_days 
 AND es.employee_id = e.employee_id
WHERE es.scheduled_day IS NULL 

Open in new window

0
 

Author Comment

by:smtpuser2
ID: 39829204
on line 2 what is d after table_2?
on line 3 what is e at the end?
on line 5-8 what is es?

Also, I specified Table_2 only had 1 field and you have 3 in your solution.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829212
the letters after the table names are aliases:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

let me update the query code slightly, I omitted the usage of the aliases for the first line:
SELECT e.employee_id, d.week_days  , es.scheduled_day
FROM table_2 d
JOIN ( select t1.employee_id friom table_1 t1 group by t1.employee_id ) e
  ON 1 = 1
LEFT JOIN table_1 es
   ON es.scheduled_day = d.week_days 
 AND es.employee_id = e.employee_id
WHERE es.scheduled_day IS NULL 

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39829242
Is this a homework assignment?  I don't know anyone who has a table to tell us the days of the week -- that's usually already a known fact.  But that aside, I'll see if I can set up a test case for you.
0
 

Author Comment

by:smtpuser2
ID: 39829250
It just times out for me. Not working.
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39829323
SELECT DISTINCT employee_id, scheduled_day, week_days
FROM table_2 INNER JOIN table_1 ON week_days != scheduled_day
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829356
>It just times out for me. Not working.

then I presume the table(s) are BIG, and not just those couple of records,

you will need indexes on the employe_id  and on the scheduleddays field(s).

please post the explain plan of the query
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39829527
use this query

SELECT distinct sch,table1.employee_id,t.week_days FROM (`table2` inner join table1) left join table1 as t on (table1.employee_id=t.employee_id and sch=t.week_days) where ifnull(t.week_days,'')=''

i assumed table like this

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(255) NOT NULL,
  `week_days` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(1, 'George', 'Monday');
INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(2, 'George', 'Tuesday');
INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(3, 'Tim', 'Tuesday');

-- --------------------------------------------------------

--
-- Table structure for table `table2`
--

CREATE TABLE IF NOT EXISTS `table2` (
  `sch` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table2`
--

INSERT INTO `table2` (`sch`) VALUES('Monday');
INSERT INTO `table2` (`sch`) VALUES('Tuesday');
INSERT INTO `table2` (`sch`) VALUES('Wednesday');
INSERT INTO `table2` (`sch`) VALUES('Thursday');
INSERT INTO `table2` (`sch`) VALUES('Friday');
0
 

Author Comment

by:smtpuser2
ID: 39829626
Not working imakepossible. Is the syntax correct? Says there is an error. None seem to work.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39830169
Please see http://www.laprbass.com/RAY_temp_smtpuser.php

This uses a bit of PHP code to effect the output.  I found that easier and faster than trying to figure out how to do it all in a single query.  Just add your credentials near line 38-41 and try it out.

<?php // RAY_temp_smtpuser.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28354857.html


/* PROBLEM DEFINITION
Table 1 has 2 fields

George | Monday
George | Tuesday
Tim | Tuesday
Tim | Thursday
Tim | Friday

Table 2 has 1 field

Monday
Tuesday
Wednesday
Thursday
Friday

Results shows me what days each employee is not scheduled.

George | Wednesday
George | Thursday
George | Friday
Tim | Monday
Tim | Wednesday
*/


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

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING THE TABLES FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE table_1
( id      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, manname VARCHAR(24) NOT NULL DEFAULT ''
, weekday VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING THE TABLES FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE table_2
( id      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, thedays VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('George', 'Monday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('George', 'Tuesday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Tuesday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Thursday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Friday')");

$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Monday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Tuesday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Wednesday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Thursday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Friday')");

/* CHECK THE TEST DATA IS OK?  YES.
$res = $mysqli->query("SELECT * FROM table_1");
while ($row = $res->fetch_object()) print_r($row);
$res = $mysqli->query("SELECT * FROM table_2");
while ($row = $res->fetch_object()) print_r($row);
*/

// GET AN ARRAY OF THE DAYS IN BOTH KEYS AND VALUES
$sql = "SELECT thedays FROM table_2";
$res = $mysqli->query($sql);
while ($row = $res->fetch_object()) $days[$row->thedays] = $row->thedays;

// GET AN ARRAY OF THE PEOPLE
$sql = "SELECT manname, weekday FROM table_1 ORDER BY manname";
$res = $mysqli->query($sql);
while ($row = $res->fetch_object()) $mans[] = $row;

// COMBINE THE DATA SETS TO GET THE DAYS EACH MAN IS AVAILABLE
$oldname = FALSE;
$my_days = array();
foreach ($mans as $row)
{
    // START A NEW DATA SET FOR EACH NEW NAME
    if ($row->manname != $oldname)
    {
        // SAVE THE DAYS THAT ARE LEFT
        $out[$oldname] = $my_days;

        // START WITH A NEW NAME AND A NEW SET OF WEEKDAYS
        $oldname = $row->manname;
        $my_days = $days;
    }

    // REMOVE THIS DAY FROM CONSIDERATION
    unset($my_days[$row->weekday]);
}
$out[$oldname] = $my_days;
unset($out[0]);

// SHOW THE DATA
print_r($out);

Open in new window

0
 

Author Comment

by:smtpuser2
ID: 39831268
I would love to accept Ray's solution but alas it is way too complicated. Looking for a MySQL query statement. This has to be a common problem.
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39831441
SELECT distinct sch,table1.employee_id,t.week_days FROM (`table2` inner join table1) left join table1 as t on (table1.employee_id=t.employee_id and sch=t.week_days) where ifnull(t.week_days,'')=''


table1  having field
employee_id,week_days


table2 having field
sch


After creating your query with this
can you give your query, i think you are missing something

it is working as i tested at my end
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39832025
...but alas it is way too complicated
Really!  Too complicated for who?  I don't see any other solutions that work :-)

Actually, in the world of computer programming tasks, it's just drop-dead simple CS101 stuff.  The code has comments explaining what it does.  It was the first working solution.  The majority of what's in the code snippet is just there to set up the test data, which is the most important thing a programmer can have.  The only way to make it harder is to try to do it all with one query.  So the question becomes, "do you want immediate results or do you want to spend your time in a struggle with a problem?"

Sometimes you may find that people will post code here at EE without testing it, or they will tell you they tested it but will not be able to show you the test case.  Since I do this stuff for a living and teach it at the university level, I find that testing is a necessary part of the process so I do it.  It is the only way you can hope to know if the code works!  In exchange for tested code samples you must provide the EE community with the SSCCE and a well-representative data set so that we can test.

I did what I could with the data provided, and the output is what you asked for.

Best of luck with your project, ~Ray
0
 

Accepted Solution

by:
smtpuser2 earned 0 total points
ID: 39840055
Sorry Ray. I really did not intend to dis your effort. I appreciate that you took the time to thoroughly explain your solution. You actually earned a gold star for your effort. As a beginning programmer, I had not used a similar technique and it looked foreign to me. Again thanks.

I was able to find another solution through a different website. Not sure if it is efficient because it seems to not load quickly. I have enclosed it below in case I should be pointed to a more efficient solution.

SELECT *
FROM (SELECT id, week_days FROM table_1, table_2
GROUP BY id,week_days) as tab  WHERE row(id,week_days) NOT IN(select id,scheduled_day from table_1)
ORDER BY id, week_days

Many thanks to all the contributors.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39840152
first, another good tip: use aliases in your queries:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

other than that, I claim my SQL is correct approach, but you did not yet come back with the answers to my question ...
0
 

Author Comment

by:smtpuser2
ID: 39840505
No homework. Creating a solution for my job.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39844191
Sigh...

Here are the sorts of things that make it hard to help (both quotes from the original question)
Table 2 has 1 field
SELECT DISTINCT employee_id, scheduled_day, week_days FROM table_2

When I tried to test the query posted here, this was the result:
Fatal error:  QUERY FAILURE: ERRNO: 1137 ERROR: Can't reopen table: 'table_1'
0
 

Author Closing Comment

by:smtpuser2
ID: 39888276
I posted the question as a usable MYSQL statement.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

746 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