How to do MySQL Subqueries?

[sunshine66]

Hi,
I have multiple tables in a database that I need to query.
Below is the method I having been using (run separate SELECT queries). How do I simplify so that I only need to do 1 query?

Help is appreciated.

<?php

session_start();

if ($_SESSION['valid_log'][0] != $_SERVER['REMOTE_ADDR'] || 
$_SESSION['valid_log'][1] != $_SESSION['User'])
{

//redirect to error page
echo '<META HTTP-EQUIV="Refresh" Content="1; URL=/error_access.html">';
exit();
}


$link = mysql_connect("localhost", "root", "password") or die("Could not connect. Please try again later.");
mysql_select_db("database",$link) or die("Could not select database. Please try again later.");

// Get Company ID for user logged into system
	$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
	$result = mysql_query($query) or die("Error with query.");	
	$row = mysql_fetch_array($result);

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

// Get list of all employees based on Company ID 
	$query2 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID ORDER BY EmployeeLast,EmployeeFirst";
	$result2 = @mysql_query($query2) or die("Error with query 2.");

?>

[paulnaj]

It's hard to have to work out what you want to achieve just by looking at your code.

It might be easier to help you if you post up what your desired final result looks like.

e.g

field1 | field2 | field3 |

value | value | value |

The table defn's might help as well.

Paul

[sunshine66]

oops...
okay here's the Db table structure:

---

userProfile

ProfileID
CompanyID
CompanyName
UserName
UserPassword
FirstName
LastName
LoginType

(note: this is the list of registered users; some companies have more than 1)

---

companyProfile

CompanyID
CompanyName
CompanyStreet
CompanyCity
CompanyState
CompanyZip
ContactFirst
ContactLast
ContactPhone

---

employeeProfile

EmployeeID
CompanyID
EmployeeFirst
EmployeeLast
MaritalStatus
SpouseFirst
SpouseLast
EmployeeStreet
EmployeeCity
EmployeeState
EmployeeZip

and here's the output:

Case 1: show the list of employees for the company of the user who is logged into the system. The user can select actions for an employee such as edit, delete, print documents, or create associated profiles like a child's profile or a health plan profile.

Employee Name | Address | City | State | Zip | Marital Status | Spouse's Name | Dependents | Action

Case 2:
a) When the user selects the Print Documents action from the employee list, go to a page that displays the employee's profile and the company profile (or just parts of either) in an HTML form as well as any other information that will be needed for the document, but that won't be stored in the database.

Employee Name: <form field with value from database>
Marital Status : <form field with value from database>
Address: <form field with value from database>
etc.
Date of Letter <form field>

b) Allow the user to edit any information that has changed or add additional information required for form.

c) Upon form submission, update the database and display values in the document.

Document Title for <CompanyName>
Date: <LetterDate>
To: <Employee Name> (if applicable: and < Spouse Name>
<EmployeeAddress>
etc.

Does this help?

[sunshine66]

Also, for Case 1 (the employee list) I tried to simplify the query by eliminating the companyProfile table since the CompanyID is in all three tables.

So the query looks like this:

    $query2 = "SELECT * FROM employeeProfile,userProfiles 
    WHERE userProfiles.CompanyID=employeeProfile.CompanyID 
    ORDER BY EmployeeLast,EmployeeFirst"; 
    $result2 = mysql_query($query2);

but when the list prints out, it displays each employee twice!
Can someone explain what I'm doing wrong?

here's the code for the display:

while($line = mysql_fetch_array($result2))
{
	$EEid      = $line["EmployeeID"];
	$last	 = $line["EmployeeLast"];
	$first     = $line["EmployeeFirst"];	
	$city      = $line["EmployeeCity"];	
	$state     = $line["EmployeeState"];	
	$spouselast = $line["SpouseLast"];
	$spousefirst    = $line["SpouseFirst"];	

    if( $c == 0 ){
            $color = "dddddd";
            $c = 1;
    }else{
            $color = "white";
            $c = 0;
    }


//If employee plan type = single
	if ($htype=="Single")
	{	
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$EEid</td>	
	<td nowrap>$last, $first</td>
	<td nowrap>$city, $state</td>
	<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
	<td nowrap>
	<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" 
	ALT=\"Edit Employee Profile\"></A></td>
                <td nowrap class=\"small\">
	<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" 
	ALT=\"Delete Employee and Dependent Profiles\"></A>
	</td>
                <td nowrap class=\"small\">n/a</td><td nowrap class=\"small\">n/a</td>
	</tr>\n";
	}

//If employee plan type = married
elseif ( (($htype=="Married") && ($spousefirst!="n/a")) || (($dtype=="Married") && ($spousefirst!="n/a")) )
{
$tableinfo .= "<tr bgcolor=$color>
<td nowrap>$EEid</td>	
<td nowrap>$last, $first</td>
<td nowrap>$city, $state</td>
<td nowrap class=\"small\"><A HREF=\"employee_forms.php?id=$EEid\">Forms</A></td>
<td nowrap>
<A HREF=\"employees_edit.php?id=$EEid\"><IMG SRC=\"/admin/images/b-edit.gif\" BORDER=\"0\" ALT=\"Edit Employee Profile\"></A></td>
<td nowrap class=\"small\">
<A HREF=\"employees_del.php?id=$EEid\"><IMG SRC=\"/admin/images/b-delete.gif\" BORDER=\"0\" ALT=\"Delete Employee and Spouse Profiles\"></A>
</td>
<td nowrap class=\"small\">$spousefirst</td>
<td class=\"small\">n/a</td>
</tr>\n";
}

}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
         <tr>
	  <th align=\"left\" valign=\"bottom\">EE<br>ID</th>
	  <th align=\"left\" valign=\"bottom\">Name</th>
	  <th nowrap align=\"left\" valign=\"bottom\">City</th>
	  <th valign=\"bottom\">&nbsp;</th>		
	  <th colspan='2' valign=\"bottom\">Employee</th>
	  <th valign=\"bottom\">Spouse</th>    

	  <th valign=\"bottom\">Dependents</th>
          </tr>";


print "$tableinfo";
print "</table>";
print "<br>";
print "<center><table>";
print "<center>$nav</center>\n";
print "</table></center>";

[paulnaj]

Hi Tracey,

That helps a lot.

Before I try and answer though, there is one thing that confuses me slightly ...

You want to pick out all employees who work for the current user's company, but I don't see any reference to the current user in the first query ...

 $query = "
 SELECT * 
 FROM userProfiles,companyProfile 
 WHERE userProfiles.CompanyID=companyProfile.CompanyID
"; 

... which gives you a full listing of all users and their companies.

Surely it should be something like ...

 $query = "
 SELECT * 
 FROM userProfiles, companyProfile 
 WHERE userProfiles.CompanyID=companyProfile.CompanyID
 AND userProfiles.ProfileID = ".$_SESSION['User']."
"; 

... or something similar, so that the current user can be picked out and only one row will be returned by the query.

Now, if that IS the case, then to pick out the employee data at the same time, you could do ...

 $query = "
 SELECT * 
 FROM userProfiles, companyProfile, employeeProfile 
 WHERE userProfiles.CompanyID=companyProfile.CompanyID
 AND userProfiles.CompanyID=employeeProfile.CompanyID
 AND userProfiles.ProfileID = ".$_SESSION['User']."
 ORDER BY employeeProfile.EmployeeLast, employeeProfile.EmployeeFirst
"; 

You could of course pare down the 'SELECT *' if you want to be more concise

Come back if I've missed something or not understood the first query.

Paul.

EDIT: I spelled your name wrong!

[sunshine66]

Thanks Paul.

I just started learning PHP & mySQL, so plese forgive my ignorance. I'm not exactly sure how sessions work, so I sometimes have a hard time explaining what I'm doing.

When the user logs into the system, I create an initial Session that stores the ProfileID.

	// connect to database
	$link = mysql_connect("localhost", "root", "password") or die("Could not connect. Please try again later.");
	mysql_select_db("database",$link) or die("Could not select database. Please try again later.");

//lookup record
/* Perform SQL query : select the userProfile that matches user name and password */

$sql= "SELECT * FROM userProfiles WHERE UserName='$UserName' AND UserPassword='$UserPassword'";

//* return the "result set" (i.e. rows or entries) resulting from the query or return error. */

$result = mysql_query($sql) or die("Error in query result");
	$row = mysql_fetch_array($result);


if($row) {

//Get values for User 
$type = $row["LoginType"];
$User = $row["UserName"];
$FirstName = $row["FirstName"];
$LastName = $row["LastName"];
$id = $row["ProfileID"];
$val="YES";

// start session

session_start(); 
$_SESSION['User'] = $User;
$_SESSION['FirstName'] = $FirstName;
$_SESSION['LastName'] = $LastName;
//$_SESSION['CompanyName'] = $CompanyName;
$_SESSION['valid_log'] = array($_SERVER['REMOTE_ADDR'], $User);

Then, from any page when I perform this initial query, I am able to identify the user's ProfileID and CompanyID. Don't ask me how I figured this out, but it works.

	$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
 	$result = mysql_query($query);	
	$row = mysql_fetch_array($result); 


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

Print "User ID: $id <br>";	
Print "Company ID: $CoID<br>";

So, if I replace my query with either one you suggested and test with the Print command, no ID values are returned! Go figure. Again, I can't explain why.

$query = "SELECT * 
	FROM userProfiles, companyProfile, employeeProfile 
	WHERE userProfiles.CompanyID=companyProfile.CompanyID 
	AND userProfiles.CompanyID=employeeProfile.CompanyID 
	AND userProfiles.ProfileID = ".$_SESSION['User']." 		ORDER BY employeeProfile.EmployeeLast, employeeProfile.EmployeeFirst 
	";

$result2 = mysql_query($query);	
$row = mysql_fetch_array($result2); 

if($row) {
  $CoID = $row["CompanyID"];
  $id = $row["ProfileID"];
}

Print "User ID: $id <br>";	
Print "Company ID: $CoID<br>";

Please, help me understand!

[paulnaj]

Hi Tracey,

I've been waiting on a call here at work so I've had time to have a crack at a sort of 'start' page. 😉

It's pretty much what you've done but tidied up ... at least so I can get what's going on a little better.

There's obviously a whole lot missed out, but it shouldn't be a problem 'filling in the gaps' if we get it right. Read the comments to see if my reasoning is OK.

(Could also be glitches ... no access to your db means I can't debug here!!)

Anyway ...

<?php
// 	connect to database 
$link = mysql_connect("localhost", "root", "password") 
	or die("Could not connect. Please try again later."); 

mysql_select_db("ecbp", $link) 
	or die("Could not select database. Please try again later."); 

// 	Perform SQL query : select the userProfile that matches user name and password
/* I changed the next line a bit just to see more clearly what's going on */
$sql= "
	SELECT * 
	FROM userProfiles 
	WHERE UserName='".$_REQUEST['UserName']."' 
	AND UserPassword='".$_REQUEST['UserPassword']."'
"; 
/*
Note that, on some servers using $UserName and $UserPassword won't actually contain any values!!. 
They need to be changed to either ...
$_POST['UserName'] and $_POST['UserPassword']
... or ...
$_GET['UserName'] and $_GET['UserPassword']
... depending on which method was used to send these data to this php page.

I've used $_REQUEST, which is just $_GET and $_POST merged together
*/

//* return the "result set" (i.e. rows or entries) resulting from the query or return error. */ 
$result = mysql_query($sql) 
	or die("Error in query result");

$row = mysql_fetch_array($result); 
/* 
Could be much more explicit here ... use mysql_num_rows to test whether one and only one user has these details
*/
if(mysql_num_rows($row) != 1){ 
	die('More than one user with the same login details in the database');
} else {
	$User = $row;
}

// If we get here, we've got exactly one user.
/*
Store minimal data in the session array 
*/
session_start();
$_SESSION['UserProfileID'] = $User["ProfileID"]; 
$_SESSION['valid_log'] = array($_SERVER['REMOTE_ADDR'], $User["UserName"]);

/* PAUL: 
1. I've changed the previous $_SESSION['User'] (this was a guess!) to $User['ProfileID']
2. We can use the same $sql, $result variables again.
3. Choose only those fields that you want to use
*/
$sql = "
	SELECT employeeProfile.*
	FROM employeeProfile, userProfiles
	WHERE employeeProfile.CompanyID = userProfiles.CompanyID
    AND userProfiles.ProfileID = ".$User['ProfileID']."
    ORDER BY employeeProfile.EmployeeLast, employeeProfile.EmployeeFirst 
"; 
$result = mysql_query($sql);     

echo '
<html>
<head>
<title>Test Page</title>
</head>
<body>
<h4>User: '.$User["UserName"].'</h4>
<h5>Company: '.$User["CompanyName"].'</h5>
<hr>
<strong>Employees</strong>
<table border="1" cellpadding="5" cellspacing="0">
	<tr>
		<th>ID</th>
		<th>Name</th>
		<th>City</th>
	</tr>
';

while($row = mysql_fetch_array($result)) {
	echo '
	<tr>
		<td NOWRAP>'.$row["EmployeeID"].'</td>
		<td NOWRAP>'.$row["EmployeeLast"].', '.$row["EmployeeFirst"].'</td>
		<td NOWRAP>'.$row["EmployeeCity"].', '.$row["EmployeeState"].'</td>
	</tr>
';
} 

echo '
</table>
</body>
</html>
';
?>

EDIT: Typo spotted !! Paul 🙂

[paulnaj]

Oops, I've seen a major booboo ...

This is better ...

<?php 
//     connect to database 
$link = mysql_connect("localhost", "root", "password")  

    or die("Could not connect. Please try again later.");  

mysql_select_db("ecbp", $link)  

    or die("Could not select database. Please try again later.");  

//     Perform SQL query : select the userProfile that matches user name and password 
/* I changed the next line a bit just to see more clearly what's going on */ 
$sql= " 
    SELECT * 
    FROM userProfiles 
    WHERE UserName='".$_REQUEST['UserName']."' 
    AND UserPassword='".$_REQUEST['UserPassword']."' 
";  

/* 
Note that, on some servers using $UserName and $UserPassword won't actually 
contain any values!!. 
They need to be changed to either ... 
$_POST['UserName'] and $_POST['UserPassword'] 
... or ... 
$_GET['UserName'] and $_GET['UserPassword'] 
... depending on which method was used to send these data to this php page. 

I've used $_REQUEST, which is just $_GET and $_POST merged together 
*/ 

/* return the "result set" (i.e. rows or entries) resulting from the query 
or return error. */ 
$result = mysql_query($sql)  

    or die("Error in query result"); 
/* 
Should be much more explicit here ... use mysql_num_rows to test whether one and only 
one user has these details 
*/ 
if(mysql_num_rows($result) != 1){  

    die('More than one user with the same login details in the database'); 
} else { 
    $User = mysql_fetch_array($result); 
} 

// If we get here, we've got exactly one user. 
/* 
Store minimal data in the session array 
*/ 
session_start(); 
$_SESSION['UserProfileID'] = $User["ProfileID"];  

$_SESSION['valid_log'] = array($_SERVER['REMOTE_ADDR'], $User["UserName"]); 

/*
1. I've changed the previous $_SESSION['User'] (this was a guess!) to $User['ProfileID'] 
2. We can use the same $sql, $result variables again. 
3. Choose only those fields that you want to use 
*/ 
$sql = " 
    SELECT employeeProfile.* 
    FROM employeeProfile, userProfiles 
    WHERE employeeProfile.CompanyID = userProfiles.CompanyID 
    AND userProfiles.ProfileID = ".$User['ProfileID']." 
    ORDER BY employeeProfile.EmployeeLast, employeeProfile.EmployeeFirst 
";  

$result = mysql_query($sql);      

echo ' 
<html> 
<head> 
<title>Test Page</title> 
</head> 
<body> 
<h4>User: '.$User["UserName"].'</h4> 
<h5>Company: '.$User["CompanyName"].'</h5> 
<hr> 
<strong>Employees</strong> 
<table border="1" cellpadding="5" cellspacing="0"> 
    <tr> 
        <th>ID</th> 
        <th>Name</th> 
        <th>City</th> 
    </tr> 
'; 

while($row = mysql_fetch_array($result)) { 
    echo ' 
    <tr> 
        <td NOWRAP>'.$row["EmployeeID"].'</td> 
        <td NOWRAP>'.$row["EmployeeLast"].', '.$row["EmployeeFirst"].'</td> 
        <td NOWRAP>'.$row["EmployeeCity"].', '.$row["EmployeeState"].'</td> 
    </tr> 
'; 
}  

echo ' 
</table> 
</body> 
</html> 
'; 
?>