[SOLVED] Using PHP to sort data in MySQL table 1 by values in table 2

[ChambeRFienD]

Well.. I have a MySQL database with 2 tables in it. products and prices.. Products has a field in it called 'prodno'. Prices has a field in it called 'prodid'.

 

There is one of each item in products and each product has it's own unique 'prodno'

 

There are multiple instances of each product's pricing in prices.. One for each price.. So if a product had multiple prices depending on quantity it would have an entry for each price.. Example:

 

id = 1

prodid = 7001

qty = 300

price = 12.5

 

id = 2

prodid = 7001

qty = 400

price = 15.5

 

I was wondering if I could query the database getting info from the products table but sort it according to the prices in the prices table.

 

Kinda like doing a "SELECT * FROM products" ordering it by the lowest price value from each item..

 

This is all very confusing to me, and I'm the one writing it. Let's try one last time..

 

Query the database selecting * from products (I plan on using all the info in products) and ordering them by the lowest price for each item in the prices table. I've tried sorting the results of just a basic SELECT * FROM products using Javascript and PHP but with pagination in the results it makes it a bit hard.

[ChambeRFienD]

Alrighty.. Figured it out myself..

 

$sql = "SELECT prices.price AS price, products.pname AS pname, products.prodno AS prodno FROM prices, products WHERE prices.prodid = products.prodno ORDER BY prices.price ASC LIMIT 20";

 

$query = mysql_query($sql);

 

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

echo $row['price'] . " - " . $row['pname'] . " - " . $row['prodno'] . "<br />\n";

}