SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group Results by data field ...

    Code:
    Hello, I am having problems with grouping records while showing the group data only once.  I am trying to get them in a html table that would look like:
    
    +------------------------+----------------------+------------------------+
    | tblTable1.fldField1    | tblTable1.fldField2  | tblTable1.fldField3    |
    +------------------------+----------------------+------------------------+
    | 12653                  | PBXL                 | 396.52                 |
    |                        | ABCD                 | 426.28                 |
    |                        | GEDL                 | 385.20                 |
    +------------------------+----------------------+------------------------+
    | 12654                  | ABCD                 | 515.18                 |
    |                        | FGHL                 | 520.00                 |
    +------------------------+----------------------+------------------------+
    | 12659                  | PBXL                 | 612.35                 |
    |                        | FGHL                 | 619.20                 |
    |                        | XTRL                 | 420.39                 |
    +------------------------+----------------------+------------------------+
    
    
    <?php
    //
    require("connect.php");
    //
    $mydata101 = "SELECT tblTable1.fldField1 as 'group', tblTable1.fldField2, tblTable1.fldField3 as 'name' FROM tblTable1 GROUP BY tblTable1.fldField1";
    $mydata102 = mysql_query($mydata101);
    //
    //
    $group = null;
    while($row = mysql_fetch_array($mydata102))
       {
            if($row['group'] != $group)
            {
                echo $row['group'];
                $group = $row['group'];
    //
                $row['name'];
    //
            }
    //
        } 
    ?>
    
    
    Any help would be great appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    can't help you with the php, but in your query, GROUP BY should be ORDER BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Say you follow r937s advice, and also change your sql slightly so that you have:
    PHP Code:
    "SELECT tblTable1.fldField1 as Group, tblTable1.fldField2 as Acronym, tblTable1.fldField3 as Value FROM tblTable1 ORDER BY tblTable1.fldField1"
    Does that give you an array looking something like this?

    PHP Code:
    var_dump$row) ;

    array(
    => array(
    'Group'=> 12659,
    'Acronym'=> 'PBXA',
    'Value'=> 123.05
    ),

    // and so on

    ); 
    If so, is your question still how do I get this to display in a table?
    Is it important that either the value or the Acronym be in any descending order too?

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <?php
    
    require("connect.php");
    
    $query = "SELECT  help.field1 , help.field2, help.field3 from help order by help.field1";
    
    $result = mysql_query($frmritebids2101) or die(mysql_error());
    
    $first_written = false;
    if(mysql_num_rows($result) > 0)
    {
            echo "<table class='result'  border='1'>";
            while($row = mysql_fetch_assoc($result))
            {       
                    echo "<tr>";
                    if(!$first_written || $first_written != $row['field1'])
                    {
                            echo "<td>{$row['field1']}</td>";
                            $first_written = $row['field1'];
                    }
                    else
                    {
                            echo "<td></td>";
                    }
                    echo "<td>{$row['field2']}</td>";
                    echo "<td>{$row['field3']}</td>";
                    echo "</tr>";
            }
            echo "</table>";
    }
    ?>


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
  •