SELECT FROM MULTIPLE TABLES

[adi123]

I am selecting data from two tables in the database. The results show up correctly but four times instead of one. I checked the code and there is no loop.

 

Can anyone please tell me what I have done wrong to make the result appear four times instead of one.

 

<?php

$category_id=$_GET['category_id'];

include('pagination/paginator.class.php');

 

$query = "SELECT COUNT(*) FROM products ORDER BY category_id";

$result = mysql_query($query) or die(mysql_error());

$num_rows = mysql_fetch_row($result);

 

$pages = new Paginator;

$pages->items_total = $num_rows[0];

$pages->mid_range = 9; // Number of pages to display. Must be odd and > 3

$pages->paginate();

 

echo $pages->display_pages();

echo "<span class=\"\">".$pages->display_jump_menu().$pages->display_items_per_page()."</span>";

 

$query = "SELECT products.p_id, item, description, price, pic, shops.s_page FROM products, shops WHERE category_id='$category_id' ORDER BY category_id ASC $pages->limit";

$result = mysql_query($query) or die(mysql_error());

 

while($row = mysql_fetch_row($result))

{

echo '<div class="product_box margin_r40">';

echo '<div class="image_wrapper">';

echo '<img src="shops/'.$row['5'].'/items/'.$row['4'].'" width="100" height="100" />';

echo '<p><strong>'.$row['1'].'</strong></p>'.$row['2'].'<p><strong>£'.$row['3'].'</strong></p>';

echo '<p class="style7"><strong><a href="view.php?action=view&id='.$row['0'].'">View Item</strong></a></p>';

echo '<p><a href="cart.php?action=add&id='.$row['0'].'"><img src="images/add_to_cart.jpg" alt="Add To Cart" width="80" height="20" /></a></p>';

echo '</div>';

echo '</div>';

}

 

echo "<table width='650'>";

 

echo "<th>".$pages->display_pages();

echo "<p class=\"paginate\">Page: $pages->current_page of $pages->num_pages</p>";

echo "<p class=\"paginate\">Items: $pages->limit (retrieve records $pages->low-$pages->high from table - $pages->items_total item total / $pages->items_per_page items per page)</th></p>";

 

echo "</table>";

?>

[Pikachu2000]

When posting code, enclose it within the forum's

 . . . 

BBCode tags.

[sunfighter]

$query = "SELECT products.p_id, item, description, price, pic, shops.s_page FROM ...

shouldn't you declare where the "item, description, price, pic" comes from? Like "products.item, products.description, products.price, products.pic"? 

[adi123]

It doesn't make a difference when I put where the item comes from. I have just noticed when I remove shops from the sql equation, it shows properly but with it there, results how 4 times more.

 

How do I stop it from multiplying the results by 4

[adi123]

Finally solved the problem. The Sql statement was the reason for results multiplying by 4.