I am having trouble to link two tables together on my database. I have an items table and a category table, I need to show which items are in which category, some items are also in 2 categories. I have made a links table with the item_id and category_id from the other tables, but don't quite know where to go from there and displaying it on the webpage within a table.

Please help

well it would be nice to show your table design, rather than let us guess it. I appreciate that some contributors like a more 'sporting' challenge of trying to work out what your design is...

tables:

item
item_id(pk)
Item_name
item_description
item_price
item_image

category
category_id(pk)
category_name

links
item_id(fk)
category_id(fk)

what is it that you want to display?

if there's only one webpage, do you want to show all items in all categories, or all categories with their items?

or will there be a category page, and you want to list only the items in that category?

or will there be an item page, and you want to list the categories that the item belongs to?

despite enjoying a sporting challenge, i am not about to write a half dozen queries for you on the hope that one of them is what you want

There is going to be 3 different categories with a page for each. On these pages i want the items within that category to be displayed. However there is one item that needs to be placed within two categories.

okay, let's say on the category page you will have a variable called $cat that contains the id of the category

this is your query to return the category name as well as all its items --

Thank you. Would that then make it so each item belongs in the correct category? I can't get my head around how each category will know what items to hold

did you test the query?

Its for an Admin section and what will happen is the admin can add, edit and delete items found within each category but we are having problems making it so each item is in the correct category, also one item can be found within two categories.

Once the admin have then edited, added or deleted content the web page for the specific category that has been changed will be updated. (Its not for a real website etc its just so I am able to grasp MySQL and PHP, finding it very difficult).

you said you created the tables

the next thing to do is put some sample data in them

then please test the query i gave you

This error appears

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

I'm not sure if I am putting it in the correct place or if I need to add more code :-/ I'm very lost! The code is what I have below, the commented out sections are what we originally had as we where not sure how to put all items within their categories.

session_start();

"SELECT category.category_name
, item.item_name
, item.item_description
, item.item_price
, item.item_image
FROM category
INNER
JOIN links
ON links.category_id = category.category_id
INNER
JOIN item
ON item.item_id = links.item_id
WHERE category.category_id = $cat";

//$sql = "SELECT * FROM `item` WHERE `item_id` = '1'";

mysql_select_db("vhs_ecom") or die(mysql_error());



//("SELECT * FROM item_price, CONCAT('£', item_price) as amount FROM item");

//$result = mysql_query("SELECT * from item WHERE item_id > 27");

echo "<table border='1'>
<tr>
<th>Item Name</th>
<th>Item Description</th>
<th>Item Price</th>
<th>Item Image</th>
</tr>";
while($row = mysql_fetch_array($result)){
echo "<tr>
<td>" .$row['item_name'] ."</td>
<td>" .$row['item_description'] ."</td>
<td>" .$row['item_price'] ."</td>

</tr>";
}
echo "</table>";
?>

sorry, i can't help you with the php -- this is the mysql forum

try testing your query directly in mysql

I didn't think of doing it there sorry. I have done it and it says;

#1054 - Unknown column '$cat' in 'where clause'

okay, let me explain the step you missed

the query i gave you anticipated that there would be a variable to specify which category

that was based on my question to you about where this query was to be run, under what circumstances, and you answered a category page, and obviously the only variable in that situation is which category it is that's about to be displayed on that page

when you test the query in mysql, you cannot, of course, use a php variable name, you have to use an actual category id value

the error message makes sense now, doesn't it

Jewellery has a category ID of 1. I have replaced $cat with $1 but it still says the same error. Its me being think Or should i put the category name?