It doesn't need to be unsigned, but you could do that as you are unlikely to need negative prices.
use DECIMAL(5,2) which will allow prices up to 999.99 note you will have to trap any errors for prices over that amount or the insert will fail. you would handle that on the front end of the application.
Thank you very much. Is (5,2) the value for the "length/values" column when creating the DECIMAL field?
I will be manually inserting the data into the table so and at this moment in time I will never have a product over that amount, so do I need to still catch any errors?
If you manually enter no you won't have to trap errors as when you enter an incorrect amount you would see the error right afterwards.
DECIMAL(5,2) means a value of five digits with two of those digits after the decimal point). You can change accordingly. (5,4) would mean a maximum of 9.9999 could be held for example.
Longer answer re Decimal: MySQL stores decimal values in groups of 9 digits, either left of right of the decimal point.
So although MySQL *enforces* DECIMAL(5,2) to only allow 3 digits left and 2 right of the decimal point, there's really no point in specifying such a small size. It will occupy the same amount of disk space as DECIMAL(18,9). Now, you may very well NOT want to allow more than 2 digits right of the decimal point, so a reasonable compromise is DECIMAL(11,2) -- 9 digits left, 2 digits right. That will occupy the same amount of disk space as DECIMAL(5,2) but give you flexibility in case someday you actually have a price of 1372.99 (for example).
In short: There is little point in specifying a DECIMAL size smaller than DECIMAL(11,2) or DECIMAL(18,9) or any other combination that puts 9 digits left of the decimal point.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Because I may have various images of the same product, would it be best to create individual tables for the products, so that if the user wants to just view 'pet housing' for instance, it would be easier to select all the data relating to pet housing.
OR
Can I create an extra field within my original table, for example 'tags', and just insert ALL products into this table, but give them certain tags when doing so. This is so I can determine which products to display using the tag field.
Please let me know your thoughts because I'm stuck and can't carry on without knowing the best way to go about this.
Is this going to lead to some very complicated SQL statements?
Does the productID in my main table need to be the same as the imageID in the first table you told me to create, which needs to relate to the productid in the 3rd table?
Complete guess btw, had some problems before where by Fou-Lu gave me some very good but complicated code, and because I have little to no knowledge in database table relationships, the idea kinda went pete tong!
Definitely an area I need to study though so it my be a good idea to start that process.
And if you use "implicit joins" it can actually be pretty simple.
Example:
Code:
SELECT P.*, I.imgName
FROM products AS P, product_images AS I
WHERE P.productID = I.productID
AND P.product_name = 'left-handed widget'
Now, if you have multiple images, that is going to return all the data from the products table multiple times: Once per image.
But that's easy enough to sort out in your PHP code. If you are only getting a single product, just get the product info from the first record and then do a while loop to get all the imaName values, for example.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Just read some pages on relationships and going to give this a good go now.
So when creating the product_images table, what does the last line of code mean the CONSTRAINT FOREIGN KEY productID REFERENCES products(productID) part and how do I implement that? Do I need to type that in somewhere? (Baring in mind I am using phpmyadmin, I cannot write actual sql).
ummm...yes, you can write actual SQL with phpmyadmin. I don't use the product, but others have told me they have used actual SQL and it has worked. If you don't know how, ask.
But *PROBABLY* phpmyadmin has a way to specify foreign keys in its table designer. Almost surely, in fact. Try reading the docs.
The reasons you want a foreign key:
(1) MySQL will not then allow you to add an image unless you give it a *valid* productID. So you get automatic error trapping.
(2) If you try to remove a product and there are still images associated with it, you won't be allowed to. You have to remove all the images that reference a given productid before you can remove that product.
This is all called "referential integrity" and is designed to keep you from making mistakes!
(MySQL also supports "CASCADE DELETE" which means that if you remove a given product it will automatically remove all the images that reference that product. I don't recommend using this feature when starting out, but it's a nice weapon to have in your arsenal later.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
