stuck on simple count & format

[menuserve]

I have a DB that has the following table

ITEM COLOR
Hat Red
Hat Red
Hat Blue
Hat Orange
Belt Red
Belt Blue
Belt Blue
Belt Orange

I want to query and display results like this:
ITEM - Red - Blue - Orange
Hat - 2 - 1 - 1
Belt - 1 - 2 - 1

Any help is greatly appreciated

[Dan Grossman]

Formatting for display is the job of your application not the database. You should select all the data you need, then write the code to display it like that.

A useful query might be

SELECT item, color, COUNT(*) FROM table GROUP BY item, color

[menuserve]

Thanks for the query... My output always looks like this...

Item Color COUNT(*)
hat red 2
hat blue 1
hat orange 1
belt red 1
belt blue 2
belt orange 1

I need help coding the php part to make it look like this:

Item Red Blue Orange
hat 2 1 1
belt 1 2 1

Any help with this PHP part would be greatly appreciated

[Dan Grossman]

That's correct, that's all the information you need to display your table.

[menuserve]

Dan - you have me confused. I know it displays that table, however I'm having trouble reformatting the table so it just lists HAT and BELT once, with the COLOR and QTY in columns.

It should only be two rows, not three for each column.

That's correct, that's all the information you need to display your table.

[oddz]

<?php
$arrItems = array(
    array('Item'=>'hat','Color'=>'red','COUNT(*)'=>2)
    ,array('Item'=>'hat','Color'=>'blue','COUNT(*)'=>1)
    ,array('Item'=>'hat','Color'=>'orange','COUNT(*)'=>1)
    ,array('Item'=>'belt','Color'=>'red','COUNT(*)'=>1)
    ,array('Item'=>'belt','Color'=>'blue','COUNT(*)'=>2)
    ,array('Item'=>'belt','Color'=>'orange','COUNT(*)'=>1)
    
    ,array('Item'=>'hat','Color'=>'green','COUNT(*)'=>2)
    ,array('Item'=>'belt','Color'=>'yellow','COUNT(*)'=>8)
);


$arrPossibleColors = array();
$arrItemsToColors = array();


foreach($arrItems as $arrItem) {
    
    if(!in_array($arrItem['Color'],$arrPossibleColors)) {
        $arrPossibleColors[] = $arrItem['Color'];
    }
    
    $arrItemsToColors[$arrItem['Item']][$arrItem['Color']] = $arrItem['COUNT(*)'];
    
}

echo '<table>';
echo '<thead>';
echo '<tr>';
echo '<th>Item</th>';
foreach($arrPossibleColors as $strPossibleColor) {
    printf('<th>%s</th>',$strPossibleColor);
}
echo '</tr>';
echo '</thead>';
echo '<tbody>';
if(!empty($arrItemsToColors)) {
    foreach($arrItemsToColors as $strItem=>$arrItemToColor) {

        echo '<tr>';
        printf('<td>%s</td>',$strItem);
        foreach($arrPossibleColors as $strPossibleColor) {
            printf('<td>%u</td>',isset($arrItemToColor[$strPossibleColor])?$arrItemToColor[$strPossibleColor]:0);
        }
        echo '</tr>';
    }
} else {
    printf('<tr><td colspan="1">No Items Available</td></tr>');
}
echo '</tbody>';
echo '</table>';
?>

[Dan Grossman]

As oddz has just shown, it's just a matter of writing the code, which there are many ways to do.

You have all the data you want to display, that was the query's job. It's your job to write the code to display it the way you want to.

[menuserve]

Dan, yes I understand that... I guess my post was for the PHP help, not the query. It seems like you don't like my post. Sorry that I need the help.

ODDZ... thanks that works... but I see you manually coded the arrays, as to where i want them to come in from the SQL database table.

Ideally I'd like to be able to add something to the table, like a tie in all three colors and then have this page add it to the table. In your example I don't see where it pulls from the query results, but rather from the array coded on the page?

Sorry, I'm a newbie working off online tutorials. I built a page that lets me add to the inventory table and it works perfectly, its the pulling of and reformatting the results that I'm struggling with.

[oddz]

Ideally I'd like to be able to add something to the table, like a tie in all three colors and then have this page add it to the table. In your example I don't see where it pulls from the query results, but rather from the array coded on the page?

The code I provided you with supports that. Use the SQL Dan Grossman supplied you with to get the data from MySQL then use the PHP I supplied you with to display the data in your requested table format. You should also note that the PHP accounts for all colors that may exists even though an item may not be available in the color (shown by the last two array items).

<?php
$arrItems = array();
$arrPossibleColors = array();
$arrItemsToColors = array();

/**********************************************************************************/
/* Fetch data from MySQL */
$objResult = mysql_query('SELECT item, color, COUNT(*) FROM table GROUP BY item, color');
if($objResult && mysql_num_rows($objResult)) {
     while($arrRow = mysql_fetch_assoc($objResult)) {
          $arrItems[] = $arrRow;
     }
}

/**********************************************************************************/

foreach($arrItems as $arrItem) {
    
    if(!in_array($arrItem['Color'],$arrPossibleColors)) {
        $arrPossibleColors[] = $arrItem['Color'];
    }
    
    $arrItemsToColors[$arrItem['Item']][$arrItem['Color']] = $arrItem['COUNT(*)'];
    
}

echo '<table>';
echo '<thead>';
echo '<tr>';
echo '<th>Item</th>';
foreach($arrPossibleColors as $strPossibleColor) {
    printf('<th>%s</th>',$strPossibleColor);
}
echo '</tr>';
echo '</thead>';
echo '<tbody>';
if(!empty($arrItemsToColors)) {
    foreach($arrItemsToColors as $strItem=>$arrItemToColor) {

        echo '<tr>';
        printf('<td>%s</td>',$strItem);
        foreach($arrPossibleColors as $strPossibleColor) {
            printf('<td>%u</td>',isset($arrItemToColor[$strPossibleColor])?$arrItemToColor[$strPossibleColor]:0);
        }
        echo '</tr>';
    }
} else {
    printf('<tr><td>No Items Available</td></tr>');
}
echo '</tbody>';
echo '</table>';
?>

[menuserve]

oddz... thank you very much for the help.

i'm going to work on what you provided and integrate that into the code of my page... while using it as a learning experience for myself!

I've been working off PHP and MySQL for dummies... that only gets me so far!

Lets keep our fingers crossed!

[menuserve]

oddz... I get this code to run without error... however it says that no Items are found.

I'm not sure how this is since I'm able to pull the table shown above in this thread.