thought process went astray with arrays

[dmacman1962]

I am having a heck of a time trying to get 2 arrays of info into something I can work with. I have checked my past posts and others and still cannot seem to solve this.

I have several tables with states and video counts selected for each state. What I am trying to end up with looks like this. (arrays.png)
http://www.izzit.org/arrays.png

I have 1 query with all the info (which I could not even get close to what I want).

SELECT s.Id, s.StateName AS State,
Count(*) AS FAV_Count
from table1 as f
	join table2 as a on f.ContactId = a.ContactId
	join table3 as s on a.State = s.StateAbbreviation
	JOIN table4 as i ON f.ItemId = i.Id
WHERE date(f.DateAdded) BETWEEN '2011-05-08' and '2011-05-14'
GROUP BY a.State, f.ItemId
ORDER BY a.State, i.SKU

This gets all the data I need to make my table(s).
(arrays2.bmp)
http://www.izzit.org/arrays2.bmp

With this query, I would like to iterate thru with a foreach and build the table (I already have the 1st row as a separate table, with the video SKU's [CC-VMD, CE-FPTP, etc]).

I just need to get the state abbreviation, then display the video count below each of the videos.

I would be OK with 2 separate queries and tables. 1st for the states on the left, and 2nd for the video counts. I can then iterate thru and build both tables.

Thanks for the help,
D

[NogDog]

If I'm following you correctly (not a given 😉 ), I think you could do something like:

$result = mysql_query($query);
$data = array();
while($row = mysql_fetch_assoc($result)) {
   $data[$row['State']][$row['Id'] = $row['FAV_Count'];
}

Then after creating your table header row, the content rows would be something like:

foreach($data as $state => $counts) {
   echo "<tr><td>$state</td>";
   foreach($counts as $count) {
      echo "<td>$count</td>";
   }
   echo "</tr>\n";
}

This assumes that your ORDER BY clause will ensure that the order of the counts will match the order you want them in the table (horizontally) and that there will be a count for each SKU. If not, then you could create an array of SKU ID's, and use that to drive the inner loop. (In this example, it's assumed you've already populated array $skuIdArray with the ID's in the proper order.)

foreach($data as $state => $counts) {
   echo "<tr><td>$state</td>";
   foreach(skuIdArray as $id) {
      printf("<td>%s</td>", (isset($counts[$id])) ? $counts[$id] : 0);
   }
   echo "</tr>\n";
}

[dmacman1962]

We're getting closer.

Here are the results from your 1st foreach. It is just getting the last value from the array.
Your foreach loop results.

If I run thru and get all the data from my query and echo all the data, I get this.
my full query results.

Here is a manual table I made up from the 1st 2 rows of data from the full query (what I need to get with your query).
demo table results.

I couldnt try your 2nd foreach because I wasnt sure how to get the data for the array.

Any ideas?

D