How to sort my result alphabetically using the ID

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

How to sort my result alphabetically using the ID

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to sort my result alphabetically using the ID

Post 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
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

Re: How to sort my result alphabetically using the ID

Post by adsegzy »

yes, but i need to echo out the maker
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How to sort my result alphabetically using the ID

Post 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?
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

Re: How to sort my result alphabetically using the ID

Post 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]);
Post Reply