0

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?

4
Contributors
6
Replies
26
Views
3 Years
Discussion Span
Last Post by diafol
0

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

0

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]);
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.