Page 1 of 1

How to sort my result alphabetically using the ID

Posted: Tue May 06, 2014 10:38 am
by adsegzy
Hello friends,
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

Code: Select all

<?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

Code: Select all

$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

Code: Select all

$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?

Re: How to sort my result alphabetically using the ID

Posted: Tue May 06, 2014 11:05 am
by Celauran
Is this what you mean?

Code: Select all

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

Re: How to sort my result alphabetically using the ID

Posted: Thu May 08, 2014 1:10 pm
by adsegzy
yes, but i need to echo out the maker

Re: How to sort my result alphabetically using the ID

Posted: Thu May 08, 2014 1:13 pm
by Celauran
Which you can do when looping over the result set.

Code: Select all

foreach ($result as $row) {
	echo $row['maker'];
	echo $row['product'];
	echo $row['price'];
}
What isn't working?

Re: How to sort my result alphabetically using the ID

Posted: Fri May 09, 2014 5:02 am
by adsegzy
Thanks friends, i got a better and easier query

Code: Select all

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