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 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 41

Thread: Storing prices

  1. #16
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    I have wrote some basic SQL using phpmyadmin but never added anything like that line there...Just got on properly today so going to play about with re-creating my table using SQL this time.

    Quote Originally Posted by Old Pedant
    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.)
    Going to read the mysql docs now and see what I can find. I've heard of cascade deleting, think it may have a been a topic on this forum and remember the functionality of it.

    Sounds really neat.

    Kind regards,

    LC.

  2. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    When in doubt, RTFM.

    I'd much rather trust the documentation than google for what somebody else may or may not have done right.

    http://dev.mysql.com/doc/refman/5.5/...ate-table.html

    Samples from that page:
    Code:
      | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
    ...
    reference_definition:
        REFERENCES tbl_name (index_col_name,...)
          [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
          [ON DELETE reference_option]
          [ON UPDATE reference_option]
    
    reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION
    ...
    Which means, for example, you can write:
    Code:
        ....
        CONSTRAINT FOREIGN KEY productid REFERENCES products(productid) ON DELETE CASCADE 
        ...
    but it also means you could write
    Code:
        ....
        FOREIGN KEY productid REFERENCES products(productid) ON DELETE CASCADE 
        ...
    or you could write
    Code:
        ....
        CONSTRAINT fk_img_prodid FOREIGN KEY productid REFERENCES products(productid) ON DELETE CASCADE 
        ...
    By giving the constraint a name, you could later remove or alter it by name. Not a likely thing to do with a foreign key, which is why I seldom bother, but it just gives you more options.

    So...MUCH better to get the *FULL* documentation with all possible options rather than just what one person chose to use.
    Last edited by Old Pedant; 11-08-2012 at 09:25 PM.
    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.

  3. #18
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Ah thank you, I am currently viewing this page: http://dev.mysql.com/doc/refman/5.5/...nstraints.html and found what you typed there.

    When you say giving the CONSTRAINT a name, is [symbol] where you put your name?

    Using mysql version 5.5 and it's telling me I have a syntax error near 'REFERENCES products(productID) ) ENGINE=INNODB' at line 1'...Not sure why as I already have my products table setup and ready, and the syntax looks to be right according to that website.

    Here is what I am typing in:
    Code:
    CREATE TABLE product_images ( imgID INT AUTO_INCREMENT PRIMARY KEY, productID INT, imgName VARCHAR(50), CONSTRAINT FOREIGN KEY (productID) REFERENCES products(productID) ON CASCADE DELETE) ENGINE=INNODB;
    Can you spot any issues?

    Kind regards,

    LC.

  4. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    ...ON DELETE CASCADE ) ENGINE = INNODB;

    You have the CASCADE and DELETE reversed.
    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.

  5. #20
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Doh!

    What concerns me more is that even before I added that line, it still wasn't working :s

    Edit: Seems to be working now since I swapped CASCADE and DELETE

    Maybe it wasn't working before because it needed that line? Before I was trying without the ON DELETE part.

    Kind regards,

    LC.

  6. #21
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    What I'm going to do now is manually add the data to the product_images table.

    What needs to be matching? Does the productID in products need to match the productID in product_images?

    Kind regards,

    LC.

  7. #22
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Yes, of course. And since you now have the CONSTRAINT there, you will get an error if you DON'T have a match. (Without the foreign key, you would have been able to put in any value for productID in the images table, but then it would never have worked when you tried to later SELECT matching images. THAT is why you want to use constraints.)

    One reason I recommend *NOT* using ON DELETE CASCADE is that when you are still developing your code, you might accidentally try to delete the wrong product. When ON DELETE CASCADE is present, the delete *will* work and you will have lost not only the product record but also the image record(s). Without ON DELETE CASCADE, if there is already an image that is referencing the product, the delete will fail. And you will do a face plant but say "thank goodness it didn't do what I asked it to do or I would have lost my data." This is especially true if, for example, you accidentally did DELETE FROM products and omitted the WHERE clause. You just deleted all your products and all your images. Except if you did *NOT* have ON DELETE CASCADE in their you didn't...the delete would fail.
    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.

  8. Users who have thanked Old Pedant for this post:

    LearningCoder (11-08-2012)

  9. #23
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    And by the by, once you have everything debugged and working, then you can alter the table/consraint to add the ON DELETE CASCADE.
    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.

  10. #24
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Thank you very much for the information.

    How would I now go about taking the on delete cascade off? Is there a certain command to use?

    At this moment, I'm probably just going to delete the table and re-write it without the on delete cascade, but like you said once I get to a point, I will want to turn it on again.

    Kind regards,

  11. #25
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    There's no "ALTER CONSTRAINT" command, so you just have to do:
    Code:
    ALTER TABLE DROP FOREIGN KEY nameyougaveit;
    
    ALTER TABLE ADD CONSTRAINT FOREIGN KEY name ...same as in create...
    Simple, really.

    This, too, is documented:
    http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
    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. #26
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    By the by, if you forget to give a name to the foreign key, MySQL gives it one for you.

    You can find out the name it used by doing
    Code:
    SHOW CREATE TABLE product_images;
    (or whatever the table name is).

    For example, using the SQL you gave in your prior posting, this is what I got via SHOW CREATE TABLE:
    Code:
    CREATE TABLE `product_images` (
      `imgID` int(11) NOT NULL AUTO_INCREMENT,
      `productID` int(11) DEFAULT NULL,
      `imgName` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`imgID`),
      KEY `productID` (`productID`),
      CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`productID`) REFERENCES `products` (`productid`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    Notice how all the defaults you did *NOT* specify (e.g., "NULL" and "CHARSET") are chosen for you as well and then shown via this command.

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

  13. Users who have thanked Old Pedant for this post:

    LearningCoder (11-08-2012)

  14. #27
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Thank you very much for your information.

    Half my trouble with looking up the documentation is I don't know the correct terms for things when it comes to SQL so it's hard to find things!

    Kind regards,

    LC.

  15. #28
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    925
    Thanks
    76
    Thanked 29 Times in 29 Posts
    Would you be able to explain the imgID field in the product_images table?

    Could it potentially look like this:

    Code:
    product_images (
    
    imgID        productID    imgName
      1                 1              bench1.jpg
      2                 1              bench2.jpg
      3                 1              bench3.jpg
      4                 2              table1.jpg
      5                 2              table2.jpg
      6                 2              table3.jpg
      7                 3              planter1.jpg
    )
    etc etc?

    Kind regards,

    LC.

  16. #29
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Sure.

    It *SHOULD* look like that.

    Any AUTO_INCREMENT field is one that MySQL fills in the value for you.

    You should *NOT* assign a value to that field!

    In other words, your SQL should look like this:
    Code:
    /* don't even mention the imgID column */
    INSERT INTO product_images( productid, imgName ) VALUES( 1, 'bench1.jpg' )
    
    or
    
    /* mention it, but assign NULL to it */
    INSERT INTO product_images( imgID, productid, imgName ) VALUES( NULL, 1, 'bench1.jpg' )
    
    or (not recommended, but legal)
    
    /* if you don't name the columns, then you must assign a value to each and every one, so you need NULL for imgID */
    INSERT INTO product_images VALUES( NULL, 1, 'bench1.jpg' )
    I always use the first form. It's clearer, to me, to simply not even mention the auto_increment field than it is to name it and then need to assign NULL to it.
    Last edited by Old Pedant; 11-09-2012 at 01:01 AM.
    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.

  17. #30
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    But the important thing is: Do *NOT* give a value for an AUTO_INCREMENT field (other than NULL) unless you have a really damned good reason for doing so.

    If, for example, you were to do
    Code:
    INSERT INTO product_images( imgID, productid, imgName ) VALUES( 77316, 1, 'bench1.jpg' )
    
    INSERT INTO product_images( imgID, productid, imgName ) VALUES( NULL, 1, 'bench2.jpg' )
    then the record for "bench2.jpg" would have an imgID of 77317.

    The principle: If you don't give a value to the auto_increment column, mySQL always assigns a value one more than the current maximum value in the column.
    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 2 of 3 FirstFirst 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
  •