Jump to content

Help with making a Product Database


downfall

Recommended Posts

  • Replies 81
  • Created
  • Last Reply
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!!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

:) 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?
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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??
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.