Creating Multidimentional Array From SQL Statement

[doubleJ]

Hello...
I have 3 sql tables for event information.
1 table is for the venue (address, phone, etc...).
1 table for the actual event information (speaker, date, etc...).
1 table is an intermediate "join" table (1 venue could be used for multiple events and events could span multiple days or have multiple sessions per day).

Table example...

TblSermon:
ID Date Time Speaker
----------------------------------
122 2012-09-18 "09:00 AM" "Joshua Jackson"
123 2012-09-18 "11:00 AM" "Joshua Jackson"
1452 2014-02-25 "06:00 PM" "JoJo The Idiot Circus Boy"

TblLocation:
ID Location  City State
-------------------------------------------
14 "Omega Arena"  Boston MA
1263 "Kentucky Civic Center" Lexingon KY

TblJoinSermonLocation:
ID MeetingName Location Sermon
-----------------------------------
323 "Special Times" 14 122
324 "Special Times" 14 123
345 "Journeys" 1263 1452

Query example...

SELECT TOP (100) PERCENT dbo.TblSermon.Date, dbo.TblSermon.Time, dbo.TblSermon.Speaker, dbo.TblJoinSermonLocation.MeetingName, dbo.TblLocation.Location, dbo.TblLocation.City, dbo.TblLocation.State
FROM dbo.TblLocation RIGHT OUTER JOIN dbo.TblJoinSermonLocation ON dbo.TblLocation.ID = dbo.TblJoinSermonLocation.Location RIGHT OUTER JOIN dbo.TblSermon ON dbo.TblJoinSermonLocation.Sermon = dbo.TblSermon.ID
WHERE (dbo.TblSermon.Date >= { fn NOW() })
ORDER BY dbo.TblSermon.Date, dbo.TblSermon.Time

Obviously, this will list all information (Date, Time, Speaker, MeetingName, Location, City, State) for each record that matches.
What I'm looking for is the ability to show each venue (Location) once and then list all the dates/times associated with that event, under it.
There's no point in duplicating MeetingName, Location, City, State for each record.

Array example...

Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
[MeetingName] => Special Times
[Date] => 2012-09-19 00:00:00.000
[Time] => 09:00 AM
[Speaker] => Joshua Jackson
)
Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
[MeetingName] => Special Times
[Date] => 2012-09-19 00:00:00.000
[Time] => 11:00 AM
[Speaker] => Joshua Jackson
)

My theory is that I can create a multidimensional array where the first level is the venue info (Location, City, State), the second level is the event info (MeetingName), and the third are the dates/times).

Array example...

Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
Array
(
[MeetingName] => Special Times
Array
(
[Date] => 2012-09-19 00:00:00.000
[Time] => 09:00 AM
[Speaker] => Joshua Jackson
)
Array
(
[Date] => 2012-09-19 00:00:00.000
[Time] => 11:00 AM
[Speaker] => Joshua Jackson
)
)
)

Unfortunately, I haven't figured out how to do that.
Hehehe...
Can you guys help me out?
BTW...
How do you indent, here?
JJ

[rocknbil]

I wouldn't do an array. Arrays need to be stored in memory, and the more memory you use, the slower things get. Additionally, once you construct your arrays, you'll have to do all your sorting and output in programming, and you can leverage the power of mySQL to make your job a lot easier.

I would do some sort of toggle so it will only output a "new name" if the name value has changed. For example, simplifying so you can see the concept,

$rows=$currName=null; 
while (list($name,$event,$date) = mysql_fetch_array($result)) { 
 if ($name==$currName) { $displayName=' '; } 
 else { $displayName=$currName=$name; } 
 $rows .= " 
 <tr> 
 <td>$displayName</td> 
 <td>$event</td> 
 <td>$date</td> 
 </tr> 
 "; 
} 
if ($rows) { echo "<table>$rows</table>"; } 
else { echo "<p>No results were found.</p>"; } 

Some notes:

- as always, not working code typed on the fly for example only.

- This
$rows=$currName=null;
1) is just good programming (always initialize any variables you use) and 2) prevents "undefined variable" warnings the first time you go through the loop and concatenate the variable $rows. They may not display in the browser, but will clog up your error logs fast, even though they're just warnings.

- Yes, the previous stores a scalar string in memory, it's just a lot less data to store. :-) This approach allows you to easily do the very important if/else (what if nothing is found?) Additionally as mentioned you don't have to jump through hoops in programming working with the arrays.

- as you can see this "tracks" the "name" in the current row with the variable $currName. If it's the same as the last row, it displays a non breaking space (because tables display weird in IE if empty,) otherwise it displays the name and should only do so once for each of the same value (be sure to add order by event_date desc, event_name asc in your select). Rinse and repeat for other recurrent values (phone, etc.)

- This
list($name,$event,$date)

is a little used shortcut to

while ($row = mysql_fetch_array($result)) { 
 $name = $row['name']; // or $row[0] 
 $event = $row['event']; // or $row[1] 
 $date = $row['event_date']; // or $row[2] 

... and works very well because mysql_fetch_array() returns both an associative and an indexed array, and we're using the latter to leverage with list().

- The above is formatted using the pre tags (find them in the links below posting) and doesn't allow tabs. Use only spaces. Many people use the quote tags to do a similar thing.

[doubleJ]

Thanks for the insightful response.
I had played around with list() but never got the results I was looking for (presumably because I didn't do the $currName part).
I am using PDO and MSSQL, so my db code will be a little different.
I think I can hack it together, though.

 Thanks for the "pre" idea. 

JJ

[doubleJ]

Ok...
I wasn't figuring out your

list()

, after all.
Here is what I ended up with and I think it produces the same results.

<?php 
try { 
 $dbh = new PDO("sqlsrv:Server=localhost;Database=Sermons", "", ""); 
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
 $sql = "SELECT TOP (100) PERCENT dbo.TblSermon.Day, dbo.TblSermon.Date, dbo.TblSermon.Time, dbo.TblSermon.Speaker, dbo.TblSermon.Series, dbo.TblSermon.Sarasota, dbo.TblSermon.NonFlc, dbo.TblJoinSermonLocation.MeetingName, dbo.TblLocation.Location, dbo.TblLocation.Pastors, dbo.TblLocation.Address, dbo.TblLocation.City, dbo.TblLocation.State, dbo.TblLocation.Zip, dbo.TblLocation.Country, dbo.TblLocation.Phone, dbo.TblLocation.Email, dbo.TblLocation.WebAddress 
  FROM dbo.TblLocation RIGHT OUTER JOIN dbo.TblJoinSermonLocation ON dbo.TblLocation.ID = dbo.TblJoinSermonLocation.Location RIGHT OUTER JOIN dbo.TblSermon ON dbo.TblJoinSermonLocation.Sermon = dbo.TblSermon.ID 
  WHERE (dbo.TblSermon.Date >= { fn NOW() }) 
  ORDER BY dbo.TblSermon.Date, dbo.TblSermon.Time"; 
 $stmt = $dbh->prepare($sql); 
 $stmt->execute();  
 $stmt->setFetchMode(PDO::FETCH_ASSOC); 
 $count = 0; 
 $rows = $currName = null; 
 foreach ($stmt as $row) { 
  if ($row["Location"] == $currName) { 
   $displayName = "&nbsp;"; 
  } else { 
   $displayName = $currName = $row["Location"]; 
  } 
  $rows .= " <tr> <td>" . $displayName . "</td> <td>" . $row["MeetingName"] . "</td> <td>" . $row["Date"] . "</td> </tr> "; 
  $count++; 
 } 
 if ($rows) { 
  echo "<table>" . $rows. "</table>"; 
 } else { 
  echo "<p>No results were found.</p>"; 
 } 
 unset($row); 
 if ($count == 0) { 
  echo "No scheduled events"; 
 } 
 $dbh = null; 
} 
catch(PDOException $e) { 
 echo $e->getMessage(); 
} 
?> 

What is the difference in how these two work?
Is one intrinsically better than the other?
JJ

[doubleJ]

What is the purpose of . in

$rows .= " <tr> <td>" . $displayName . "</td> <td>" . $row["MeetingName"] . "</td> <td>" . $row["Date"] . "</td> </tr> ";

?
When I took it out, only 1 result was displayed (instead of 10-15).
JJ

[doubleJ]

Ok...
I found a big wrench in the plan.
The reason that I was looking at a multidimensional array was that I would be able to nest the bits of information, appropriately.
For instance...

Upcoming Events 
Special Times 
 Omega Arena (Boston, MA) 
 Session One 
  Date: September 19th @ 09:00AM 
  Speaker: Joshua Jackson 
 Session Two 
  Date: September 19th @ 11:00AM 
  Speaker: Joshua Jackson 

I don't seem to be able to do that with the afore-mentioned code, since each record produces a line with all the fields (albeit black for some).
This is great for tabular data, though.
JJ

[rocknbil]

Ehh . . you just discoverd the problem with pre, you need to break long lines. :-) A mod will be along to fix it soon . . .

What is the purpose of . in $rows .= " .....

This approach (concatenating to $rows) allows you to easily do the very important if/else (what if nothing is found?)

Concatenating is "adding to a string as needed"

$me = 'Meoh ';
$me .= ' Myoh';
echo $me; // "Meoh Myoh"

The reason that I was looking at a multidimensional array was that I would be able to nest the bits of information, appropriately.

You should still be able to do that. You just "nest" your display items. In the same way you're "tracking" the outer event name, you have a variable inside to display stuff as the sessions change (or not.)

[doubleJ]

Concatenating is "adding to a string as needed"
$me = 'Meoh ';
$me .= ' Myoh';
echo $me; // "Meoh Myoh"

I guess this is the same, but what I normally do is...

$me = 'Meoh ';  
$me = $me . ' Myoh';  
echo $me; // "Meoh Myoh"  

Is there a time when

.=

is better than

= $variable

?

You should still be able to do that. You just "nest" your display items. In the same way you're "tracking" the outer event name, you have a variable inside to display stuff as the sessions change (or not.)

Hmmm...
I'm trying to wrap my head around the idea of that.
The only way that I can think how that would even work would be to define a butt-load (that's a metric butt-load, btw...) of variables ($session1date, $session1time, $session1speaker, $session2date, $session12date, etc...).
One particular event that we have, each year, has 7 sessions over 3 days with 2 speakers.
Maybe I'm looking at it backwards.
I know there is the ability to do

for ($i,$i<something,$i++) {}

, but I haven't used that very much.
I guess I just think more like arrays (like nested ordered lists in html).
Hehehe...
Speaking of nested lists, that it how I would probably code this data.
The way I'm taking your concept is almost like I would need to nest

foreach

over and over to get the information that I want.
I'm assuming that's not what you mean, since that seems like it would be a lot of overhead and processing time.
That's just the only way I can get your concept.
JJ

[rocknbil]

Is there a time when .= is better than = $variable?

When you're inside a loop where inline concatenation isn't possible - as above. :-)