# Storing prices

Show 40 post(s) from this thread on one page
Page 1 of 3 123 Last
• 11-06-2012, 04:49 AM
LearningCoder
Storing prices
Hi, I wanted to store some prices within a database. I looked into it and believe the correct way is to create a table field of DECIMAL type.

I need to be able to insert prices up to £999.99 Maximum. Not sure if I need to give the 'length/values' column any value or if I just leave it blank.

Noticed people are saying to set it to 'unsigned' also. Not quite sure what I'm doing here....

Kind regards,

LC.
• 11-06-2012, 01:56 PM
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.
• 11-06-2012, 05:49 PM
LearningCoder
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?

Kind regards,

LC.
• 11-06-2012, 06:38 PM
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.
• 11-06-2012, 06:50 PM
LearningCoder
Thank you very much!

When inserting manually, what format do I enter into the column?

Should I just be entering 149.99 for example, then when reading the data out, add the '£' sign?

Kind regards,

LC.
• 11-06-2012, 07:28 PM
Old Pedant

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.
• 11-06-2012, 09:00 PM
LearningCoder
Thank you very much for explaining that. I will take your advice and set it to `(11,2)`

Kind regards!

LC.
• 11-07-2012, 10:36 AM
LearningCoder
Just had another question come to me which relates to this very same database....

Here is my table structure:
Code:

```products (   productID(int)PK A-I   product_img(varchar 40)   product_name(varchar 40)   product_details(text)   product_price(DECIMAL 11,2) )```
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.

Thank you for any help

Kind regards,

LC.
• 11-07-2012, 02:34 PM
Do neither.
have your products table and remove the image column.

create an images table with two columns
imageid, pathtoimage

create a third table product_images with two columns
productid, imageid

you put a new row in this table for every image a product has.
• 11-07-2012, 07:41 PM
LearningCoder
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.

Kind regards,

LC.
• 11-07-2012, 08:09 PM
Old Pedant
Like this:
Code:

```CREATE TABLE products (     productID INT AUTO_INCREMENT PRIMARY KEY,   product_name VARCHAR(40),   product_details TEXT,   product_price DECIMAL 11,2) ) ENGINE=INNODB; CREATE TABLE product_images (     imgID INT AUTO_INCREMENT PRIMARY KEY,     productID INT,     imgName VARCHAR(50),     CONSTRAINT FOREIGN KEY productID REFERENCES products(productID) ) ENGINE=INNODB;```
It's called a "one to many table". One productID can have many imgID records.

It is *VERY VERY* standard database design. Read up on NORMALIZATION.

No, it doesn't lead to "very complicated SQL statements." It leads to quite common JOINs.
• 11-07-2012, 09:29 PM
LearningCoder
Thank you for that, I will google it and read up on it.

It might not seem very complicated for you but it is for me. I can just about write a select which orders and limits.

Must improve my sql.

Regards,

Lc.
• 11-07-2012, 10:39 PM
Old Pedant
Honest, it doesn't have to be complex.

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.
• 11-08-2012, 02:18 AM
LearningCoder
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).

Kind regards,

LC.
• 11-08-2012, 02:40 AM
Old Pedant
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.)
Show 40 post(s) from this thread on one page
Page 1 of 3 123 Last