Welcome to WebmasterWorld Guest from 54.144.124.152

Forum Moderators: open

Message Too Old, No Replies

Updating a join table

     

Wayder

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

10+ Year Member



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

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

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

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

10+ Year Member



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

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

10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month