SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Community Advisor ULTiMATE's Avatar
    Join Date
    Aug 2003
    Location
    Bristol, United Kingdom
    Posts
    2,160
    Mentioned
    46 Post(s)
    Tagged
    0 Thread(s)

    Beginner: Planning two related mySQL tables in PHP

    I have two tables, let's call them 'guitars' and 'photos'. The Guitars table has information on guitar, whilst the Photos table contains various photos of these Guitars. My upload page allows users to add information on these Guitars, along with a Photo.

    My issue is that there is no reason for information for a guitar to be added if that guitar is already in the database. In that case it'd be good if my insert queries would add the guitar information in the Guitars table only if the guitar is unique, and for the photo_id, but within the Photos table the guitars_id to contain the relevant ID.

    What is the best way to do this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    best way to do this is with INSERT... ON DUPLICATE KEY UPDATE

    adding the photo with the correct id may require a SELECT query after the INSERT in order to pick up the id, whether it be newly added or pre-existing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Community Advisor ULTiMATE's Avatar
    Join Date
    Aug 2003
    Location
    Bristol, United Kingdom
    Posts
    2,160
    Mentioned
    46 Post(s)
    Tagged
    0 Thread(s)
    Thanks for replying. I think I understand, although I think I've confused myself. Let's say my table was:

    PHP Code:
    $sql1 "INSERT INTO photos ('photo_id','photo') VALUES ('$photo_id','$photo','$car_id')";

    $sql2 "INSERT INTO cars ('car_id','name','speed') VALUES ('$car_id','$name','$speed')"
    Should a SELECT query appear in-between the two for the car query to then be changed to add "...ON DUPLICATE KEY UPDATE SET car_id = $new_car_id"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    whoa, wait a sec, what happened to the guitars?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Community Advisor ULTiMATE's Avatar
    Join Date
    Aug 2003
    Location
    Bristol, United Kingdom
    Posts
    2,160
    Mentioned
    46 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    whoa, wait a sec, what happened to the guitars?
    Ah, sorry about that. I was just using a simplified example instead of the real one I'm using (that's pretty long-winded and not available to me outside of work). In reality I've got a couple of tables that need altering and I've only really substituted the main one for Guitars and Cars, oddly enough two things just on my mind.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, it would go something like this --

    the cars table would have a UNIQUE key on name, which is the key that triggers the ON DUPLICATE action --
    Code:
    INSERT 
      INTO cars 
         ( name
         , speed ) 
    VALUES 
         ( '$name'
         , '$speed' )
    ON DUPLICATE KEY UPDATE speed = VALUES(speed)
    note no car_id is passed in (because you don't know if you're adding a new one or updating an existing one)

    once that has run successfully, you need to pull back the id, whether it was an old or new row --
    Code:
    SELECT id
      FROM cars
     WHERE name = '$name'
    then use this in the subsequent query --
    Code:
    INSERT 
      INTO photos 
         ( photo
         , car_id ) 
    VALUES 
         ( '$photo'
         , $car_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Community Advisor ULTiMATE's Avatar
    Join Date
    Aug 2003
    Location
    Bristol, United Kingdom
    Posts
    2,160
    Mentioned
    46 Post(s)
    Tagged
    0 Thread(s)
    Brilliant. Thank you very much!

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't want information added to the guitars table by your contributors. In third normal form you would have three tables; guitars, photos and information. e.g 1-many. If you don't know about database normalisation already I recommend reading up as good database design helps avoid lots of difficult application programming problems further down the development line.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by neildonald View Post
    In third normal form you would have three tables; guitars, photos and information.
    information??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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