php/mysql grouping db rows

[morleymouse]

im having a bit of a mare with this, should be easy (I think)

Got a bit of a database in a similar sort of structure as below

Merchant | product | link

Yes its been simplified and in the entire database lets say there are 15 merchants contributing products - but I dont know what they are.

I want to show all product by each merchant, if i knew what each merchant was it'd be an easy

PHP Code:

$qry= mysql_query("SELECT * FROM products WHERE merchant_name = 'Merchant'") or die("Database Error"  . mysql_error()); 


And repeating that for each merchant, but I dont know what merchant are in the database. I cant just find out and set it up that way because its going to be changing very regularly.

I think its some sort of GROUP BY merchant_name query first then cycling through them but keep getting myself in a mess

If anyone could help it'd be greatly appreciated

[haggul]

Do you want all products listed, with perhaps a heading in between for each merchant :

Merchant 1
Prod 1
Prod 2
Prod3.....
Merchant 2
Prod1
Prod2
Prod3...

... and so on?

If so you can stick to the one query, make sure you have an order by clause in it to list in merchant order. Then in your loop just check for changes in the value of the merchant, and write the merchant name header to the page based on that.

Did that make any sense? I can probably knock up some PHP code if that is what you want, and that didn't make enough sense!!

It's more efficient than having a call to the database for each merchant.

[morleymouse]

hmmm but theres a price in there as well so ideally id like to split it down by merchant, then by price

cheers for that though, on the way to a fix

[haggul]

Do us an example of what the results should look like (as per my amazingly detailed one above) - just showing what you want/expect where and it'll all become clear!! It's nice when the day job is solving your own database/script issues to have a look at someone elses.

[morleymouse]

day job like that im glad your about

okay so lets say there are 3 merchants in my db, one has 1 product, other has 2, other has 3 so the db is below (changed link for price)

merchant1 | baloon | 12.99
merchant2 | peg | 1.99
merchant2 | pencil | 1.89
merchant3 | arm | 0.20
merchant3 | leg | 5.46
merchant3 | stool | 22.00

What I want after a bit of php magic...

merchant 3
arm - 0.20
leg - 5.46
stool - 22.00

merchant2
pencil - 1.89
peg - 1.99

merchant1
baloon - 12.99

hope thats a bit clearer, the merchant with the cheapest price first, so its merchants by price asc, then within that products by price asc

[haggul]

Ahh gotcha...

try this for the query - assuming your version of mysql supports sub querys - if not shout and can redo this :

select t1.merchant_name, t1.product, t1.price,
(select min(price) from tblTable t2 where t2.merchant_name = t1.merchant_name) as lowestprice
from tblTable t1
order by lowestprice, t1.merchant_name, t1.price

within your php loop just have similar to the following :

if($lastmerch!=$row[0])
{
$lastmerch = $row[0];
echo $row[0] . "<br>";
}


That should sort of do it - of course if you are using tables etc for layout whack in some extra code in there. I'm an ASP/.Net coder mostly so excuse my PHP!!

[morleymouse]

thats one crazy select query! never used one liek that before, and thats where the errors kicking in

error line is this:

while ($row = mysql_fetch_array($test, MYSQL_ASSOC)) {


and $test is

$test = mysql_query("select t1.merchant_name, t1.product, t1.price,
(select min(price) from tblTable t2 where t2.merchant_name = t1.merchant_name) as lowestprice
from tblTable t1
order by lowestprice, t1.merchant_name, t1.price");

just me being silly, should I be replacing the t1's with something or lowestprice, tblTable? Sorry just loads of bits ive not seen before

[haggul]

No worries.

Firstly the mental SQL statement includes a subquery that fetches the lowest possible price for a particular merchant - that can then be used as the initial order by. The t1 and t2 are aliases to make the code "easier", as is the lowestprice bit - thats the name of the returned value of the subquery.

tblTable will need to be replaced by your own table name, as may merchant_name, product and price with your field names.

My PHP would be similar to below as I've never used fetch_array but as I said PHP is my weakest bit!! :

$sql = "select t1.merchant_name, t1.product, t1.price,
(select min(price) from tblTable t2 where t2.merchant_name = t1.merchant_name) as lowestprice
from tblTable t1
order by lowestprice, t1.merchant_name, t1.price";

$query = mysql_query($sql,$mylink) or DIE (mysql_error());
while ($row = mysql_fetch_row ($query))
{

do your stuff

}

Where $mylink is your connection to your database.

It may be you can get away with your current PHP and just make sure the SQL references you proper table/field names?

[morleymouse]

id got the rest but forgot to replace tblTable with the table name!

works a treat muchos I owe you one!