[SOLVED] Need help with nested or sub-query

[spock9458]

I don't know the correct terminology for this, and I know I have done it before but I lost my code.  I'm basically trying to run a query to find names in a "company" table, and then run a sub-query or "nested" query to find names in a "contact" table based on the company name found in the first query.  I want the output to look like this:

 

Company1

  Contact1

  Contact2

 

Company2

  Contact1

  Contact2

  Contact3

etc...

 

Here is my code attempt:

 

<?php

Connection to DB


// Retrieve all the data from the "company" table
$result = mysql_query("SELECT * FROM company")
or die(mysql_error());  

// store the record of the "company" table into $row
//$row = mysql_fetch_array( $result );

echo "<table border='1'>";
echo "<tr> <th>Company Name</th> <th>ID</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['comp_name'];
echo "</td><td>"; 
echo $row['id'];
echo "<br>";

// This is my first nested query
// Retrieve entries from "contact" table that match Companies
$result2 = mysql_query("SELECT * FROM contact WHERE cont_company = '$row['comp_name']'") or die(mysql_error());
// New table for contact results
echo "<table border='0'>";
// keeps getting the next row until there are no more
while($row2 = mysql_fetch_array( $result2 )) {
// Print out the contacts under the company
echo "<tr><td>";
echo $row2['cont_name'];
echo "</td><td>"; 
echo $row['id'];
echo "</td></tr>";
}
echo "</table>";


echo "</td></tr>"; 
} 

echo "</table>";

?>

 

I get a BIG parse error when I try this:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/robg/public_html/RobTestNest1.php on line 29

 

Am I anywhere close to getting this right?  Please point me in the right direction.

 

Thanks,

Rob

[Mark Baker]

Take a close look at the ' in your SQL statement, and consider changing it slightly.

$result2 = mysql_query("SELECT * FROM contact WHERE cont_company = '$row['comp_name']'") or die(mysql_error());

perhaps to

$secondaryQuery = "SELECT * FROM contact WHERE cont_company = '".$row['comp_name']."'";
$result2 = mysql_query($secondaryQuery) or die(mysql_error());

 

Or even better, use a single query that joins both the company table and the contact table

SELECT cpy.comp_name,
       ctct.cont_name
  FROM company cpy
  LEFT JOIN contact ctct ON ctct.cont_company = cpy.comp_name

 

[spock9458]

Thanks, Mark... your suggested code changes worked!  I will have to study a little more to learn how to use the JOIN function. 

 

Am I correct in seeing that you created new abbreviations (cpy, ctct) in the Query itself?  You then select from two different tables... is this a type of "concatenation"?  I have a lot to learn.

 

If you get a chance to explain your JOIN Query a little more, it might help me out.  Thanks,

 

Rob

[Mark Baker]

Thanks, Mark... your suggested code changes worked!  I will have to study a little more to learn how to use the JOIN function.

If you're going to be doing a lot of PHP coding using databases, it's well worthwhile learning about SQL syntax as well: a lot of PHP coders might write great PHP but hopelessly inefficient database queries... and as a result, their pages run slowly.

The way you'd nested your queries in the original example is a prime example of a mistake that a lot of coders make - I've even found it in work that my own development team have done: the fact that you're willing to learn will definitely make you a better all-round coder. SQL is incredibly powerful when properly used, especially when you make it work for you rather than having to add a lot of extra PHP code to perform multiple queries.

 

Am I correct in seeing that you created new abbreviations (cpy, ctct) in the Query itself?  You then select from two different tables... is this a type of "concatenation"?  I have a lot to learn.

cpy and ctct are simply synonyms that I assigned to the two tables... not strictly necessary, but a habit that I've got into when writing queries that reference more than one table. If both tables have fields with the same name, then the query can't guess which one you mean, so prefixing the field name with that synonym (or with the table name) allows you to tell the SQL query which one you actually want.

 

I'm probably not the best person to ask for an explanation... I'm not so good at explaining (and MySQL isn't my database of choice), but basically a join returns a row for every "set of records" in the database that matches the criteria across two or more tables. The "straight" join will only return a row when both/all referenced tables have records that match the where criteria; while LEFT and RIGHT joins will return a record for one of the pair/set of tables even if there are no records that match the join in the other table(s) in the set.

As an example, a forum has members, but not all members have posted any messages: tables might by USERS and POSTS. You can write a SQL query to retrieve a list of the members and the posts that they have made.

Using a straight join, the query might look like:

SELECT U.USERNAME,
       P.POSTID
  FROM USERS U,
      POSTS P
WHERE P.USERID = U.USERID

However, as a straight join, this will only return the names of users who have made one or more postings.

 

As a left join query:

SELECT U.USERNAME,
       P.POSTID
  FROM USERS U
      LEFT JOIN POSTS P ON P.USERID = U.USERID

it will return the list of all users, even those who have made no postings.

 

 

[spock9458]

OK, I'm getting very close to my solution, but one last thing is not working.  Here is the short explanation:  I am building an online directory for an organization, to list the following info:

1. Companies

2. Contact people at those companies, who should be listed immediately below the company entry

3. Some Companies have Branch offices that should be listed right below the contacts at the main company

4. Most of the Branch offices also have contacts that should be listed right below their entries

 

I have "nested" the various queries so that everything in my sample test data is displaying just right, EXCEPT the last thing - the contacts at the Branch offices.  Here is the link to my test output:

http://www.robgrinage.com/RobTestNest5.php

 

As you can see, the third company listed is the one that has Branch offices, correctly showing below it.  However, instead of the Branch contacts showing right below the Branch office info, ALL other contacts are being shown after EVERY company.  I have tried everything I can think of to fix this... here is my code:

 

// Retrieve all the data from the "company" table
$result = mysql_query("SELECT * FROM company")
or die(mysql_error());  


// Start building the table for showing results using "while" loop

echo "<table border='1' cellpadding='10' cellspacing='0' bgcolor='#f0f0f0' style='margin-left:100px; margin-bottom:20px'>";
// Here is the "while" loop
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table row
echo "<tr><td><b>"; 
echo $row['comp_name'],"<br>";
echo $row['comp_uw'],"</b><br>";
echo $row['comp_street'],"<br>";
echo $row['comp_csz'],"<br>";
echo $row['comp_ph'],"&nbsp&nbsp ",$row['comp_fx'],"<br>";
echo $row['comp_email'],"<br>";
echo $row['comp_web'];
echo "<br>"; 

// This is my first nested query
// Retrieve entries from "contact" table that match Companies
$secondaryQuery = "SELECT * FROM contact WHERE cont_company = '".$row['comp_name']."' ORDER BY cont_rank";
$result2 = mysql_query($secondaryQuery) or die(mysql_error());
// New table for contact results
echo "<table border='0' style='margin-left:30px'>";
// keeps getting the next row until there are no more
while($row2 = mysql_fetch_array( $result2 )) {
// Print out the contacts under the company
echo "<tr><td>";
echo "<b>",$row2['cont_name'],"</b>, ",$row2['cont_title'];
echo "</td></tr>";
}
echo "</table>";
echo "<br>";

// Here is the second nested query
// Retrieve entries from "branch" table that match Companies
$thirdQuery = "SELECT * FROM branch WHERE br_company = '".$row['comp_name']."' ORDER BY br_street";
$result3 = mysql_query($thirdQuery) or die(mysql_error());
// New table for branch results
echo "<table border='0' style='margin-left:30px'>";
// keeps getting the next row until there are no more
while($row3 = mysql_fetch_array( $result3 )) {
// Print out the branches under the company
echo "<tr><td>";
echo "<b>",$row3['br_name'],"</b><br>";
echo $row3['br_street'],"<br>";
echo $row3['br_csz'],"<br>";
echo $row3['br_ph'],"&nbsp&nbsp ",$row3['br_fx'],"<br>";	
echo "</td></tr>";
}
echo "</table>";
echo "<br>";

// This is my third nested query
// Retrieve entries from "contact" table that match Branches
$fourthQuery = "SELECT * FROM contact WHERE cont_branch = '".$row3['br_street']."'";
$result4 = mysql_query($fourthQuery) or die(mysql_error());
// New table for contact results
echo "<table border='0' style='margin-left:60px'>";
// keeps getting the next row until there are no more
while($row4 = mysql_fetch_array( $result4 )) {
// Print out the contacts under the branch
echo "<tr><td>";
echo "<b>",$row4['cont_name'],"</b>, ",$row4['cont_title'];
echo "</td></tr>";
}
echo "</table>";
echo "<br>";


echo "</td></tr>"; 
} 

echo "</table>";

?>

 

I realize this is kind of long, but hopefully someone has the time to help me figure out what is wrong with my last nested query.  FYI - some Branches do not have a "Company Name" and therefore I have linked the Branch contacts to the branch street address (br_street).  I have made sure the spelling and spacing are identical.

 

Thanks,

Rob

[spock9458]

OK, I figured this out all on my own... The problem was I was closing the table row and the table of the "$row3" result before I was running the last Query, which needed the result of $row3['br_street'] in order to select the proper contacts.  I just moved the closing tags of that table to the end of the branch contact table, and voila! It is working like a charm now.

 

Thanks for all help!

Rob