Printing results of two queries using multi-dimensional arrays in table format

[sunshine66]

How should I update this code to properly sort the combined results by Lastname? Currently, it prints all sorted employee profiles then all sorted dependent profiles.

// Perform queries to get profiles for resulting company
$query2 = "SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes' 
    ORDER BY EmployeeID"; 
$result2 = mysql_query($query2) or die("Query 2 error : " . mysql_error() );  

$query3 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes' 
    ORDER BY EmployeeID"; 
$result3 = mysql_query($query3) or die("Query 3 error : " . mysql_error() );   

// Put Dependent results in table format
while($line = mysql_fetch_array($result2)) 
{ 
    $ID      = $line["DependentID"]; 
    $type    = $line["DependentType"]; 
    $last     = $line["DependentLast"]; 
    $first     = $line["DependentFirst"];     

$tableinfo .= "<tr bgcolor=$color> 
<td nowrap>$type: $ID</td>     
<td nowrap>$last, $first</td>"; 

// if COBRA = YES, show empty cell
   if ($COBRA=="Yes") { 
	 $tableinfo .= "<td>&nbsp;</td>\n";
   }
// if COBRA = No, show links to forms
   if ($COBRA=="No") { 
	 $tableinfo .= "<td nowrap>
	<A HREF=\"f-A2.php?id=$ID\">Doc A</A> | <A HREF=\"f-H.php\">Doc H</A><br>
	 <A HREF=\"f-C.php?id=$ID\">Doc C</A> | <A HREF=\"f-C1.php?id=$ID\">Doc C1</A>  ";

    // If dependent is a minor include links to Doc D
    if  ( $type=="Minor")
    {
 	$tableinfo .= "<BR>
 	<A HREF=\"f-D.php?id=$ID\">Doc D</A> | <A HREF=\"f-C1.php?id=$ID\">Doc D1</A>";
    }

$tableinfo .= "</td>\n"; 
  }
	$tableinfo .= "</tr>\n"; 
} 

// Put Employee results in table format
while($line = mysql_fetch_array($result3)) 
{ 
    $type = "Employee"
    $ID      = $line["EmployeeID"]; 
    $last     = $line["EmployeeLast"]; 
    $first     = $line["EmployeeFirst"];    

    $taa 	 = $line["TAA"];	
    $COBRA = $line["COBRA"];	


$tableinfo .= "<tr> 
<td nowrap>$type, $ID</td>     
<td nowrap>$last, $first</td>"; 

if ($COBRA=="No") { 
 $tableinfo .= "<td nowrap class=\"small\" align=\"left\">
<A HREF=\"f-A.php?id=$ID\">Doc A</A> | 
<A HREF=\"f-H.php\">Doc H</A><br>
<A HREF=\"f-B.php?id=$ID\">Doc B</A> | <A HREF=\"f-B1.php?id=$ID\">Doc B1</A>\n";

 //if employee TAA = yes, show Doc J link
if ($taa=="Yes")
{ 
 $tableinfo .= "| <A HREF=\"f-J.php\">Doc J</A>";
}
$tableinfo .= "</td>\n";
   }
// if employee COBRA = YES, show empty cell
   if ($COBRA=="Yes") { 
	 $tableinfo .= "<td>&nbsp;</td>\n";
   }

$tableinfo .= "</tr>\n"; 
} 

// print results in table
print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\"> 
       <tr> 
      <th align=\"left\" valign=\"bottom\">Type & ID </th> 
      <th align=\"left\" valign=\"bottom\">Name</th>  

      <th align=\"left\" valign=\"bottom\">Forms</th>  

      </tr>"; 

print "$tableinfo"; 
print "</table>"; 
print "<br>";

I was able to successfully sort both results in a SELECT box, but I'm confused how to work with multi-dimensional arrays to print results in a table; and in combination with the IF statements (to print certain info depending on $type). REF:

$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
$result = mysql_query($query) or die("Query error: " . mysql_error() ); 

$row = mysql_fetch_array($result);//Get ID values
     if($row) {
	$CoID = $row["CompanyID"];
	}

$query4 = "SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes' 
      ORDER BY DependentLast,DependentFirst";  

$result4 = mysql_query($query4) or die("Error with query 4 : " . mysql_error() ); 

$query8 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes' 
      ORDER BY EmployeeLast,EmployeeFirst"; 
$result8 = mysql_query($query8) or die("Error with query 8 : " . mysql_error() );   

$name_8 = array(); 
while($row8 = mysql_fetch_array($result8))    

{    

    $name_8[$row8["EmployeeID"]] = $row8["EmployeeLast"].", ".$row8["EmployeeFirst"]; 
}      

$name_4 = array(); 
while($row4 = mysql_fetch_array($result4))    

{    

    $name_4[$row4["DependentID"]] = $row4["DependentLast"].", ".$row4["DependentFirst"]; 
}  

$combined = array_merge($name_8,$name_4); 
asort($combined); 


print "<select>";
while (list($key, $value) = each($combined)) {
echo "<OPTION VALUE=$key>$value</OPTION>\n";
}

print "</select>";

3 Database tables, each with unique IDs; CompanyID common to all three.

[trex005]

step one : Get rid of "SELECT *"
that is wonderful SQL if you are looking at a table.. but is a nightmare when you have HUGE joins...
step two : use a union

$sql = "
(
     SELECT
     DependantID as id,
     DependentType as type,
     DependentLast as last,
     DependentFirst as first
     FROM
     dependentProfile
     WHERE
     CompanyID='$CoID'
     AND
     COBRA='Yes'
)
UNION ALL
(
     SELECT
     EmployeeID as id,
     'Employee' as type,
     EmployeeLast as last,
     EmployeeFirst as first
     FROM
     employeeProfile
     WHERE
     CompanyID='$CoID'
     AND
     COBRA='Yes'
)
ORDER BY last
";

[sunshine66]

Hi,
This is great! I've been trying to figure out what a simpler way is...glad to learn about UNION ALL. You set it up in a way that makes it easy for me to understand how to set it up.

I used * because the table will have much more information, but I like your method even better.

I don't mean to be a pain, but I'm just learning. I could really use your help...

When I update my code, I get "Query 2 error: You have an error in your SQL syntax near '( SELECT DependentID as id, DependentType as type, DependentLast as last, Depend' at line 1" Note: I updated a typo in DependantID and double checked the spelling of table field names.

Here's the updated code:

//.... continued from first query to get CoID value

// Find employee profiles and dependent profiles for CoID and COBRA=yes
$sql = " 
( 
     SELECT 
     DependentID as id, 
     DependentType as type, 
     DependentLast as last, 
     DependentFirst as first 
     FROM 
     dependentProfile 
     WHERE 
     CompanyID='$CoID' 
     AND 
     COBRA='Yes' 
) 
UNION ALL 
( 
     SELECT 
     EmployeeID as id, 
     'Employee' as type, 
     EmployeeLast as last, 
     EmployeeFirst as first 
     FROM 
     employeeProfile 
     WHERE 
     CompanyID='$CoID' 
     AND 
     COBRA='Yes' 
) 
ORDER BY last 
";

$result2 = mysql_query($sql) or die("Query 2 error: " . mysql_error() );
while($line = mysql_fetch_array($result2))
// continue code ...

[trex005]

hmmm I fear that might mean you have mysql 3.X (Unions were introduced in 4)
This page might help :
http://jinxidoru.com/tutorials/union.html
basically this talks about a work around for older versions of Mysql.... though if this doesn't help... Just reply and I can help you with another workaround in the morning when I wake up!

G'night!

[sunshine66]

Well that's bad news for me. I've got MySQL 3.23.53
Thanks for the link. It's a start, but still confusing for me.
I get the concept of creating a third table, but the code is still a bit foggy. Here is the example provided in the tutorial with my comments of my understanding of how to do it...and where I have questions:

/* SELECT IFNULL (main_table1.fieldname1, main_table2.fieldname1 AS variable1) 
??? Why use IFNULL here???
??? for more than one variable, would I repeat like this???
??? how about assigning the word 'Employee' to create the Employee Type field ???
SELECT 
IFNULL(employeeProfile.EmployeeID, dependentProfile.DependentID) AS id
IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first
IFNULL(employeeProfile.EmployeeLast, dependentProfile.DependentLast) AS last
IFNULL('Employee',dependentProfile.DependentType) AS type
etc.
*/
SELECT IFNULL(table1.id, table2.id) AS id

/*FROM dummy_table (example: employeeProfile) AS ??? 
??? What does D represent???? */
FROM _dummy AS D

/*I don't understand JOIN condition syntax...even after reading MYSQL reference manual 
??? Where does the reference to the dependentProfile come into play ???
LEFT JOIN employeeProfile ON (????????)
LEFT JOIN dependentProfile ON (???????)
*/
LEFT JOIN table1 ON (D.num = 0)
LEFT JOIN table2 ON (D.num = 1)

/* I don't understand these conditions...except the IS NOT NULL part
??? Would my conditions be: 
WHERE CompanyID='$CoID' AND COBRA='Yes' IS NOT NULL   */
WHERE D.num < 2 AND IFNULL(table1.id,table2.id) 
IS NOT NULL

/* ORDER BY field_name_of_choice ... taken from AS variables in first part of SELECT statement 
Mine would be:
ORDER BY last, first   */
ORDER BY id

I REALLY appreciate your offer to help! THANK YOU!!
You don't know how happy it would make me to figure this out...I've been working on it for weeks! I guess that comes with being a newbie

[trex005]

/* SELECT IFNULL (main_table1.fieldname1, main_table2.fieldname1 AS variable1)
??? Why use IFNULL here??? ANSWER: if the field in the first table is NULL (and set as not null) you can assume that is not the tablew you are looking for
??? for more than one variable, would I repeat like this??? ANSWER: YES! :)
??? how about assigning the word 'Employee' to create the Employee Type field ??? ANSWER : if type does not exist in employeeProfile, simply check dependentProfile for the NULL (see below)
SELECT
IFNULL(employeeProfile.EmployeeID, dependentProfile.DependentID) AS id
IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first
IFNULL(employeeProfile.EmployeeLast, dependentProfile.DependentLast) AS last
IFNULL(dependentProfile.DependentType,'Employee') AS type
etc.
*/
SELECT IFNULL(table1.id, table2.id) AS id

/*FROM dummy_table (example: employeeProfile) AS ???
??? What does D represent????
ANSWER : D is actually another table you will need in your database... it's pourpouse is only to have something to join both the other tables on and force one or the other to be null*/
FROM _dummy AS D

/*I don't understand JOIN condition syntax...even after reading MYSQL reference manual
??? Where does the reference to the dependentProfile come into play ???
LEFT JOIN employeeProfile ON (????????) 
LEFT JOIN dependentProfile ON (???????)
ANSWER: using the dummy table, your conditions would be the same as below
*/
LEFT JOIN table1 ON (D.num = 0)
LEFT JOIN table2 ON (D.num = 1)

/* I don't understand these conditions...except the IS NOT NULL part
??? Would my conditions be:
WHERE CompanyID='$CoID' AND COBRA='Yes' IS NOT NULL
you will need this full condition in your WHERE what this does is throw out all the extra rows created by the join
though you will need to add your own conditions and you can either do that in the join 
(D.num = 0 && your conditions go here)
or do something a bit more fancy in the where (I'd probably do it in the join even though it is a little less proper)*/
WHERE D.num < 2 AND IFNULL(table1.id,table2.id) 
IS NOT NULL

/* ORDER BY field_name_of_choice ... taken from AS variables in first part of SELECT statement
Mine would be:
ORDER BY last, first
ANSWER : Bingo!*/
ORDER BY id

[sunshine66]

Okay, still having problems...
"Query 2 error: You have an error in your SQL syntax near 'IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first ' at line 3"

I created 2 additional tables in database (_dummy and Dump), both with 1 field called 'num' (varchar, 50 characters)

/* 
/// SELECT IFNULL (main_table1.fieldname1, main_table2.fieldname1 AS variable1) 
/// if type does not exist in employeeProfile, simply check dependentProfile for the NULL (see below) 
/// _dummy and Dump are tables with 1 field called "num" for the purpose of temporary usage to combine 2 other tables
*/ 

$sql ="
SELECT 
IFNULL(employeeProfile.EmployeeID, dependentProfile.DependentID) AS id 
IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first 
IFNULL(employeeProfile.EmployeeLast, dependentProfile.DependentLast) AS last 
IFNULL(dependentProfile.DependentType,'Employee') AS type 

FROM _dummy AS Dump

LEFT JOIN employeeProfile ON (Dump.num = 0 && CompanyID='$CoID' && COBRA='Yes') 
LEFT JOIN dependentProfile ON (Dump.num = 1 && CompanyID='$CoID' && COBRA='Yes') 

WHERE Dump.num < 2 AND IFNULL(employeeProfile.id,dependentProfile.id) IS NOT NULL 
ORDER BY last, first ";

$result2 = mysql_query($sql) or die("Query 2 error: " . mysql_error() );

Please help me figure out what I'm doing wrong! If you could update code and add comments as applicable, I would be VERY greatful!

[tsinka]

Hi,

I think there's a , missing after each IFNULL part. Besides that I'm not sure if && inside the SQL query works. I never used that and can't test it now so I can't say anything about using && in a SQL query. But I wonder if that works ....

$sql =" 
SELECT  

IFNULL(employeeProfile.EmployeeID, dependentProfile.DependentID) AS id,  

IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first,  

IFNULL(employeeProfile.EmployeeLast, dependentProfile.DependentLast) AS last,  

IFNULL(dependentProfile.DependentType,'Employee') AS type  

FROM _dummy AS Dump 

LEFT JOIN employeeProfile ON (Dump.num = 0 && CompanyID='$CoID' && COBRA='Yes') 
LEFT JOIN dependentProfile ON (Dump.num = 1 && CompanyID='$CoID' && COBRA='Yes') 

WHERE Dump.num < 2 AND IFNULL(employeeProfile.id,dependentProfile.id) IS NOT NULL  

ORDER BY last, first "; 

$result2 = mysql_query($sql) or die("Query 2 error: " . mysql_error() );

Thomas

[trex005]

Sorry, My bad on the && I just work in so many different languages I sometimes write without thinking... And yes, you need those commas WTG tsinka!

$sql =" 
SELECT  

IFNULL(employeeProfile.EmployeeID, dependentProfile.DependentID) AS id,  

IFNULL(employeeProfile.EmployeeFirst, dependentProfile.DependentFirst) AS first,  

IFNULL(employeeProfile.EmployeeLast, dependentProfile.DependentLast) AS last,  

IFNULL(dependentProfile.DependentType,'Employee') AS type  

FROM _dummy AS Dump 

LEFT JOIN employeeProfile ON (Dump.num = 0 AND CompanyID='$CoID' AND COBRA='Yes')
LEFT JOIN dependentProfile ON (Dump.num = 1 AND CompanyID='$CoID'AND COBRA='Yes')

WHERE Dump.num < 2 AND IFNULL(employeeProfile.id,dependentProfile.id) IS NOT NULL  

ORDER BY last, first ";

it has beena ton of fun helping you get this far!