I have a products website. I have a table for product & another for makers where maker of products can be added.

MAKERS TABLE
ID MAKER
1 Nissan
2 Toyota
3 Audi
4. Honda

PRODUCTS TABLE
ID PRODUCT MAKER_ID PRICE
1 2000 Corolla 2 5000
2 2010 CRV 2 4500
3 2007 Element 4 5800
4 2005 A4 3 4200
5 2009 Infinity 1 8000
6 2005 Corolla 2 7000
7 2012 A7 3 9000

On search Page, product whose prices are above 4500 was searched

<?php $sql = "SELECT * FROM products WHERE price > 4500"; ?>

ID PRODUCT MAKER_ID PRICE
1 2000 Corolla 2 5000
3 2007 Element 4 5800
5 2009 Infinity 1 8000
6 2005 Corolla 2 7000
7 2012 A7 3 9000

Then, I want to group this product according to makers alphabetically, then I did this

$get = mysql_query("SELECT * FROM `products` GROUP BY `maker_id` ORDER BY MAKER_ID");

while($row=mysql_fetch_array($get)){    
$mid = stripslashes($row[maker_id]);

Then I tried to use the $mid to get the maker name from the MAKER TABLE

$sql = mysql_query("SELECT maker FROM makers WHERE id='$mid'");
$roll = mysql_fetch_array($sql);
echo $roll[maker];

}

The echoed result is;

TOYOTA
NISSAN
AUDI
HONDA

But I want the result to be alphabetical, that is;
AUDI
HONDA
NISSAN
TOYOTA

Pls what do I do?

try adding ORDER BY maker on you query...

I did but still didn't work

Member Avatar
diafol

You're running 3 queries instead of one...

SELECT m.maker, p.* FROM maker AS m INNER JOIN products AS p ON m.maker_id = p.maker_id WHERE p.price > 4500 ORDER BY m.maker, p.product

I think

Thanks friend, i got a better and easier query

//From http://www.w3resource.com/sql/joins/joining-with-group-by-and-order-by.php

$get = mysql_query("SELECT products.id, products.manufacturer, manufacturers.maker,  
SUM(manufacturers.id)  
FROM products,manufacturers  
WHERE products.manufacturer=manufacturers.id  
GROUP BY products.manufacturer  
ORDER BY manufacturers.maker ASC");



while($row=mysql_fetch_array($get)){    
$id = stripslashes($row[id]);
$name = stripslashes($row[name]);
$manu = stripslashes($row[manufacturer]);
$maker = stripslashes($row[maker]);
Member Avatar
diafol

WHat's this for?

SUM(manufacturers.id)  

got a better and easier query

Better? Easier? Erm... Difficult for me to judge as I don't have a copy of your DB to test - I'm pretty sure my first attempt is lacking something, but I don't think the example you copied is the right one either. It may work, but it makes little sense.

Also you use mysql_* functions which have been deprecated. You should be using PDO or mysqli. In addition, if you use WHERE clauses, be sure to use prepared statments with parameter binding.