Help with making a Product Database

[downfall]

Ah, starting to make sense now.I'll read on a few tutorials then and hopefully get this database up and running!!

[downfall]

Ok, I've done a bit of reading and come up with these 3 tables:

[b]categories[/b]
category_id (primary)
category_name

[b]bands[/b]
band_id (primary)
band_name

[b]products[/b]
product_id (primary)
product_name
product_small_image
product_large_image
product_price
category_id
band_id

Does this look like a good database structure?

With the above changes to the database, whereas it was noyl one table before containing all the feilds, where does the code need changing for the to achieve the linked left navigationn menu showing the categories, linking to that categories product?

Here is the code that worked for using just one table called products:

[code]
#create query
$sql="select distinct category from products";

#execute the query
$rs=mysql_query($sql,$conn)
or die("could not execute the query");;

while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id=$cat'>$cat</a>\n";
}

echo <<<_HTML

$list

_HTML;[/code]

Is there much change to this, do multiple tables need to be called upon? Any assistance would be appreciated!!

[Barand]

The relationship between category and product is straightforward - a product belongs in a category.

However, the band/product relationship is going to be different - a product can have many bands, not just a single band_id. Also a band can have many products. Where you have a many-to-many relationship like this you have to introduce an intermediate link table, say "band_products" containing both band and product ids as foreign keys

[pre]
categories            products                  band_products          bands
--------------        -------------            -------------          ------------
category_id (pk)--+  product_id (pk)  ---+    bp_id (pk)      +---  band_id (pk)
category_name    |  product_name        +--  product_id (fk)  |      band_name
                  |  product_small_image      band_id (fk)  ---+
                  |  product_large_image
                  |  product_price
                  +-- category_id (fk)
[/pre]

At least I'm assuming this is the case ie The Andrews Sisters, the Four Tops and the Morton-Fraser Harmonica Quartet could all have black, large T-shirts.

[downfall]

Thanks berand, I'll add a new table in my databse containing band_id and product_id.

With the inclusion of this table, does anything else in the other tables need altering? ie.. remove band_id field from the products table?

[Barand]

Only add that table [b]if[/b] my many-to-many assumption is correct.

If it is correct, then yes, the band_id would move from the product table to the band_product table

EDIT PS: It depends on your product catalogue. IF you have

product description
------------------
black T-Shirt (Arctic Monkeys)
black T-shirt (Rolling Stones)

then each product does have only 1 band and you don't need the table adding. OTOH if you just have

black T-shirt

and several bands have that product then you do need it.

[downfall]

Yep, a few of the product names (namely "Logo") are used by more then one band, so I'll add the table as you said and delete band_id from products table.

Thanks again ;)

ps thanks for the diagram

[downfall]

Another question :) (but I am learning so much here!!)

What table do I select, to put all the categories in my left navigation menu, with them linked to the page of the appropriate category clicked, showing products for that category on the following page (details.php)?

Do I get select from categories or products, using the diagram structure posted a moment ago? This is what I used for my left navigation category selection when I only had 1 table - products. are any other changes required with the new 4 table format?

[code]$sql="select distinct category from products";

#execute the query
$rs=mysql_query($sql,$conn)
or die("could not execute the query");;

while($row=mysql_fetch_array($rs))
{
$id = $row["id"];
$cat = $row["category"];

$list .= "<a href='details.php?id=$cat'>$cat</a>\n";
}

echo <<<_HTML

$list

_HTML;[/code]

[Barand]

You can now list them from the categories table
[code]
<?php
$sql = "SELECT category_id, category_name
        FROM categories
        ORDER BY category_name";
$res = mysql_query($sql) or die(mysql_error());
while (list($id, $cat) = mysql_fetch_row($res)) {
    echo "<a href='details.php?id=$id'>$cat</a>\n";
}

?>[/code]

Same for bands.

When you list the categories in the selected category, you now need to search for those products where category_id = $id whereas before you were searching for a cat name

When searching for a selected band you need a JOIN query, something like

[code]$sql = "SELECT p.*
        FROM products p
        INNER JOIN band_products bp ON p.product_id = bp.product_id
        WHERE bp.band_id = '$band'
        ORDER BY p.product_name";[/code]

[downfall]

Great stuff, thanks again.

I've just written everything out on the database with the 4 table structure, and have now found out that phpmyadmin does not include a foreign key option >:(

Are there any other means I can input the foreign keys to the field names that I need to?

I know when you are creating a databae locally you can use dos, any ideas how i would go about it online?

[Barand]

By including category_id in the product table it becomes a "foreign key". For added efficiecy, add non-unique indexes on the FK columns in you tables.

[b]Optionally[/b], you can go further and formally declare it as a FK (if you want automatically to enforce referential integrity) by using tables of type InnoDB

[downfall]

thanks, i'll give that a go. :)

[downfall]

:) I now have a nice list of categories going down my left categories navigatio menu thanks to that altered code Barand! Thanks!

Could you (or anyone) assist me with the page (details.php), that when the categroy link is pressed, goes to list all the products in that category? This is the old code that worked when I only had 1 table - "products":

[code]$cat = $_GET['id'];

$sql = "SELECT name, price FROM products WHERE category = '$cat'";
$result = mysql_query($sql);
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo <<<HTML
  <table>
    <tr>
    <td>{$product['name']}</td>
    </tr>
    <tr>
    <td>{$product['price']}</td>
    </tr>
  </table>
  <br><br>
HTML;
}[/code]

Could anyone assist is updating this to my new 4 table database?

[Barand]

If you used the code I gave you then

$sql = "SELECT name, price FROM products WHERE category = '$cat'";

needs to be this, using category_id instead of category to find the related products

$sql = "SELECT name, price FROM products WHERE category_id = '$cat'";

[downfall]

Thanks for the quick reply.

I'm getting this error after clicking on a category link:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/rockrag/public_html/details2.php on line 26

Line 26 is:

[b]while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){[/b]

the full code:

[code]$cat = $_GET['id'];

$sql = "SELECT name, price FROM products WHERE category_id = '$cat'";
$result = mysql_query($sql);
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
   echo <<<HTML
   <table>
    <tr>
     <td>{$product['name']}</td>
    </tr>
    <tr>
     <td>{$product['price']}</td>
    </tr>
   </table>
   <br><br>
HTML;
}[/code]

Any idea what needs changing in line 26, or anything else to the code?

[Barand]

Error in the query

Change
$result = mysql_query($sql);
to
$result = mysql_query($sql) or die (mysql_error());

[downfall]

Thanks, getting closer :)

No error, But, it now says:

[b]Unknown column 'name' in 'field list'[/b]

Any ideas?

[alpine]

Your error is here:

SELECT [color=red]name[/color], price

No field named "name" in your table products

[Barand]

try "product_name" maybe?

[downfall]

product_name - it works :D

I really do owe a huge thanks to alpine, huggie and barand! All you help is really appreciated!

My category listings now is basically complete so I can give the band select a go.

But theres one thing that would be useful for the category listings page thats just been fixed.

At the moment it looks like this (when the T-shirt link is pressed for example:

[b]T-Shirt Name

$9.99[/b]

I would prefer it to have the band the t-shirt belongs to with it, like:

[b]Band Name - T-Shirt Name

$9.99[/b]

Is this easy to do??

[Barand]

Change query to

[code]<?php
$sql = "SELECT p.product_name, p.price, b.band_name
        FROM products p
        INNER JOIN band_products bp ON p.product_id = bp.product_id
        INNER JOIN bands b ON bp.band_id = b.band_id
        WHERE p.category_id = '$cat'
        ORDER BY p.product_name, b.band_name";
?>
[/code]

As discussed earlier, some products may have more than one band

[downfall]

When I click a category now, it says:

[b]Unknown column 'bp_product_id' in 'on clause'[/b]

I tried changing bp_product_id to band_products.product_id as a guess but then I get a different error which I probably created myself by changing that text: Unknown table 'band_products' in on clause

[Barand]

Sorry. That should have been "bp.product_id"

I made a couple of other edits so copy query again.

[downfall]

That seems to work with no errors :)

How can I successfully echo the band name into the HTML below? As you can see below, I added {$bands['band_name']} in a new table row but the band name doesn't appear for that product.. just the product name and price appear.

[code]$cat = $_GET['id'];

$sql = "SELECT p.product_name, p.product_price, b.band_name
        FROM products p
        INNER JOIN band_products bp ON p.product_id = bp.product_id
        INNER JOIN bands b ON bp.band_id = b.band_id
        WHERE p.category_id = '$cat'
        ORDER BY p.product_name, b.band_name";
$result = mysql_query($sql) or die (mysql_error());
while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo <<<HTML
  <table>
    <tr>
    <td>{$product['product_name']}</td>
    </tr>
    <tr>
    <td>{$bands['band_name']}</td>
    </tr>
    <tr>
    <td>{$product['product_price']}</td>
    </tr>
  </table>
  <br><br>
HTML;
}[/code]

[alpine]

You have to call it from the mysql_fetch_array you are using - thats $product

so {$product['band_name']} should do it

[downfall]

Thanks alpine :) I'm sure the browse by category section of my website is now complete at last :D

Now on to browse by band in a dropdown menu... :)