pulling from two mysql tables

[lional]

Hi

I have products in a produs table and categories in another.

I would like to put a category heading and then list all the products in that category below it and then list the next category with all of its products and so on.

Here is my code so far

<?php
    $query = "SELECT * from products WHERE vets_tour = '1'";
$result = mysql_query($query, $conn);
while ($row = mysql_fetch_assoc($result)){
$prodid_out = $row["prod_id"];
$category_out = $row["category"];
$prod_name_out = $row["prod_name"];
$prod_desc_file_out = $row["prod_desc_file"];
$image_file_out = $row["image_file"];
$price_out = $row["price"];
$query = "SELECT * from cats WHERE cat_id = '$category_out'";
$result = mysql_query($query, $conn);
while ($row = mysql_fetch_assoc($result)){
$cat_id_out = $row["cat_id"];
$category_name_out = $row["category"];
$online_purchase_out = $row["online_purchase"];
$sell_cats .= $category_out . ' ';
}
?>
<tr><td>
</td><td>
<div style="text-align:center;"><h3><?php print $category_name_out ?> Category</h3></div>
</td>
</tr>

      <?php
    $query = "SELECT * from products WHERE vets_tour = '1' AND category='$cat_id_out'";
$result = mysql_query($query, $conn);
while ($row = mysql_fetch_assoc($result)){
$prodid_out = $row["prod_id"];
$prod_name_out = $row["prod_name"];
$prod_desc_file_out = $row["prod_desc_file"];
$image_file_out = $row["image_file"];
$price_out = $row["price"];


 echo '<tr><td><div id="prod_text"><img src="images/products/' . $image_file_out . '"></div></td><td><div id="prod_text">';
 include "images/products/text/$prod_desc_file_out";
echo '</div>
<p></p>';
}}
?>

 

Thanks in advance

 

Lional

[Muddy_Funster]

OK, do not run queries within loops unless you absoloutly have to - and don't use SELECT *.

 

something like this should do the job:

 $query = "SELECT prod_id, prod_name, prod_desc_file, image_file, price, cats.cat_id, cats.category AS cat_out, online_purchase ".
"FROM products RIGHT JOIN cats ".
"ON (cats.cat_id = products.category) ".
"WHERE vets_tour = '1'";
        $result = mysql_query($query, $conn);
$catflag = '';
        while ($row = mysql_fetch_assoc($result)){
$prodid_out = $row["prod_id"];
$prod_name_out = $row["prod_name"];
$prod_desc_file_out = $row["prod_desc_file"];
$image_file_out = $row["image_file"];
$price_out = $row["price"];
$cat_id_out = $row["cat_id"];
$category_name_out = $row["cat_out"];
$online_purchase_out = $row["online_purchase"];
$sell_cats .= $category_out . ' ';
        if ($catflag != $category_name_out){
echo '-'.$category_name_out.'<br>';
$catflag = $category_name_out;
}
echo'---'.$prodid_out.'<br>';
echo'---'.$prodid_name_out.'<br>';
echo'---'.$prodid_desc_file_out.'<br>';
echo'---'.$image_file_out.'<br>';
echo'---'.$price_out.'<br>';
echo'---'.$cat_id_out.'<br>';
echo'---'.$category_name_out.'<br>';
echo'---'.$online_purchase_out.'<br>';
}

 

This is untested, and poorly formated for quickness sake, but should get you close to what you are looking for.