[Resolved] simultaneous updates across tables

jasonmills58

Hi. I'm working with a MySQL database and I have a master table and three other tables. The master table has a 'clientname' field which is used for all kinds of things. I would like to use it as the main column in the other three tables as well. The problem is if someone needs to edit the 'clientname' field - it will throw off the other tables. is there a way to tell the other three tables to automatically update their 'clientname' fields if the main table changes? Or do i have to just submit that 'clientname' edit four times?

thanks!

leatherback

Hi,

Better design would be to have a seperate table "Clients" where each name only occurs once (Unless the clients in your main table are already unique: No dup. records for one client? Then just use the record ID from that table). The first colum would be client_id, and auto_increment integer colum. You use the values in this colum in all the other tables to refer to a client.

jasonmills58

They do indeed all have their own unique id in the main table. But I'm not sure I get your meaning... How would I tell the other three tables about the client_id from the main table? For instance, one of the other tables is called 'calendar' with fields for clientname, eventname, eventdate, and eventinfo.

Are you saying I would initialize each of those entries (all of the sub-tables are new and empty) with the client_id instead of the clientname? How would I associate the clientname when reporting "SELECT * FROM calendar WHERE client_id='$client_id' " ?

thanks so much for your help!

leatherback

THat is right. Insert the clientid instead of the name.

When placing a query, you should use a join between client table and the table you are querying, on the ID's. Or you could query them as you suggested:

SELECT * FROM calendar, clients WHERE clients.id = calender.client_id and client_id='$client_id'

jasonmills58

that worked perfectly. the only weird thing is i had to pull the whole process out of the function and run it right on the page. I couldn't pass the session variable to the update function.

but thats ok, it works great now!

cheers!