PHP MySQL problem

[DanHLFC]

Hi,

 

I have set up a website connected to a MySQL database, but there is something that I want to do but I am not quite sure how. To try to explain it clearly, I will use the following example.

 

Say I have three tables in my database:

Category(category_id, name)

Subcategory(subcategory_id, category_id, name)

Product(product_id, subcategory_id, name, description)

 

What I want to create is a navigation system whereby users can click on a category on the categories page, and that will take them to list of subcategories within that category. These subcategories would be pulled out of the database and listed as hyperlinks to the next page, which would contain a list of products within that subcategory. Again, these would be hyperlinks that would take the user to a page which pulls the product details from the database and displays it as regular text.

 

I understand how to do the last bit (extracting product details), however I am unsure as to how I would go about getting a list of subcategories within the category that the user chooses, and displaying them as hyperlinks. Does anyone know how to do this?

 

Thanks in advance.

[Yesideez]

On each script you'll need to run through a SELECT query in a loop to pull the data from the tables displaying the data.

$query=mysql_query("SELECT * FROM table");
while ($row=mysql_fetch_assoc($query)) {
  echo '<a href="show.php?id='.$row['tableid'].'">'.$row['tabletext'].'</a>';
}

 

In your final script you'll need a slightly more complex query to join the tables together:

SELECT c.*,s.*,p.* FROM category AS c
LEFT JOIN subcategory AS s ON c.category_id=s.subcategory_id
LEFT JOIN product AS p ON p.subcategory_id=s.subcategory_id
WHERE p.product_id=$productid

 

EDIT: Changed last line of query to reflect the chosen product_id

[DanHLFC]

Thanks, I'll give that a try 

[DanHLFC]

Just another quick question, where would that second SQL query have to be placed?

 

Thanks