我该如何输出这张表？

Question

I'm trying to output prices for some products, all from different vendors. The products are in the rows and the vendors are in the columns. It seems like an easy enough thing to do but I can't figure out how I'd do this with PHP.

The products are stored in a products table, the vendors are in a vendor table, prices are in a pricing table. There are common ID fields that link them all up. A sample record from the pricing table might be:

product_id = 5, category_id = 2, vendor_id = 8, product_price = $5.50

In my table, the vendors will always be in the same order across the columns. But, how do I structure the query and PHP so I can get that output? I know this is a basic question but I'm relatively new to this and need some help. Thanks in advance if you can help me output a table like the one below.

Edit: My table structure is as follows:

product_categories

category_id

category_name

category_order (used to sort)

vendors

vendor_id

vendor_name

models

model_id

model_name

model_link (link to the website for more info)

prices

model_id

vendor_id

category_id

price

<h2>Pet Foods</h2>
<table>
   <tr>
      <td>Product</td>
      <td>Vendor 1</td>
      <td>Vendor 2</td>
      <td>Vendor 3</td>
   </tr>

   <tr>
      <td>Cat Food</td>
      <td>$5</td>
      <td>$6</td>
      <td>$5.50</td>
   </tr>
</table> 

Answer 1

$sql="SELECT product_id, product_price FROM pricing ORDER BY product_id, vendor_id";
$res=mysql_query($sql,$link);
if(mysql_num_rows($res)>0){
    $pId='';
    echo "<table><tr><td>Product</td><td>Vendor 1</td><td>Vendor 2</td><td>Vendor 3</td></tr>";
    while($row=mysql_fetch_array($res)){
        if($pId!=$row[0]){
            if($pId!=''){
                echo "</tr>";
            }
            $pId=$row[0];
            echo "<tr><td>$pId</td>";
        }else{
            $price=$row[1];
            echo "<td>$price</td>";
        }
    }
    echo "</tr></table>";
}

I have assumed in this that you have same vendors and no vendor data is missing, but I will still put some check to see if I haven't missed any vendor price data. you can easily do that. Good luck!