SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Inserting Help

  1. #1
    SitePoint Member
    Join Date
    Dec 2005
    Location
    St. Paul, MN
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting Help

    Not sure if this should be in MySQL or PHP so if I put it in the wrong place please move it to the correct location.

    I have 1 database with 2 tables:

    jobs table includes the following:
    job_id as the pk
    job_name

    photos table includes:
    photo id_ as the pk
    photo_name
    photo_description

    I might not understand normalization correctly, but how I understand it is since multiple photos would have the same job_name a different table is required for that.

    I have a form that requires the user to enter in a photo description and a job name if it is a new job - otherwise there is a drop down list that shows job names already entered. Where I am running into the problem is each time a job_name is entered it creates a new row - even if it is a duplicate name. Right now because it is entereing in a new row each time even if a duplicate job name the user has multiple of the same job name in the drop down.

    What I am trying to do is is not have the same job listed in 15 different rows if there is 15 different photos from the same job. I have no idea how to go about associating 15 photos to the same job_id and job_name so a new row is not created each time.

    If looking at the query I am using is needed please let me know and I will add it.

    Thanks in advance for any help with this.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are working with a one-to-many style relationship here. Unforntunately, you don't have a foreign key set, so there is nothing to relate the two tables together.

    You should add a field in the photos table called fk_job_id which will hold a reference to the job_id of the photo.

    sample:

    job_id 1

    photo_id 1
    job_id 1

    photo_id 2
    job_id 1


    now, when you use a left outer join on the tables, it will join the photos to 1 record in the jobs table.

    Of course, this assumes it was inserted correctly.


    What you need to do is to program a check if the job name exists.

    select job_name from jobs where job_name = '$_POST['job_name'];

    if you get a record, DO NOT do an insert.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually you don't have to check to see if a job exists or not, since on the form there is a choice between an existing job (dropdown) and a new job (input field)

    if the user elects an existing job and fills in a new job name, that's a form validation error and shouldn't even reach the database

    if the dropdown is selected, and the input field is blank, it's not a new job

    if the dropdown is not selected, and the input field is not blank, it's a new job, so you don't have to check to see if it's there already
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Buckethead
    What I am trying to do is is not have the same job listed in 15 different rows if there is 15 different photos from the same job. I have no idea how to go about associating 15 photos to the same job_id and job_name so a new row is not created each time.
    when you get the results from mysql it will have 15 rows with the job name. in your host language, you detect the change in the job name column and display the header.

  5. #5
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    actually you don't have to check to see if a job exists or not, since on the form there is a choice between an existing job (dropdown) and a new job (input field)

    <snip />

    if the dropdown is not selected, and the input field is not blank, it's a new job, so you don't have to check to see if it's there already
    IF, of course, you turst your users.

    Once that list gets huge, it will be easy to over-look a name that is already on the drop-down. And i don't believe in client side checks as the only solution. Hence the server-side check.

  6. #6
    SitePoint Member
    Join Date
    Dec 2005
    Location
    St. Paul, MN
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok sounds like I have alot more learning on MySQL to do. But if I am understanding this correct I want to add another field to my photos table so it would insert the job_id into it? And at the same time I would want to check to make sure that the $_POST['job_name'] does not match any job names in the table and if it does not match then do a insert?

    Thanks again for the help. This is my 1st time working with MySQL so kinda learning as I go here. Working with 1 table is easy enough, but from what I am reading this is the correct way to do this with 2 tables and would rather learn the correct way to start out.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    IF, of course, you turst your users.
    i turst 'em all the time

    yes, of course, you're right, it is imperative to check on the server side

    i still wouldn't do a select first, though

    i'd have a unique constraint on the job name, and just go ahead with the insert -- if it fails then trap the error and issue "job already exists" message

    insert + errortrap is approx twice as efficient as select + errortrap + insert

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    say, do i maybe use too many smilies?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, if you didn't click on the smileys all the time, you could be replying to other threads and thus be on 11,000+ posts instead of only 10,000 !


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
  •