Page 1 of 1

PHP, Databases & Select Menu Help

Posted: Mon Oct 15, 2012 5:26 pm
by mrzebra
Need some help here and I'm not sure if this is possible to do.

I'm designing my online store and I'm putting the products into tables in a database. So far I have to following tables in my database:

Apothecary with the following headers: id (INT), name(text), description (text), incense(varchar), oil(varchar)
id: incrementing # with leading 0's (example: 00136)
name : name of the specific blend (example: Abramelin)
incense: this is either a Y or N (this is used to tell which blends can be done as an incense)
oil: this is either a Y or N (used to tell which blends can be made as an oil)

Products with the following headers : productId (INT with leading 0's), name (text), price(float)
Example:
01 Small Incense 6.50
02 Large Incense 9.00
03 1 dram oil 4.50
04 2 dram oil 8.00
05 4 dram oil 14.50
06 8 dram oil 25.00


So now on my incense page I have it all displaying properly by using a query on my database to pull all the items in the apothecary table that have a Y value under incense. I have a select options menu for selecting the size wanted (small or large). I'm stuck now on how to pull the correct price if the person selects small from the menu or large from the menu. Is there a way of doing this? I'm thinking it has to be done through javascript but I'm unsure???

The other issue I'm running into is when I pass an item to my cart page. I need to combine the productId with the id from the apothecary table to create a unique item id. (Example: 0100136 would be "Abramelin Small Incense").

I've been reading about JOINS and I was able to use CROSS JOIN that joined small incense and large incense with all items in my apothercary table but then I got stuck on how I would work with that in my cart.php page, especially since I will be having around 20-25 different products in my products table. I was thinking about having to use switch statements on the cart page.

Any help would be much appreciated and hopefully this makes sense.

Re: PHP, Databases & Select Menu Help

Posted: Wed Oct 17, 2012 1:43 pm
by califdon
A few fundamentals: the reason you find it difficult to make this work is that your database schema (the structure of your data) doesn't follow the rules for relational databases, in that you are combining more than a single value in the same field in your Products table. When you say that "name" will contain such things as "2 dram oil" or "Large Incense", and you need to distinguish between "Large" and "Small" or "1 dram" and "4 dram", you are violating the very first rule, which is that values in a column (field) must be "atomic", that is, cannot be broken into smaller values within it. So the first thing you must do is separate that field into 2 fields: "name" and "size". That allows you to select the name and size of a product.

Then, although this probably won't cause a problem, an INT value has no such thing as leading zeroes ( 5 = 05 = 005); that's just a matter of how you might want to format a number for display. But in fact, there should be no need to ever display an id field (they are there to identify records to the database engine and the queries that use them), so you can just ignore the "leading zeroes" business entirely.

You would not want to use a CROSS JOIN, in any case. What you need to learn about is INNER JOINs (which is the default, so you only need to use "JOIN" in such a query. To explain JOINs would require a lot more explanation than can be done in a reply post here in the forum. Use the following search terms in Google and you will find many useful articles:
sql inner join tutorial

Good luck.

Re: PHP, Databases & Select Menu Help

Posted: Sat Jan 19, 2013 10:04 pm
by mrzebra
Thanks for the advice. I have been thinking about my database design. I created a pdf file of the structure of the tables in my database. I'm not sure if there is an easy way of doing this. I want to use leading zeros on the id's because I want to create a unique product id for the item in the shopping cart. For example, Abramelin Small Incense would have the id #100001 and a Abramelin Crowley 1 Dram oil would have the id #400002. My apothecary table has over 163 different types available. I want to make it easy to update pricing for items and make it easy to add new apothecary types.

I don't know if my structuring is too complicating and if it can be simplified or not.

I'm thinking for my cart code i will have to use a switch statement for each item in the products table.

Any help on trying to simplify this would be greatly appreciated.
My database table structure
My database table structure

Re: PHP, Databases & Select Menu Help

Posted: Sat Jan 19, 2013 10:36 pm
by califdon
You don't seem to have understood any of my earlier explanations. I can only refer you back to that post and suggest you carefully read it, then do the required reading of tutorials on relational databases. The approach you are taking is guaranteed to fail. Change your table structure to conform with 3rd Normal Form of the relational model, drop your insistence on "leading zeroes", which is not appropriate for an ID field and, in any case, is not what you showed as an example. I taught database classes in college for many years and I assure you that trying to think through a database structure without understanding relational normalization is a sure recipe for failure. Relational database theory is highly NON-intuitive.

Re: PHP, Databases & Select Menu Help

Posted: Sun Jan 20, 2013 2:38 am
by mrzebra
So from what I read and kind of understand (you will have to bear with me because I'm learning all of this stuff on my own) this is the approach i should be taking. Now I'm thinking I can put "Figure Candles" on the products table because the prices are different for each figure candle. I won't be able to list it under the "candles_table" because all the others will have a set price. Is my thinking correct with the rules...I can't have any nulls?? Am I heading in the correct direction now?

The other issue I'm confused about is my apothecary table because the 160+ formulations are available in incense, oils, soap or powders...
revised database structure.jpg

Re: PHP, Databases & Select Menu Help

Posted: Sun Jan 20, 2013 12:29 pm
by califdon
It's very difficult to design, or to evaluate someone else's design of a relational database without really understanding what the real-life circumstances are in excruciating detail, but let me try to get you on the right track with some generalities:
  1. Every relational database is a DATA MODEL of relationships that exist in the real world.
  2. You can't begin to make any decisions about your tables until you have defined the ENTITIES that you will represent in the database. Entities are like nouns in grammar--things, places, people, events, transactions, etc. They are the "things" that you will deal with, and which have ATTRIBUTES or qualities (adjectives, in grammar). I strongly recommend that you do this in writing, not just by naming them, but writing a complete sentence about each one that clearly defines what it is.
  3. Each Entity will become a TABLE in your database. If you have any many-to-many relationships in your database, each of them will become an additional Table. IMPORTANT: The above rules completely determine what Tables you will require. Don't let anyone tell you that there are any other considerations, like "too many tables" or "too many fields in a table" that have any meaning in a relational database!
  4. Once you have carefully defined your entities, you determine what will be used as the PRIMARY KEY for each entity. This can be a "natural key", such as a value that has some meaning in the real world, but in most cases you will be well advised to use a totally meaningless value that is assigned automatically by the database, starting at 1 (in MySQL this is called auto_increment). The reason for this is that the Primary Key of a table must ALWAYS be present and it CANNOT be duplicated within the table, so it is usually easier to let the database manage it rather than try to make it meaningful to humans. This is the ID field that will also be used as the Foreign Key in other tables to link related records. If you have other identifiers that are meaningful to humans, you will usually do better to make them separate fields and avoid referring to them as "ID" fields.
  5. Then you must determine the ATTRIBUTE fields for each entity. This is where NORMALIZATION comes in. That term means that you must define Attribute fields so that they conform to a set of rules defined around 1960 by an IBM mathematician, Dr. E. F. (Ted) Codd, the "father" of relational database theory. He defined several numbered "normal forms" (1st Normal Form, 2nd Normal Form, ... up to 5) and the entire SQL query language is based on having your data in conformance with these rules, or at least the first 3 Normal Forms. For example, the 1st Normal Form (1NF) requires that data in a field must be "atomic" or "single valued"; in other words, you cannot store more than one indivisible value in a field of a record, so you cannot have a field that stores the names of your children, because the whole basis for using SQL queries to retrieve data is based on there being only one value in a field. 1NF also requires that every row (record) in a table be unique, because otherwise the queries could not distinguish among them. This is why it is so important to use a database-managed Primary Key, which insures that every row will be unique, at least in that field.
If this sounds esoteric it's because, as I said earlier, relational database design is anything but intuitive! Those who try to take shortcuts are sure to fail, if their database is at all complex.

I recommend that you study (not just scan) a couple of good online tutorials on relational databases and data normalization. It's the only path I know to achieve an acceptable design of a database as complex as yours seems to be.