help with multidimensional array 1

[mThomas]

I have a mssql query which returns the following:

state name category quantity freshness
New York Ange Frey On-Line Researcher 2 181
New York Casey Cregg Governmental Rptr 61 30
New York Casey Cregg Governmental Rptr 100 60
New York Casey Cregg Governmental Rptr 36 90
New York Casey Cregg Governmental Rptr 104 180
New York Casey Cregg Governmental Rptr 129 181
New York Chris Schroer Copy Editor 1 180
New York Chris Schroer Copy Editor 15 181
New York Danielle Bikowsky Senior Editor 8 30
New York Danielle Bikowsky Senior Editor 12 60
New York Danielle Bikowsky Senior Editor 4 90
New York Danielle Bikowsky Senior Editor 19 180
New York Danielle Bikowsky Senior Editor 55 181
New York Dea Parody Governmental Rptr 153 30
New York Dea Parody Governmental Rptr 172 60
New York Dea Parody Governmental Rptr 26 90
New York Dea Parody Governmental Rptr 124 180
New York Dea Parody Governmental Rptr 75 181

The result set is reprsents:

State name category quantity freshness

I want to convert the data to read the freshness as columns and place the quantity under each corresponding freshness so for the above result set I would end u with:

state name category 30 60 90 180 181
New York Ange Frey On-Line Researcher 2
New York Casey Cregg Governmental Rptr 61 100 36 104 129
New York Chris Schroer Copy Editor 180 1 15
New York Danielle Bikowsky Senior Editor 8 12 4 19 55
New York Dea Parody Governmental Rptr 153 172 26 124 75
New York Ange Frey On-Line Researcher 2
New York Casey Cregg Governmental Rptr 61 100 36 104 129
New York Chris Schroer Copy Editor 180 1 15
New York Danielle Bikowsky Senior Editor 8 12 4 19 55
New York Dea Parody Governmental Rptr 153 172 26 124 75

Here is the logic I've been trying:

Using a query to get the distinct names, I inserted that into an array.

Then I used a foreach loop to go through each value in the array. Within the foreach loop I used a while to go through the query wich produced the first result set. Here is a summary of what I'm trying so far:

create name array

foreach name array value
while fetch_row
if row == value
do something

Here is the test code:

[COLOR=red]// read the query and pass the values to an array[/color]

while ($row = mssql_fetch_row($reporters)) {

$array[] = $row[0];

}
[COLOR=red]//initials some values[/color]
$fresh30 = "";
$fresh60 = "";
$fresh90 = "";
$fresh180 = "";
$fresh181 = "";
$count = -1;

[COLOR=red]// loop thourgh the array[/color]
foreach($array as $value) { 
$count = $count + 1;
        [COLOR=red]// loop thourgh the result set[/color]
	while ($rep_row = mssql_fetch_row($newyork)) {
	                [COLOR=red]// if values match[/color]
			if($rep_row == $value) {
			
			if($row[4] == "30"){ $fresh30 = $row[3]; }
			if($row[4] == "60"){ $fresh60 = $row[3]; }
			if($row[4] == "90"){ $fresh90 = $row[3]; }
			if($row[4] == "180"){ $fresh180 = $row[3]; }
			if($row[4] == "181"){ $fresh181 = $row[3]; }
			[COLOR=red]// append tab delimited the freshness values[/color]
			$array[$count] .= "\t$fresh30\t$fresh60\t$fresh90\g$fresh180";
			
			[COLOR=red]// clean out the values ready for thenext iteration[/color]
			$fresh30 = "";
			$fresh60 = "";
			$fresh90 = "";
			$fresh180 = "";
			$fresh181 = "";
			
			}				
	}
}

Then I've tried reading/echoing some of the array values but it just isn't working. For the most part I dont get an error. I get nothing. When I echo the name arry I get the values. When I echo the query I get the result set.

I hope I've made it clear enough to understand what I want to do. In summary I want to use the last column of the result set as column headers and place in each of those columns the values from the second to last column so starting with:

[state] [name] [category] [quantity] [freshness]
florida mike reporter 1 30
florida mike reporter 20 60
florida mike reporter 0 90
florida mike reporter 5 180
florida mike reporter 50 181
texas jim reporter 1 181


I would end up with:

Note: I've use the brackets to represent columns.

[state] [name] [category] [30] [60] [90] [180] [181]
florida mike reporter 1 20 0 5 50
texas jim reporter 0 0 0 0 1

I think the solution is in a multidimensional array, but I'm not sure.

tia...mike

 

[DRJ478]

You just need to create new elements in the multi-dimensional array:

# iterate
while ($row = mysql_fetch_assoc($result)){
   # now set the value
   $final[$row['state']][$row['name']][$row['category']][$row['freshness']] = $row[quantity];
}
print_r($final);

 

[mThomas]

With a little manipulation it worked. Thank you ever so much. It is a beautiful thing!!

mike