Access dynamic value in PHP / MySQL query

Question

I'm working on getting a value from a database table, then getting another value based on the first value. This first part is working fine. I'm just getting a list of organization names and outputting them to a list:

$org_name = mysql_query('SELECT org_name FROM Orgs_Teams GROUP BY org_name');
echo "<ul>";
while ($org_row = mysql_fetch_row($org_name)) {
echo "<li>{$org_row[0]}</li>";
}
echo "</ul>";

But here's where I'm stuck. I now need to get the names of each team within each organization. I'm working with a simple database table with two columns: org_name & team_name.

Ultimately I'm looking to have a nested list for my nav menu like this (which gets repeated for each Org Name):

Org Name 1
- Team Name 1
- Team Name 2
- Team Name 3

I've been messing with different queries like this:
$team_name_query = mysql_query('SELECT team_name FROM Orgs_Teams WHERE org_name = "$org_name" ');

But haven't been able to get anything to work. My thought was that the team name query would go inside the 'while' loop and create the nested list but I'm stuck. Thanks in advance for any help.

Popnoodles · Accepted Answer · 2012-12-27 01:10:25Z

Have a column in your teams table that has the org id that team belongs to and join the tables. This gives you all the data at once

org_id   org_name
-------+---------
   1   |   Org 1
   2   |   Org 2


team_id    team_name    org_id
---------+------------+--------
    1    |   Team 1   |   1
    2    |   Team 2   |   1
    3    |   Team 3   |   2
    4    |   Team 4   |   2

$teams="SELECT * FROM Orgs
INNER JOIN Orgs_Teams USING (org_id)
WHERE org_id=1";

returns

org_id   org_name    team_id    team_name    org_id
-------+-----------+---------+------------+--------
    1  |   Org 1   |    1    |   Team 1   |   1
    1  |   Org 1   |    2    |   Team 2   |   1

Then iterate through the results to create the child list elements and use the org info from the first (or any) result to create the parent.

echo '<ul><li>';
$i=0;
while ($row = mysql_fetch_row($teams)) {
    if ($i==0) echo $row['org_name'] .' <ul> ';
    echo '<li>' . $row['team_name'] .'</li>';
}
echo '</li></ul></li></ul>';

You can use the same query without the WHERE clause to get all your orgs and the teams within them and while iterating compare the current org_id with the previous to see whether or not you should end the org list item you're populating with teams and start another .

Twisty · Accepted Answer · 2012-12-27 01:07:33Z

0

You can use a nested loop, so performing a second query while in the loop of the first loop. The other would be to push the results of your first query into an array. You can then loop the array and perform a series of queries.

$org_names = new array();
while ($org_row = mysql_fetch_assoc($org_name)) {
   $org_names = $org_row[];
}

echo "<ul>";
foreach($org_names as $name){
 // Do more queries
}
echo "</ul>";

Also as was just mentioned, performing a JOIN query could pull all the data needed in one recordset. Something like:

SELECT A.org_name, B.team_name FROM Orgs_Teams AS A JOIN Orgs_Teams AS B ON A.org_id = B.org_id WHERE A.org_name = '$org_name';

answered Dec 27, 2012 at 1:07

Twisty

31k2 gold badges30 silver badges50 bronze badges

Collectives™ on Stack Overflow

Access dynamic value in PHP / MySQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related