Need help on a specific query

[Twitch]

Hello all,

 

Once again I turn to the experts here for some help.  Been trying various versions of code to get what I want, but no luck.  I'm working on a page I didn't originally create and I want to keep as much of the original code as I can.  So I have this code:

$sql = "select idnum, lname, fname, location from employees
    where 
    temp <> '1' and 
    (
idnum in ($idnumstring) AND (
idnum in (select idnum from CurrentLDAP) or
idnum in (select idnum from shifts 
                  where intime >= '$start' and outtime <= '$stop'))
    )
    and salary = 'hourly'
    order by lname, fname";
$employee = Perform_Select_Query ( $sql, Return_Configuration () );
print $employee[0][error];


$i=0;
foreach ( $employee as $k => $v ) {
    if ( !$employee[$k][idnum] ) {  break; }
    # build array of idnums
    $employees[$i]  = $employee[$k][idnum];
    # hash last and first names by idnum for later use
    $lname[ $employee[$k][idnum] ] = $employee[$k][lname];
    $fname[ $employee[$k][idnum] ] = $employee[$k][fname];
$emplocation[ $employee[$k][idnum] ] = $employee[$k][location];
    $i++;
}

# get list of all offices (sites)
$sql   = "select sitename from sites";
$site = Perform_Select_Query ( $sql, Return_Configuration () );
print $site[0][error];

$i=0;
foreach ( $site as $k => $v  ) {
    if ( !$site[$k][sitename]  ) {  break;  }
    $sites[$i] = $site[$k][sitename];
    $i++;
}


foreach ($employees as $person) {
    # new, much more efficient sql query

    $regular_approved=0;
    $regular_unapproved=0;
    $ot_approved=0;
    $ot_unapproved=0;
    $dot_approved=0;
    $dot_unapproved=0;
    $pto_approved=0;
    $pto_unapproved=0;
    $holiday_approved=0;
    $holiday_unapproved=0;
    $disability_approved=0;
    $disability_unapproved=0;
    $maternity_approved=0;
    $maternity_unapproved=0;
    $juryduty_approved=0;
    $juryduty_unapproved=0;
    $bereavement_approved=0;
    $bereavement_unapproved=0;
$fmla_approved=0;
    $fmla_unapproved=0;
    
    $sql = "select 
	intime, 
	outtime, 
	location, 
	shifttype, 
	approved 
    from shifts
    where 
	idnum = '$person'
	and intime >= '$start'
	and outtime <= '$stop'
	and outtime is not null
	and outtime <> 0";
    $shiftInfo = Perform_Select_Query ( $sql, Return_Configuration () );
    print $shiftInfo[0][error];

 

The code after that just calculates the different shifts/types etc.

What I would like to do is also display some information from the "sites" table in my table that the user sees. The "location" column in the employee table is related to the "sitename" column in the sites table.  So for instance the display data is like so and it works perfectly:

# display data
    $myDataRows .= "<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>BERE</td><td>$bereavement_approved</td></tr>"
              ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DISAB</td><td>$disability_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DOUBLE</td><td>$dot_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>FIXSAL</td><td>$ot_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOLI</td><td>$holiday_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOUR</td><td>$regular_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>JURY</td><td>$juryduty_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>OVER</td><td>$ot_approved</td></tr>"
			  ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>VACN</td><td>$pto_approved</td></tr>";

 

In the displayed data I need "Dept." to actually be the dept column from the sites table based on $emplocation[$person] value. This value is the location column from the employee.

 

Hopefully I explained it correctly cause I think I'm confused...haha

 

I understand if this is too much to ask with all that code, but I thought I'd give it a try before I pull anymore hair out.

 

Thanks in advance,

Twitch

[jbrown84]

It sounds like you need to use "Join" but I could be wrong. This tutorial is probably oversimplified for what you are doing but it helped me to get the basic idea: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[Twitch]

Thanks for the reply, jbrown84.

 

Yea I tried using JOIN on the first query but couldn't get it to work, then I noticed the second sites query and thought maybe I could do something with that but I tried various things and that didn't work either.  JOINS and foreach's still confuse me, but I'm learning haha.

[jbrown84]

Yeah same here, I'm still trying to learn that and just started using Zend so it's hard to go backwards from what I've learned on there. You may have already tried this, but I would create a clone of that page (something like test.php) and try selecting and outputting only that one value that you are trying to get. That usually helps me to isolate the problem

[ngreenwood6]

In mysql you can perform select statements inside of select statements. So you could do something like this:

 

$sql = "select idnum, lname, fname, location from employees, (SELECT something_here FROM sites WHERE location=sites.sitename LIMIT 1) AS something_here
    where 
    temp <> '1' and 
    (



idnum in ($idnumstring) AND (



idnum in (select idnum from CurrentLDAP) or



idnum in (select idnum from shifts 
                  where intime >= '$start' and outtime <= '$stop'))
    )
    and salary = 'hourly'
    order by lname, fname";

 

You will have to replace both of the something_here 's with the value that you actually want. If you notice I also put limit 1 in the query, that is because each one can only return one value. so you will have to do multiple selects to get all the values that you need. A join would be a better choice but if you cant seem to figure it out this will work for you.

[Twitch]

Can't tell you how much I appreciate the replies guys.  Been at this all day.  Getting the dept value from the sites table is the last piece of the puzzle.

Unfortunately, I tried ngreenwood6's idea and that didn't work either.  The page generates an excel file.  It works with the data I had before, but when I add the second select statement, the page quits working.  I've even broken the statement down to the most basic select and it still breaks the page.

 

I know it's probably something simple, I'm just not seeing it at the moment.

 

Thanks again,

Twitch

[Twitch]

I thought I'd give something like this a try:

select employees.idnum, employees.lname, employees.fname, employees.location, employees.prid, sites.siteID, sites.sitename, sites.defaultCode, sites.initials, sites.dept from employees, sites

 

It didn't break it, it just made it so that each entry was doubled or tripled.  Maybe I'm on to something...haha

[jbrown84]

I thought I'd give something like this a try:

select employees.idnum, employees.lname, employees.fname, employees.location, employees.prid, sites.siteID, sites.sitename, sites.defaultCode, sites.initials, sites.dept from employees, sites

 

It didn't break it, it just made it so that each entry was doubled or tripled.  Maybe I'm on to something...haha

 

Did you add "where employees.location = sites.sitename" to the end of that?

[Twitch]

Good catch, jbrown84!  I added it and that did the trick.  I could then get the values I needed from the sites table.

 

select employees.idnum, employees.lname, employees.fname, employees.location, employees.prid, sites.siteID, sites.sitename, sites.defaultCode, sites.initials, sites.dept from employees, sites
    where 
employees.location = sites.sitename and
    temp <> '1' and 
    (
idnum in ($idnumstring) AND (
idnum in (select idnum from CurrentLDAP) or
idnum in (select idnum from shifts 
                  where intime >= '$start' and outtime <= '$stop'))
    )
    and salary = 'hourly'
    order by lname, fname";

 

WHOOO HOOO feels good after working on this all day!

 

Thanks again guys this site has never disappointed me for help.

 

Much gratitude,

Twitch