PHP / SQL替代嵌套while循环中的db调用

Question

My first post, tried to be as thorough as possible, apologies in advance if I've gotten something wrong. I'm pretty novice with PHP/SQL as well so please be patient with me. I've found a couple of similar questions about loops within loops but I'm not sure the solutions apply in my case.

I have two tables, tws_workshopNames and tws_workshops. The primary key from tws_workshopNames is used as a foreign key in tws_workshops to relate the two tables. The reason I've split this into two tables is there are many cases where the same workshop name/price/description is offered on multiple dates/times.

Can't submit a screenshot but here's a simplified outline of the table design in SQL Server:

tws_workshopNames:
workshopNameID (pri)
description
price
etc.

tws_workshops:
workshopID (pri)
workshopNameID (foreign)
date
time
etc.

What I want to happen is basically this:

1. query tws_workshopNames table and display workshopName/price/description/etc.
2. for each workshopName go through the tws_workshops table and display all records that have the same workshopNameID

In other words, go through tws_workshopNames and display the first workshopName, then go through tws_workshops and display all records that are related to that workshopName, then go to next workshopName in tws_workshopNames, display all records related to that workshopName etc.

I'm able to achieve the desired result by using a while loop within a while loop wherein the outer loop does a call to tws_workshopNames and the nested loop does a call to the tws_workshops table. However I've been reading a lot about this and it's clear this is not a good approach as it results in a lot of calls to the db, but I'm having a hard time understanding any alternatives.

Desired output:

Workshop 1
price
description
 date (of workshop 1)
 time (of workshop 1)
 ...

Workshop 2
price
description
 first date (of workshop 2)
 first time (of workshop 2)
 second date (of workshop 2)
 second time (of workshop 2)
 third date (of workshop 2)
 third time (of workshop 2)
 ...

Workshop 3
price
description
 date (of workshop 3)
 time (of workshop 3)
 ...

etc.

Here is the current code that works with nested while loops:

<?php
// query workshopNames table, what types of workshops are available?
$query = mssql_init("tws_sp_workshopNames", $g_dbc);

// pull up result
$result = mssql_execute($query);
$numRows = mssql_num_rows($result);

while($row = mssql_fetch_array($result)) {

echo "<div style=\"...\">
<span class=\"sectionHeader\">" . $row['workshopName'] . "</span><br />
<span class=\"bodyText\"><strong>" . $row['price'] . "</strong></span><br />
<span class=\"bodyText\">" . $row['description'] . "</span>";

$workshopNameID = $row['workshopNameID'];

// query workshops table, what are the dates/times for each individual workshop?
$query2 = mssql_init("tws_sp_workshops", $g_dbc);
mssql_bind($query2, "@workshopNameID", $workshopNameID, SQLVARCHAR);

//pull up result
$result2 = mssql_execute($query2);
$numRows2 = mssql_num_rows($result2);

while($row2 = mssql_fetch_array($result2)) {

echo $row2[date] . "&nbsp;";
echo $row2[time] . "<br />";

};

echo "</div><br />";

};
?>

The stored procedures are very simple:

tws_sp_workshopNames = "SELECT workshopNameID, workshopName, description, location, price FROM tws_workshopNames"
tws_sp_workshops = "SELECT date, time, maxTeachers, maxStudents, teachersEnrolled, studentsEnrolled FROM tws_workshops WHERE workshopNameID=@workshopNameID"

Hope that's all relatively clear, all I'm really looking for is a better way to get the same result, i.e. a solution that does not involve a db call within the loops.

Thanks in advance for any help, been a few days straight banging my head against this one...

Answer 1

You are correct to avoid usage of looping queries in this case (since the desired result can be achieved with just a simple JOIN in one query).

I would avoid using GROUP_CONCAT() as well because there is a character limit (by default, you can change it), plus you have to parse the data it outputs, which is kind of a pain. I would just get all the data you need by joining and get every row. Then load the data into arrays using the workshop ID as the key but leave the array open to append each of your time data as a new array:

$workshops[$workshop_name][] = $timesArray;

Then on your output you can loop, but you don't have to hit the database on each call:

foreach ($workshops as $workshop_name => $times)
{
  echo $workshop_name;
  foreach ($times as $time)
  {
    echo $time;
  }
  echo "<br>";
}

This is not the exact code, and as you've pointed out in your question, you want to keep/display some other information about the workshops - just play around with the array structure until you get all the data you need in a hierarchy. You can use something like http://kevin.vanzonneveld.net/techblog/article/convert_anything_to_tree_structures_in_php/ if you are trying to build a deep tree structure, but I think that's overkill for this example.

Since this is what I would call an "Intermediate Level" question, I think you should try to work through it (THIS is what makes you a good programmer, not copy/paste) using my suggestions. If you get stuck, comment and I'll help you further.