Treehouse: Grow your CSS skills. Land your dream job.

Need some advice (MySQL)

  • # October 7, 2010 at 4:16 pm

    Ok, I’ll try to explain this the best I can.

    I’ve got a table for “Jobs” that has columns for “id” (auto incrementing unique key), “client name”, “address”, “phone”, etc…

    I’ve got some fields for “additional items” on an “edit job” page that I need to save in my database. These additional items need to be associated with that particular job id. The fields for the additional items is “Item Name” and “Item Price”. There are 10 of these on my page.

    When it comes time for someone to enter in the series of item names and prices, they go to the “Edit Job” page where the data for that particular job is shown, and they can enter in the item name and price for each item. All 10 items may or may not have data.

    My idea was that I’d have a separate table that had columns for id (auto incrementing unique key), jobid (so that each item is associated with the correct job), item name, item amount. The problems I’ve been having with thinking this through is:

    a) I’m guessing I could just loop through 10 times for each field to see if it has a value and wasn’t left blank, so that it just won’t make 10 rows even if there is only 1 additional item the user put in.

    b) I’m also guessing I’d need to use REPLACE or INSERT ON DUPLICATE UPDATE to create the rows, since this is on an edit page, the rows may or may not exist.

    I guess what I’m asking is, am I on the right track? How would you go about doing what I’m trying to do? Am I forgetting anything?


    # October 7, 2010 at 5:14 pm

    My MySQL skills are a bit rusty, haven’t used that for a long time. I’ll see if I got some time to look into it though, if no one else has replied yet, as I do find it interesting.

    # October 8, 2010 at 4:41 pm

    I don’t understand what good it would have 10 items to edit on an edit page if you have less than that… I would only show what is available to edit and develop a method for creating new ones… then you only have to keep track of the edits made to actual records. Otherwise I don’t see a great way of keeping track of what records are new and which ones are edits without doing a lot of validation and checks to tell if you should perform one action or the other.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.