Jump to content


Photo

Help with making a Product Database


  • Please log in to reply
81 replies to this topic

#21 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 07:04 PM

my misreading - thought it was about the categories table


:D I do it all the time.  Doesn't have a categories table yet I don't think, that's the point, so we'll see what we can do.

Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#22 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 07:20 PM

Huggie, Alpine, you've been so helpful through all of this, thank you both!

Huggie - that works perfect , thanks (again!)

I had a feeling I'd have to restrucutre things... so before I go off deleting/adding new tables, I'll just tell you something else I'm hoping to include in my navigation that may effect my database structure...

Ok, first of all the is a music merchandise website. As you know, the category links down the left now which you've both assisted me on let the user browse through category. And your saying maybe a Category table is needed to make this run better, I understand that.

But I also plan to have a dropdown menu in the left navigation, which has all the music band names in, and when you would select one, it would display all products from that band. Essentially, this would be just like selecting a category, except in a dropdown menu, and browsing for band name.

So, my question is, would I need ANOTHER table also, for Band? At the moment, just like category, band is just another field name in my products table. So in the end would I need a products table, categories table, and a bands table?

#23 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 07:23 PM

Yes, that sounds about right...

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#24 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 29 September 2006 - 07:51 PM

Thanks, sorry if all the questions are getting a little tedias!

One last thing then, before I put all of this "into action"!

I create new tables, called products, categories, and bands. In categories table, the put the fields "id" and "category_name", and in the bands table, I put in the fields "id" and "band_name". In the products table, would I still need the fields "category", and "band", seeing as I have made new tables specificly for them? If I don't need them, then how can I, when putting in a new product into my database in the products table, say which category this new product belongs to and which band this product belongs to? I understand how I'd probably have to make all 3 tables have "ID" fields as the primary, but don't understand how adding a new product in the product table, how to link it with a category in the category table and a band in the band table.

How would I achieve this (probably basic) relationship?

#25 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 September 2006 - 11:07 PM

You still have a category field and a band field in the products table, but they only contain ID's, ID's that are linked to the primary key in both the band and categories table.  It's called normalization.

Try searcing on Google for it.  You'd do well to spend just 15 minutes reading about it or maybe taking a tutorial on it.  It seems like a boring step now, but it will save you so much time and coding in the future, and that's a promise!

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#26 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 08:05 AM

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

#27 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 04:06 PM

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

categories
category_id (primary)
category_name

bands
band_id (primary)
band_name

products
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:

#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;

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

#28 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 September 2006 - 05:39 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#29 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 05:50 PM

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?

#30 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 September 2006 - 05:55 PM

Only add that table if 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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#31 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 06:24 PM

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

#32 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 07:13 PM

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?

$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;


#33 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 September 2006 - 07:30 PM

You can now list them from the categories table
<?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";
}

?>

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

$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";


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#34 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 07:45 PM

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?

#35 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 September 2006 - 07:54 PM

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.

Optionally, 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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#36 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 30 September 2006 - 08:06 PM

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

#37 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 09:54 AM

:) 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":

$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;
}

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

#38 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 October 2006 - 09:59 AM

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'";
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#39 downfall

downfall
  • Members
  • PipPipPip
  • Advanced Member
  • 61 posts

Posted 02 October 2006 - 10:14 AM

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:

while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){

the full 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;
}

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

#40 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 October 2006 - 10:20 AM

Error in the query

Change
$result = mysql_query($sql);
to
$result = mysql_query($sql) or die (mysql_error());
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users