PHP/Grouping mySql results

[jdarin]

 

PHP version 5.2.5

MySQL version 4.1.22-standard

 

Hi, I need help with grouping results from mysql database.

 

Say this is my sample database:

--- *** SAMPLE DATABASE *** ----

Brand    Model    Year    Color    Price

Ford    F150    2005    Red    15000

Ford    F150    2006    Blue  17000

Ford    F250    2003    Silver    25000

Ford    F250    2004    White    26000

Ford    F250    2007    Red    27000

Chevy    1500    2005    Red    15000

Chevy    2500    2006    Blue    28000

Chevy    3500    2003    Silver    21000

Chevy    2500    2005    Black    22000

----------------------------------------------

 

Ok, I want Brand/Model grouped together in queries.  Also I dont want to just "singularly/distinctly" group by model number because sometimes different Brands have the same model name/number, (say Ford has a car called "Model C" and Chevy had a car called "Model C", i would not want Model C (from different brands) grouped together) i would want Ford Model C and Chevy Model C grouped separately.

 

Perhaps I need to use Sub-selects??

 

for example, someone queries entire database for all results

results are sorted like this (note brands ordered alphabetically, then each model for brand ordered chronologically, then under each particular model, years ordered...)

 

--- *** WHAT I WANT  *** -----

Chevy    1500   

        1) 2005    Red    15000

Chevy    2500   

        1) 2005    Black    22000

        2) 2006    Blue    28000

Chevy    3500   

        1) 2003    Silver    21000

Ford F150

        1) 2005    Red    15000

        2) 2006    Blue  17000

Ford    F250   

        1) 2003    Silver    25000

        2) 2004    White    26000

        3) 2007    Red    27000

---------------------------------

 

currently people can query by brand (which works correctly) but i want to group by model for each brand. also sometimes people want to search just year and i want to display all brands and models for that year, but group by brand/model..

 

current php/sql code is along the lines..

$sql = "select * from inventory WHERE `inventory`.`brand` LIKE '$querybrand' AND `inventory`.`year` LIKE '$queryyear'";

/* I select * because i actually have other information about cars i display, like "Notes", "DiscountInfo", etc */

$query = mysql_query($sql);

$rowcount = mysql_num_rows($query);

 

then displaying results using:

while ($row = mysql_fetch_array($query)) {

/* actually use tables here, but i've edited this for example of how i display */

echo $row['brand']." ";

echo $row['year']." ";

echo $row['color']." ";

echo $row['price']."<br />";

}

 

This displays the correct results with proper WHERE statements in place, i just want to be able to group as i listed above.

 

Do i need to use DISTINCT or GROUP by, or HAVING, or do i need to be executing subqueries?

 

Any and all help would be appreciated, thanks

J. Darin

[aschk]

You DON'T want GROUP BY or any other grouping functionality. These should ONLY be used for function aggregations (i.e. average, summation, count). I think your understanding of what GROUP BY does isn't quite correct.

 

Thus, the layout you have specified MySQL will be unable to give you. The best you can do is use the ORDER BY clause and order the results as you want.

 

$sql = "SELECT * 
FROM inventory 
WHERE `inventory`.`brand` LIKE '$querybrand' 
AND `inventory`.`year` LIKE '$queryyear'
ORDER BY Brand, Model, Year, Color, Price
";

 

Bear in mind however, this will give you back the resultset you already have (although the one you had before had no guaranteed ordering).

 

Any "grouping" as you call it will need to be done by PHP.

[jdarin]

Well can anyone help me figure out the PHP to group the results as I would want?

 

See, I was thinking: First query for DISTINCT Brand/Models, and WHILE querying the distinct brand/model, execute a "subquery" for the results of each brand/model.

 

I need to find a way to group the results.

 

Any suggestions?

 

i'm not sure if this would bog down or not for possible 1000's entries, or if it would work..

 

---- POSSIBLE SOLUTION CODE? --------------------

 

$sql = "SELECT DISTINCT(brand,model)

            FROM inventory

            WHERE `inventory`.`brand` LIKE '$querybrand'

            AND `inventory`.`year` LIKE '$queryyear'

            ORDER BY `inventory`.`brand`,`inventory`.`model`";

 

/* HOW TO Correctly use DISTINCT here?? remember, i want Ford "model c" and Chevy "model c" to be distinct*/

 

$query = mysql_query($sql);

 

while ($row = mysql_fetch_array($query)) {

 

      echo $row['brand']." ";

      echo $row['model']."<br />";

 

 

      $subquerysql = "SELECT *

                              FROM inventory

                              WHERE `inventory`.`brand` LIKE '$row['brand']'

                              AND `inventory`.`model` LIKE '$row['model']'

                              AND `inventory`.`year` LIKE '$queryyear'

                              ORDER BY `inventory`.`year`,`inventory`.`color`";

      $subquery = mysql_query($subquerysql);

 

      while ($subqueryrow = mysql_fetch_array($subquery)) {

            echo "- ";

            echo $subqueryrow['year']." ";

            echo $subqueryrow['color']." ";

            echo $subqueryrow['price']."<br />";

 

      }

 

}

--- END code ----------------------------------------

[jdarin]

---- WOW prelimary testing is showing this as working.. note corrections in $sql (for distinct notation) and $subquerysql (for php quotations) --------------------

 

$sql = "SELECT DISTINCT brand,model

            FROM inventory

            WHERE `inventory`.`brand` LIKE '$querybrand'

            AND `inventory`.`year` LIKE '$queryyear'

            ORDER BY `inventory`.`brand`,`inventory`.`model`";

 

$query = mysql_query($sql);

 

while ($row = mysql_fetch_array($query)) {

 

      echo $row['brand']." ";

      echo $row['model']."

";

 

 

      $subquerysql = "SELECT *

                              FROM inventory

                              WHERE `inventory`.`brand` LIKE '".$row['brand']."'

                              AND `inventory`.`model` LIKE '".$row['model']."'

                              AND `inventory`.`year` LIKE '$queryyear'

                              ORDER BY `inventory`.`year`,`inventory`.`color`";

      $subquery = mysql_query($subquerysql);

 

      while ($subqueryrow = mysql_fetch_array($subquery)) {

            echo "- ";

            echo $subqueryrow['year']." ";

            echo $subqueryrow['color']." ";

            echo $subqueryrow['price']."<br />";

 

      }

 

}

-------------------------------------------------

[aschk]

This all makes me think that your table isn't normalised if you're GROUP'ing where you shouldn't need to be (i.e. model/brand).

I would also be careful about doing a mysql_fetch_array loop (with additional query) inside another mysql_fetch_array loop. I've had some extraneous results with this because the 2nd mysql_fetch* overwrites the 1st. Check and test it to be sure.

 

First thing to do, normalise your table(s)