[SOLVED] Please Help with PHP and MySQL Query

[TonEUK]

Hello all

 

I am creating a content management system and I have a history table which stores what products were bought together. The purpose of this is so that when a customer looks at a product, related products which were bought with the product they are looking at will be displayed below.

 

The history table with example records in looks like this:

 

p_id | r_id

 

1 | 7

1 | 6

8 | 7

3 | 7

1 | 7

 

Every time a customer checks out the link between the 2 products is created. For example customer 1 bought product 1 and 7 at the same time.

 

The problem I have is trying to query the table for the most number of related products. So for example, say a customer views product 1 I would require 3 similar products which were bought at the same time to be displayed under the product.

 

So from the records in the table above I would need the query to count how many time another product was bought with it and display it in ranked order. So for this table 1 and 7 were bought 2 times so 7 would be displayed first then 1 and 6 were bought once so that would be second.

 

How would I query this table in sql to do that?

 

Thanks

 

Tone

[TonEUK]

Can anyone help me with this please.

Can it be done?

 

Thanks

[Barand]

Is this what you mean

<?php
$prodid = 1;

$sql = "SELECT r_id, COUNT(*) as num
        FROM history
        WHERE p_id = '$prodid'
        GROUP BY r_id
        ORDER BY num DESC";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

while (list($r, $n) = mysql_fetch_row($res)) {
    echo "$r : $n <br />";
}
?>

[TonEUK]

Thanks alot, the query works exactly how I wanted it to.

Had to add LIMIT 3 to then end to make it only return 3 records.

 

Thanks for your help, I'm not that good with SQL will have to get some books on it.

 

Thanks