Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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...

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
 
Thanks so much for the info. I had seen some mention of using JOIN for similar purposes but had trouble understanding how it would get me where I need to be. Definitely appreciate your comment about working through it myself as well and will do so. Clearly I need to spend some time playing around with arrays in general. Will check in again if I hit a snag. Cheers. –  washe May 31 '12 at 3:38
 
just think about what you're really asking of the data. You REALLY want all the times tied to the workshop name and price. The header is simply a display issue. so you want your result query to include ALL times (select * from times and LEFT JOIN the workshop table for names and prices). When writing SQL queries, I tend to decide which single table will return the highest number of records, then just LEFT JOIN the remaining data you need to match. In my experience, this approach works for about 80% of all SQL queries you will need to write. –  Juventus18 Jun 1 '12 at 20:09
 
Thanks again for the info, trying to get through another pile of work to hopefully get back to this soon. Hope it's ok that I leave this open until I can get back to it. –  washe Jun 5 '12 at 23:53
add comment
up vote 0 down vote

I don't see anything wrong with the way you're doing things. I suppose you could concatenate the result and then manipulate the output in your application using one query. Your query might looks something like

SELECT
    n.workshopNameId,
    n.price,
    n.description,
    GROUP_CONCAT(w.date) as dates,
    GROUP_CONCAT(w.time) as times
FROM tws_workshopNames n
INNER JOIN tws_workshops w USING(workshopNameID)
GROUP BY n.workshopNameID
share|improve this answer
 
Thanks for your quick response. The above does work but after a lot of reading my understanding is that having a db call within a loop is bad practice. I believe the idea is that it makes for more db calls than are necessary and will slow down the server. I'll see what I can do with your concatenate suggestion. Thanks again. –  washe May 31 '12 at 0:01
 
More calls doesn't necessarily mean more execution time. From SQL Antipatterns: Programmers have an instinct that the fewer SQL queries, the better the performance. This is true assuming the SQL queries in question are of equal complexity. On the other hand, the cost of a single monster query can increase exponentially, until it’s much more economical to use several simpler queries. Depends on your situation and what you're trying to accomplish. I wouldn't worry about it until you notice it becoming a problem. You could cache the queries as well for increased performance. –  Steve Robbins May 31 '12 at 0:06
 
That does make sense, I'll keep that in mind. Thanks again for the input! –  washe May 31 '12 at 0:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.