Database design for PHP 1

[DaRNCaT]

I don't know if this is the right place to post this, but I seriously need some help with the script I'm supposed to write.
I have items- they go in a table items with a unique ID
I have types- there are four types, items can be one of the four types. There are also categories- or sub types, Items can be many different sub types.
I have no idea how to put this into a database, or how to create queries for it.
Any help would be SO appreciated, at the moment I have a simple script working, but it's just not doing what I need.

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..

 

[spookie]

hi,
On the first look, There can be three tables
1)Items :fields(itemid,itemname,typeid,subtypeid)
2)type : fields(typeid,name)
3)subtype : fields(subtypeid,typeid,subtypename)

Eg.entry in item can be
1,php book,1,2

type
1,book

subtype
2,1,technical

(above all are rough examples, but u got the idea )

spookie



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[casters]

DaRNCaT, you are in the right place, in the sense that you are talking about a typical task that is solved storing data in a databases (mySQL, for example) and then managing the data using SQL statements (like, select, update, insert and so on) that you launch using a server-side script (in our case PHP) that will not only retrieve the data from the databases, but also "generate" html code to create dynamic pages. To do this you need lot of information that cannot be stored in just some messages. I suggest you to look for a good manual before starting: then you'll find in this forum lot of people that would be glad to help you in specific problems.

Good luck!
Gaetano

 

[DaRNCaT]

Spookie:
works to a point- and I did have that as an initial layout, the problem is that one item, can belong to many sub categories.
so you might have (for example) one necklace- it belongs to type necklaces, it also belongs to sub types- mother of pearl, wedding, sets, and one offs.
I can't figure out how to put this into a database schema.

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..

 

[sleipnir214]

If a sub-category can appear in any category, then:

1 table to store the product
1 table to store the major categories.
1 table to store the subcategories.
1 table that relates a product to a category and a subcategory.

If subcategories are limited to a single particular category, then you only have categories -- there's no such thing as a subcategory. A row in the category table has one column for the id of that category's parent -- if that value is zero, then that category is at the top of the heirarchy.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[DaRNCaT]

Thanks sleipnir214
I kind of understand that! I'm making a freebie site for a friend, and I'm a front end designer, this backend stuff gives me a headche.
how would you do a query on that?

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..

 

[sleipnir214]

What do you want to query?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[DaRNCaT]

To be honest, I'm pretty lost- I know what I've be told it has to acheive-
the front page has links to 4 sections- when those are clicked, it shows the section clicked and all the possible subsections, then if a sub section is clicked it shows the contents of the subsection
I'm sure there must be an easier way to do this, but I don't know how, nor do I uderstand how to build the queires.

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..

 

[sleipnir214]

It sound to me like you don't have categories and subcategories. It sounds like you have nested categories.

In this case, you need three tables:
1 to store all category information,
1 to store products,
1 to relate products to a category.

I'll assume you have no problems with the products table.

Let's say your category heirarchy is supposed to be like this:

Cats
   Siamese
   Burmese
   Norwegian Forest
Dogs
   Dalmatians
   Scottish Terriers
Birds
   Empreror Penguins
   Macaws
   Crows
Fish
   Betas
   Tetras
   Pirhana

The categories table will contain something like the following:

category_id   parent_id   name
1             0           Cats
2             1           Siamese
3             1           Burmese
4             1           Norwegian Forest
5             0           Dogs
6             5           Dalmatians
7             5           Scottish Terriers
8             0           Birds
9             8           Empreror Penguins
10            8           Macaws
11            8           Crows
12            0           Fish
13            12          Betas
14            12          Tetras
15            12          Pirhana

When you want to show the top-most categories, perform a query like:

SELECT * from categories where parent_id = 0

or replace zero with the category_id of your category.


Your products_to_categories table will have either two or three columns, depending on whether you want a record id column. If you have a kitten with a product_id of 3, and that kitten is a Siamese, then products_to_categories will contain:

id product_id category_id
1 3 2

You can now query for all products in a category:

select * from products p, products_to_categories pc, categories c where
p.id = pc.product_id and pc.category_id = c.category_id and c.category_id = <your category number>

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[ThomasJSmart]

I would have to repeat casters to be honist, this isnt realy the kinda stuff that can be taught from a topic discussion, you should realy save yourself a lot of hassle and irritation and read a decent book on it, there's plenty of ppl here to help you with specifics but not much ppl who are going to give a php/mysql lesson in a topic.

a good book is not neccesarily an expensive book, try any p2p program, like kazaalite.nl and download &quot;PHP and MySQL Web Dev.pdf&quot; this covers a lots of stuff and if u dont feel like reading it all you can use it as a reference and just check the stuff u want.

good luck,
Thomas

I learned a bit yesterday, today i learned a lot, imagine what i'll learn tomorrow!

 

[sleipnir214]

K9logic:
Please, either redflag the thread or help.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[DaRNCaT]

I have serveral good books- none of them give me any idea how to do this.
I have asked alot of GOOD programmers, and nobody has answered with something I can use.
Let's say your category heirarchy is supposed to be like this:
----well-- it's more like this
Necklaces
Mother of pearl
Sets
Wedding
One off
Earings
Mother of pearl
Sets
Wedding
One off
Precious stones
Braclets
Mother of pearl
Sets
One off
Precious stones
Shell
Accesories
Mother of pearl
Sets
Wedding
One off
Precious stones
Shell

As you can see, each jewellery item could belong to several different categories under the one type.
No body has been able to point me to something that shows how to create that kind of many to many relationship.
If anyone can shed some light on this I really would appreciate it, I've dug through books, talked to programmers etc, I'm beginning to think it's impossible under MySQL.

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..

 

[ThomasJSmart]

you could save variouse cat id's in the catagory field,

objects table
id-name-cat
1-earings-1,2,3

catogory table
id-name
1-pearls,
2-one off
3-stones

then to display earings you just explode the cat field
and make an array with all the cat-id's
then display the name of each cat buy matching the each id in the array with the name in the cat-table.


I learned a bit yesterday, today i learned a lot, imagine what i'll learn tomorrow!

 

[sleipnir214]

DaRNCaT:
I strongly disagree with K9Logic on the database schema you should use. Exploding a single &quot;categories&quot; field is cumbersome, it does not allow good use of database indeces, it makes insertions and deletions a PITA, it makes searching problematic, and it doesn't follow normal forms.

Let's take a subset of the problem.

You have a categories table, which looks something like this:

cat_id  cat_name         cat_parent_id
1       Necklaces        0
2       Mother of Pearl  1
3       Sets             1
4       Wedding          1
5       One off          1

And a products table which contains only two items, an 18&quot; necklace and a 24&quot; necklace. The table would look like:

prod_id   prod_name
1         18" necklace
2         24" necklace

The 18&quot; necklace should appear in the categories &quot;Mother of Pearl&quot; and &quot;Wedding&quot;, and the 24&quot; necklace should appear in the categories &quot;Sets&quot; and &quot;One Off&quot;. Both should appear in the &quot;Necklaces&quot; top-level category. Use another table, products_to_categories, to represent the relationships:

p2c_id    p2c_prod_id    p2c_cat_id
1         1              0
2         1              2
3         1              4
4         2              0
5         2              3
6         2              5

To find all the products in the &quot;Necklaces&quot; category, use a query like:

SELECT
   prod_id, prod_name
FROM
   categories, categories_to_products, products
WHERE
   cat_id = 0 AND
   cat_id = p2c_cat_id AND
   p2c_prod_id = prod_id

To make our 18&quot; necklace also appear in the category &quot;Sets&quot; just add a record to the products_to-categories table:

INSERT INTO
   products_to_categories
SET
   p2c_prod_id = 1,
   p2c_cat_id = 3

Removing the product '18&quot; necklace' from a category is just a matter of deleting the appropriate record from products_to_categories.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[DaRNCaT]

Thanks sleipnir214, I'll try putting that schema into what I have and see if I can figure it out.

Sometimes, when my code just won't behave, I take it outside and make it listen to britney spears music, and when it comes back it's really well behaved. I wonder if it's suffering from post tramatic stress syndrome now..