homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Updating a join table
Wayder




msg:4248723
 9:55 pm on Jan 3, 2011 (gmt 0)

I am using a join table to record which category an event belongs to.

category_id | event_id
5 | 1
1 | 1
2 | 3
6 | 4
7 | 4
etc.

I need to update this table if the event categories change and I have been trying to find a quick and simple method of updating the table with the new relationships without having to jump through too many loops.

I am thinking of deleting all the existing relationships based on event_id then inserting the new ones. Any suggestions on a better solution please.

Thanks

 

JAB Creations




msg:4253845
 7:01 am on Jan 16, 2011 (gmt 0)

Unless I'm mistaken there is no way to execute a write query using a JOIN.

What I think you're trying to say is that you want to update items in two tables with minimal use of queries (since they are write queries). If true then what you'll need to do is create an array in your scripting language and build your UPDATE query for each table, one query per table. I'm not sure what your level of skill is so if you're not sure how to change multiple entries on the same table in a single query I recommend making a test table and practicing with phpMyAdmin which while it's code is messy and full of ticks and the spaces are in the wrong places should at least get you on track to figuring out how to better construct your queries.

- John

johnblack




msg:4253860
 9:25 am on Jan 16, 2011 (gmt 0)

I think what he's asking is how to update a many-many relationship table, sometimes called a join table. In the example there would be a category and event table with a join table called category_event for example.

Other than deleting the existing records and inserting new ones as you suggest, there's no 'neat' way of doing it. The only thing I could think of is to update the existing records with the new category (assuming the input screen is event based). However what happens if the number of categories for the event changes? Then you still have to delete or insert records into the join table.

I think what you have come up with is the best way.

Wayder




msg:4253889
 12:59 pm on Jan 16, 2011 (gmt 0)

Thanks for the replies.

johnblack is 100% on the mark and it was the increase in the number of categories for a specfic event that I was thinking about. I decided to delete all then reinsert.

brycen




msg:4254944
 2:03 am on Jan 19, 2011 (gmt 0)

Wrap the delete and re-insert into a transaction, and the outside world will never know the difference.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved