Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 41

Thread: Storing prices

  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts

    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....

    Appreciate any help you can offer.

    Kind regards,

    LC.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Short answer: Yes.

    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.

  • Users who have thanked Old Pedant for this post:

    LearningCoder (11-06-2012)

  • #7
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Thank you very much for explaining that. I will take your advice and set it to (11,2)

    Kind regards!

    LC.

  • #8
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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.

    Please let me know your thoughts because I'm stuck and can't carry on without knowing the best way to go about this.

    Thank you for any help

    Kind regards,

    LC.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #10
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    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.
    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.

  • #12
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    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.
    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.

  • #14
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    923
    Thanks
    76
    Thanked 29 Times in 29 Posts
    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.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,553
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    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.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •