Problems creating a table layout for a search result and counts

[JimChuD]

Hi,
I'm trying to create a sort of Pivot table (like you would in excel)
it gets the top columns then the totals and then the for each rows.

the problem is for each row i have to count the number of results for that row and then after it finds those results it has to then put those in the right place along there...

however i cant work out how to do it properly.
the i have it at current is for each count then roll through the discrepancy array and then if it finds it count the total and then add it along. however if thre are 10 columns wide and it finds 3 discrepancies it obviously creates 30 columns and if i dont have the else it just puts it in the first places it finds it...

im totally stuck.
if you can help me please i would greatly appreciate it.

Regards
Jim
[code]<?
# GET ALL SUPPLIERS LIMITED TO 10 RESULTS
$query_sup = "SELECT id, supname FROM suppliers ORDER BY supname LIMIT 10";
$result_sup = mysql_query($query_sup);

# GET ALL DISCREPANCIES
$query_discrep = "SELECT id, discrepancy FROM discrepancies ORDER BY discrepancy";
$result_discrep = mysql_query($query_discrep);
$totalrows_discrep = mysql_num_rows($result_discrep);

#GET START OF TABLE AND ALL DISCREPANCIES - CREATE TABLE HEADERS AND ALSO SETUP DISCREP ARRAY
echo ' <table>
<tr>
<td>Supplier</td>';

$discrepancy_array = array();
while ($discrep = mysql_fetch_array($result_discrep)) {
echo ' <td>'.$discrep['discrepancy'].'</td>';
$discrepancy_array[] = $discrep['id'];
}

echo ' </tr>';

# FOR EACH SUPPLIER
while ($sup = mysql_fetch_array($result_sup)) {
echo ' <tr>
<td>'.$sup['supname'].' - '.$sup['id'].'</td>';

# GET THE COUNTED DISCREPANCIES
$query_count1 = "SELECT lodis.disrep1, COUNT(*) as counted FROM lodis Inner Join goodsin ON goodsin.id = lodis.giid WHERE gisupid = {$sup['id']} GROUP BY disrep1";
$result_count1 = mysql_query($query_count1);

# FOR EACH DISCREPANCY FOUND
while ($count1 = mysql_fetch_array($result_count1)){

$discrep_column_total = 0;
foreach ($discrepancy_array AS $discrepancy) {

if ($count1['disrep1'] == $discrepancy) {
$discrep_column_total = $discrep_column_total + $count1['counted'];
$row .= "<td>{$discrep_column_total}</td>";
} else {
$row .= "<td>{$discrep_column_total}</td>";
}
}
echo $row;
}
echo "</tr>";
}
?>[/code]

[Psycho]

Could you give a brief example using "test" data to illustrate what you are trying to accomplish? I.e. show the data as it is returned from the database and then show it how you want it displayed.

[Mattyspatty]

its hard to understand exactly what you mean, i [b]think[/b] i understand...  are you trying to order the suppliers by number of discrepancies in a <table> and in an adjacent table row have various bits of data?

[JimChuD]

This is how i wanted it.


[table][tr][td]Supplier[/td][td]Discrepancy1[/td][td]Discrepancy2[/td][td]Discrepancy3[/td][td]Discrepancy4[/td][td]Discrepancy5[/td][/tr][tr][td]Supplier Name[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][/tr][tr][td]Supplier Name[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][/tr][tr][td]Supplier Name[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][td]Discrep count total[/td][/tr][/table]

however obviously with the count the <td>'s for the discrep count totals are either more or less and the formatting off.
Thanks for the prompt response
Regards

[JimChuD]

as you can see above as the count loops it should basically add the variables in to the relavent places if there are 12 discrepancy2 then under discrepancy2 it should show 12 and all thhe rest should show 0 or blank.
However due to the count loop at current if there are 2 different discrepancies instead of showing thm in the right places it shows 10 discrepancies and not all in the right place.

[JimChuD]

*bump*