SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    stuck on simple count & format

    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

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    SELECT item, color, COUNT(*) FROM table GROUP BY item, color

  3. #3
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That's correct, that's all the information you need to display your table.

  5. #5
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


    Quote Originally Posted by Dan Grossman View Post
    That's correct, that's all the information you need to display your table.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    <?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>';
    ?>

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by menuserve
    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 Code:
    <?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>';
    ?>

  10. #10
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  11. #11
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •