SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Grouping records

    I was playing around yesterday with a small page I built to keep track of lap times for my slot cars (go ahead, laugh). I made a table with columns for car name, car time, and modifications. I was able to throw some PHP together to make it display a table. No problem.

    Also on the page I have a list of my cars, grouped by brand. It looks something like this:

    Code HTML4Strict:
    <h3>Brand</h3>
    <ul>
    <li>car name</li>
    <li>car name</li>
    <li>car name</li>
    </ul>
    and so on, for each mfgr.

    After I thought about it, I realized I could pull this list out of the database as well, so I added another column for brand, and a column for the filename of the picture of the car. I tried to build the list using PHP, and I got close, but there's something I'm not grasping.

    Here's what I have so far:

    Code PHP:
    <?php 
    	$db = mysql_connect("", "my_dbuser", "my_dbpass");
             if (!$db)
                     {
                     echo mysql_error(); exit;
                     }
     
    	mysql_select_db("my_dbname", $db);
     
    	$result = mysql_query("SELECT car_brand, car_name FROM times ORDER BY car_brand, car_name ASC");
            $set = array();
            while ($record = mysql_fetch_object($result)) {
            $set[$record->car_brand][] = $record;
            }
            foreach ($set as $car_brand => $records) {
               echo "<h3>{$car_brand}</h3>\n";
               echo "<ul>";
            foreach ($records as $record) {
            echo "\n\t<li>{$car_name}</li>\n";
            }   
            echo "</ul>\n";
    }
    ?>

    This gets me all the brands, but with a list of empty bullets.

    If this were ColdFusion, I could do this with the GROUP attribute of <cfoutput>, but I'm not so good with PHP, so a little guidance would be helpful. Or maybe I'm going about it completely wrong?

  2. #2
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps this will nudge you in the right direction
    Code:
    $set[$record->car_brand][] = $record->car_name;
    Edit: Still may not work as you intended. For debugging after this I would suggest getting used to the following code:

    Code PHP:
    echo '<pre>';
    print_r($set);
    echo '</pre>';

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should work.
    PHP Code:
    $result mysql_query("SELECT car_brand, GROUP_CONCAT(car_name ORDER BY car_name ASC) AS car_names FROM times ORDER BY car_brand ASC");

    while (
    $record mysql_fetch_object($result)) {
    echo 
    "<h3>".$record->car_brand."</h3>\n";
    echo 
    "<ul>\n";
    $cars explode(',',$record->car_names);
    foreach(
    $cars as $thecar) {
     echo 
    "\t<li>".$thecar."</li>\n";
    }
    echo 
    "</ul>\n\n";


  4. #4
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've got a lot of bouncing back and forth between foreach loops. There's a much simpler way to accomplish the output.

    You start by turning $result into an array -but you can use mysql_fetch_assoc.

    I would start using that function with your while statement:

    Code:
    while ($row = mysql_fetch_assoc($result)) {
    
    }
    Because you're ordering your results first by brand, I would create away to determine each time you've got a new brand (to create a header):

    Code:
    if ($row['car_brand'] != $lastbrand){
        echo "<h3>".$row['brand']."</h3>";
    }
    $lastbrand = $row['brand'];
    Then just add the output for the car name. Final product looks like this:

    Code:
    while ($row = mysql_fetch_assoc($result)) {
        if ($row['car_brand'] != $lastbrand){
            echo "<h3>".$row['car_brand']."</h3>";
        }
        $lastbrand = $row['brand'];
        echo $row['car_name']."<br>";
    }
    Let me know if you have any more troubles (or if you need anything explained).
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  5. #5
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha. Solution is above too! I had to step away from my computer (at work). spiderling beat me to it!
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks for the replies. It's amazing how many ways there are to accomplish the same result. I need to study the differences and learn.

    spiderling's method:

    car_brand1
    • car_name
    • car_name
    • car_name
    • car_name
    • car_name
    • car_name


    I get only one of the brands, but then all the cars under it, regardless of brand.


    jeffvdovjak's method:

    car_brand1

    car_name

    car_brand1

    car_name

    car_brand2

    car_name

    car_brand2

    car_name


    Desired result:

    car_brand1
    • car_name
    • car_name
    • car_name
    • car_name



    car_brand2
    • car_name
    • car_name
    • car_name
    • car_name



    This does get me a lot closer though, thanks. I'm going to fiddle with it and see what I can come up with. I like fiddling!

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by busy View Post
    Edit: Still may not work as you intended. For debugging after this I would suggest getting used to the following code:

    Code PHP:
    echo '<pre>';
    print_r($set);
    echo '</pre>';
    Good tip! That actually displays things the way they're supposed to look, just not formatted.

    Example:

    Code:
        [Fly] => Array
            (
                [0] => Dodge Viper GTS
                [1] => Ferrari F40 (Time)
                [2] => Ford Capri RS Turbo (Pentosin)
                [3] => Marcos LM600 10th Anniversary
                [4] => Panoz LMP
                [5] => Porsche 908
            )
    
        [Ninco] => Array
            (
                [0] => Ascari (Hanscan)
                [1] => BMW LM V12
                [2] => Mosler MT900R (Gulf)
            )
    Now I just need to figure out how to get the array items to show properly.

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Got it!!

    Code PHP:
    foreach ($records as $record) {

    needed to be:

    Code PHP:
    foreach ($records as $car_name) {

    With the addition of busy's change.

    Woohoo! Thanks.

  9. #9
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Weird. I tested my results on my server (set up a mock database with 3 entries under 2 different brands) and I got:

    brand
    name
    name
    name

    brand
    name
    name
    name

    Did you copy and paste or make sure you had the $lastbrand = $row['brand']; line?
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  10. #10
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jeffvdovjak View Post
    Did you copy and paste or make sure you had the $lastbrand = $row['brand']; line?
    Yep, sure did. Odd, huh?

    Now I have created a new problem for myself. I want to pull the car_id (primary key, autonumber) out of the database as well, so I can turn each list item into a link to a page where I can pull a pic and description in.

    But just adding car_id to the query and changing the code as such doesn't work:

    Code PHP:
    foreach ($records as $car_name) {
       echo "\n\t<li><a href=\"car.php?car={$car_id}\">{$car_name}</a></li>";
    }

    I think I might have to change the code above.

    My current code looks like this:

    Code PHP:
    	$result = mysql_query("SELECT car_id, car_brand, car_name FROM cars ORDER BY car_brand DESC, car_name ASC");
            $set = array();
            while ($record = mysql_fetch_object($result)) {
            $set[$record->car_brand][] = $record->car_name;
            }
            foreach ($set as $car_brand => $records) {
               echo "<h3>{$car_brand}</h3>\n";
               echo "<ul>";
            foreach ($records as $car_name) {
            echo "\n\t<li><a href=\"car.php?car={$car_id}\">{$car_name}</a></li>";
            }   
            echo "\n</ul>\n";
    }

    My link looks like this: car.php?car=

    Hrm...

  11. #11
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is you're not assigning $car_id.

    In your while loop, you need to specify what you're doing with $record->car_id for each row.

    Perhaps something along these lines:
    Code php:
    while ($record = mysql_fetch_object($result)) {
        $set[$record->car_brand][] = $record->car_name;
        $ids[$record->car_brand][] = $record->car_id;
            }

    Then to get the records:
    Code php:
    foreach ($records as $key => $car_name) {
        echo "<li><a href=\"car.php?car={$ids[$car_brand][$key]}\">{$car_name}</a></li>";
    }

    Personally I like to do this kind of stuff in one array, but it would need to be organized differently. I like array structures more like this:
    Code php:
    $brands[$brand_id]['brand_name']=$brand_name;
    $brands[$brand_id]['cars'][$car_id]=$car_name;

    You can work with something like that WAY easier, and id numbers make great array keys.

  12. #12
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by busy View Post
    The problem is you're not assigning $car_id.

    In your while loop, you need to specify what you're doing with $record->car_id for each row.

    Perhaps something along these lines:
    Code php:
    while ($record = mysql_fetch_object($result)) {
        $set[$record->car_brand][] = $record->car_name;
        $ids[$record->car_brand][] = $record->car_id;
            }

    Then to get the records:
    Code php:
    foreach ($records as $key => $car_name) {
        echo "<li><a href=\"car.php?car={$ids[$car_brand][$key]}\">{$car_name}</a></li>";
    }
    Ok, the first part I tried, but I was implementing that bottom part wrong. At least I was half right!


    Quote Originally Posted by busy View Post
    Personally I like to do this kind of stuff in one array, but it would need to be organized differently. I like array structures more like this:
    Code php:
    $brands[$brand_id]['brand_name']=$brand_name;
    $brands[$brand_id]['cars'][$car_id]=$car_name;

    You can work with something like that WAY easier, and id numbers make great array keys.
    Maybe that will be lesson 2 someday.

  13. #13
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is weird. I just tested it and it worked.

    I received your desired result.
    car_brand1

    * car_name
    * car_name
    * car_name
    * car_name



    car_brand2

    * car_name
    * car_name
    * car_name
    * car_name
    I tweaked the query slightly by adding GROUP BY car_brand, since it should be used when using GROUP_CONCAT.

    PHP Code:
    $result mysql_query("SELECT car_brand, GROUP_CONCAT(car_name ORDER BY car_name ASC) AS car_names FROM times GROUP BY car_brand ORDER BY car_brand ASC"); 
    Last edited by spiderling; Mar 31, 2009 at 13:07.

  14. #14
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    : Holy crap guys! I never knew about GROUP_CONCAT() - I just looked it up and that looks incredibly useful!!!!

    This is definitely the best thing I learned today. I'm so excited I think I peed a little.

  15. #15
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, I have posted each of the versions here:

    @spiderling: Yours is the first version, as it didn't like the second version at all (and yep I did make sure the table name was correct).

  16. #16
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about the table name. I also noticed that I included the $link variable, which you don't have, and I forgot the semicolon at the end of the line. Out of curiosity, what version of MySQL are you running?

    I edited the entry with the corrections.

  17. #17
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spiderling View Post
    Sorry about the table name. I also noticed that I included the $link variable, which you don't have, and I forgot the semicolon at the end of the line. Out of curiosity, what version of MySQL are you running?

    I edited the entry with the corrections.
    I pulled out the $link variable and your version works now (I'd already caught the semicolon).

    My versions are as follow:

    MySQL: 5.0.67-community
    PHP: 5.2.8


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
  •