Proper use for a multidimensional array

scambro

Hi,

I'm having a hard time understanding how this is supposed to work in my circumstance, perhaps multidimensional array is the wrong term.

I'm used to thinking about this from a different SQL based language, so tell me if this makes no sense. I have two tables in my database (venue, and venue_synonym), they link together based on the venue_id key. I want my array to look something like this (using my other knowledge-set):

1 venue_id
1 beg_active_dt
1 end_active_dt
1 seating_capacity
1 venue_type_cd
1 synonyms[*]
2 synonym_mnemonic
2 synonym_type
2 synonym_beg_active_dt
2 synonym_end_active_dt
etc.

All the 1's are from the venue table, all the 2's are from the venue_synonym table. What I have below seems to work, but I'm not sure that I've really gone about it the proper way. Could someone take a look and let me know if this looks like the right thing to do when pulling all of this data in? Thanks!

$query_1 = "SELECT
				v.venue_id,
				v.venue_type_id,
				v.seating_capacity,
				v.active_ind,
				v.beg_active_dt,
				v.end_active_dt,
				v.city,
				v.state_country_id
			FROM
				venue v
			WHERE 
				v.active_ind = 1
				AND v.venue_id = 365
			ORDER BY 
				v.venue_id";
$results_1 = mysql_query($query_1) or die(mysql_error());

$v = 0;
$venues = array();
while($row = mysql_fetch_array($results_1))
{
	$venues['venue_id'][$v] 		= $row['venue_id'];
	$venues['venue_type_id'][$v] 	= $row['venue_type_id'];
	$venues['seating_capacity'][$v] = $row['seating_capactiy'];
	$venues['active_ind'][$v] 		= $row['active_ind'];
	$venues['beg_active_dt'][$v] 	= $row['beg_active_dt'];
	$venues['end_active_dt'][$v] 	= $row['end_active_dt'];
	$query_2 = "SELECT
				vs.venue_synonym_id,
				vs.venue_id,
				vs.mnemonic,
				vs.primary_ind,
				vs.active_ind,
				vs.beg_active_dt,
				vs.end_active_dt
			FROM 
				venue_synonym vs
			WHERE
				vs.venue_id = '".$row['venue_id']."'";
	$results_2 = mysql_query($query_2) or die(mysql_error());
	$vs=0;
	while($row = mysql_fetch_array($results_2))
	{
		$venues['synonyms'][$v]['venue_synonym_id'][$vs] 	= $row['venue_synonym_id'];
		$venues['synonyms'][$v]['venue_id'][$vs]			= $row['venue_id'];
		$venues['synonyms'][$v]['mnemonic'][$vs] 			= $row['mnemonic'];
		$venues['synonyms'][$v]['primary_ind'][$vs] 		= $row['primary_ind'];
		$venues['synonyms'][$v]['active_ind'][$vs] 			= $row['active_ind'];
		$venues['synonyms'][$v]['beg_active_dt'][$vs] 		= $row['beg_active_dt'];
		$venues['synonyms'][$v]['end_active_dt'][$vs] 		= $row['end_active_dt'];
		$vs++;
	}
	$v++;
}

bpat1434

You could really save yourself teh double looping by using a single query:

SELECT 
    v.venue_id, v.venue_type_id, v.seating_capacity, v.active_ind, v.beg_active_dt,
    v.end_active_dt, v.city, v.state_country_id,
    vs.venue_synonym_id, vs.mnemonic, vs.primary_ind, vs.active_ind,
    vs.beg_active_dt, vs.end_active_dt
FROM venue v
INNER JOIN venue_synonyms USING(venue_id)
WHERE v.active_ind = 1
    AND v.venue_id = 365
ORDER BY v.venue_id ASC

That should give you one dataset. Then when you call [man]mysql_fetch_assoc/man you will have an associative array of all the information for that row. Take the "multidimensional" out, so you're dealing with something much easier.

scambro

bpat1434 wrote:
You could really save yourself teh double looping by using a single query:
SELECT 
    v.venue_id, v.venue_type_id, v.seating_capacity, v.active_ind, v.beg_active_dt,
    v.end_active_dt, v.city, v.state_country_id,
    vs.venue_synonym_id, vs.mnemonic, vs.primary_ind, vs.active_ind,
    vs.beg_active_dt, vs.end_active_dt
FROM venue v
INNER JOIN venue_synonyms USING(venue_id)
WHERE v.active_ind = 1
    AND v.venue_id = 365
ORDER BY v.venue_id ASC
That should give you one dataset. Then when you call [man]mysql_fetch_assoc/man you will have an associative array of all the information for that row. Take the "multidimensional" out, so you're dealing with something much easier.

I had that previously, but I think I was trying to get one set of data for the primary information, and one set for the secondary for some reason. Ha, I don't even remember why I got rid of that now, but there was a reason...

Besides that, is this how you would typically get data into a multidimensional array?

Thanks!

bpat1434

There's no "typical" way to get data into a multidimensional array. However you feel best makes sense to you is the best way for that situation. I can't say one way is better than the other.

One common way to get a multi-dimensional array of the rows returned via mySQL query is something like this:

<?php
$query = "SOME WEIRD SELECT QUERY LIMIT 0, 100";
$result = mysql_query($query);

$dataset = array(); // Will hold all our data returned from DB
$i = 0; // Just a counter

while($row = mysql_fetch_assoc($result))
  $dataset[$i++] = $row;

mysql_free_result($result);
?>

Then, $dataset[0] would be the first row, $dataset[1] the next, so on and so forth. But that's really superfluous since you're just recreating the object from mySQL in array form.

NogDog

Based on a possibly shaky understanding of what you want to accomplish, I would probably set up the array as:

array (
   $venue_id => array (
      'venue_type_id' => $foo,
      'seating_capacity' => $bar,
      // etc....
      'synonyms' => array(
         $venue_synonym_id => array(
            'mnemonic' => $foo,
            'primary_ind' => $bar,
            // etc....
         )
      )
   )
)

bpat1434

My thing is, from the looks of it, it seems they're only pulling one record out (venue_id 365) so one query to return one large result-set should suffice. But since they never stated exactly how it will change (if at all) or how it's going to be used, it's difficult to say the best solution to how the array should be created.

For example.....
I'm working on an open-source program that is closed-source right now. In the calendar functions, an array was used to hold events for the current month. I was tasked with adding recurring events to the calendar. To do this, I had to change the array from being
$array[event_id] = array( title, date, time, location, info)
to
$array[day_number] = array(event_id => array(title, time, location, info), event2_id => array(title, time, location, info))

There are multiple ways to solve the issue. But without seeing the array in use, or an explanation of how it is going to be used, or even what the goal is, there are a multitude of ways to set up the array.

But your way makes perfect sense Nog.

scambro

NogDog wrote:

Based on a possibly shaky understanding of what you want to accomplish, I would probably set up the array as:

array (
   $venue_id => array (
      'venue_type_id' => $foo,
      'seating_capacity' => $bar,
      // etc....
      'synonyms' => array(
         $venue_synonym_id => array(
            'mnemonic' => $foo,
            'primary_ind' => $bar,
            // etc....
         )
      )
   )
)

Thanks, I think that looks more straightforward than what I was trying to do, I'll try to work something around like that.

My thing is, from the looks of it, it seems they're only pulling one record out (venue_id 365) so one query to return one large result-set should suffice. But since they never stated exactly how it will change (if at all) or how it's going to be used, it's difficult to say the best solution to how the array should be created.

Yeah - sorry about that, I was just doing that id=365 part for testing because I knew that one had lots of sub-mnemonics in my db, should have taken it out when I posted the code for it. Really my goal at this point was just to better understand how to work with arrays more. I have in the past with PHP, but usually at a very basic level to move data between MySQL and the user. The information you guys have posted has been helpful, thanks for the help!