A problem that I'm running up against is that I need to be able to add 1 record to 2 different tables, but when I view 1 record, I need to know that the other record exists. I thought that I'd add a column to the tables called link. Link would hold the name of the other table name and the unique id of the other record. I was thinking maybe like table_name*uid or tablename%uid. Then I could seperate them and get the location of the other record. The problem is, how can I do this?
I think that I need to:
1. INSERT the first record
2. get the Unique ID (AutoIndex) of that record
3. combine the UID with the tablename
4. INSERT the record into table 2, adding the 'link' we created to the link column
5. get the Unique ID (AutoIndex) of that record
6. combine the UID with the tablename
7. UPDATE the first record, adding in the link to the second.
The problem is, I can't figure out how to do steps 2 and 5. How can I find out what AutoIndex it assigned to the record? I could SELECT rows that have the same info, but there might be multiples. Any Ideas?Originally posted by AaronCampbell
A problem that I'm running up against is that I need to be able to add 1 record to 2 different tables, but when I view 1 record, I need to know that the other record exists. I thought that I'd add a column to the tables called link. Link would hold the name of the other table name and the unique id of the other record. I was thinking maybe like table_name*uid or tablename%uid. Then I could seperate them and get the location of the other record. The problem is, how can I do this?
I think that I need to:
1. INSERT the first record
2. get the Unique ID (AutoIndex) of that record
3. combine the UID with the tablename
4. INSERT the record into table 2, adding the 'link' we created to the link column
5. get the Unique ID (AutoIndex) of that record
6. combine the UID with the tablename
7. UPDATE the first record, adding in the link to the second.
The problem is, I can't figure out how to do steps 2 and 5. How can I find out what AutoIndex it assigned to the record? I could SELECT rows that have the same info, but there might be multiples. Any Ideas?
quite simple...allow me to demonstrate:
mysql_query("INSERT INTO table (cols) VALUES (values)");
$uid = mysql_insert_id();
$link = 'table'.'%'.$uid;
mysql_query("INSERT INTO table2 (cols,link) VALUES (values,'$link')");
$uid2 = mysql_insert_id();
$link2 = 'table2'.'%'.$uid;
mysql_query("UPDATE table SET link = '$link2' WHERE id = '$uid'");
but I wouldn't use a link column like you describe...instead I'd use a link table with a setup like:
id | table | uid
that way if you change your table names it's less hastle to update one table then to update x amount of columns.
hth
Thanks a TOn for the answer...so you think that I should have the 'link' column have an ID in it that corresponds to a 'link' table, which holds the same info that I was originially going to put into the link column?
Seems like I'd have a whole lot more inserts and selects if I did it like that. Also, all the tablenames are read in from a config file into an array, so it'd be pretty easy to loop through the tables, and update them.
Thanks again.
I think that I need to:
1. INSERT the first record
2. get the Unique ID (AutoIndex) of that record
3. combine the UID with the tablename
4. INSERT the record into table 2, adding the 'link' we created to the link column
5. get the Unique ID (AutoIndex) of that record
6. combine the UID with the tablename
7. UPDATE the first record, adding in the link to the second.
The problem is, I can't figure out how to do steps 2 and 5. How can I find out what AutoIndex it assigned to the record? I could SELECT rows that have the same info, but there might be multiples. Any Ideas?Originally posted by AaronCampbell
A problem that I'm running up against is that I need to be able to add 1 record to 2 different tables, but when I view 1 record, I need to know that the other record exists. I thought that I'd add a column to the tables called link. Link would hold the name of the other table name and the unique id of the other record. I was thinking maybe like table_name*uid or tablename%uid. Then I could seperate them and get the location of the other record. The problem is, how can I do this?
I think that I need to:
1. INSERT the first record
2. get the Unique ID (AutoIndex) of that record
3. combine the UID with the tablename
4. INSERT the record into table 2, adding the 'link' we created to the link column
5. get the Unique ID (AutoIndex) of that record
6. combine the UID with the tablename
7. UPDATE the first record, adding in the link to the second.
The problem is, I can't figure out how to do steps 2 and 5. How can I find out what AutoIndex it assigned to the record? I could SELECT rows that have the same info, but there might be multiples. Any Ideas?
quite simple...allow me to demonstrate:
mysql_query("INSERT INTO table (cols) VALUES (values)");
$uid = mysql_insert_id();
$link = 'table'.'%'.$uid;
mysql_query("INSERT INTO table2 (cols,link) VALUES (values,'$link')");
$uid2 = mysql_insert_id();
$link2 = 'table2'.'%'.$uid;
mysql_query("UPDATE table SET link = '$link2' WHERE id = '$uid'");
but I wouldn't use a link column like you describe...instead I'd use a link table with a setup like:
id | table | uid
that way if you change your table names it's less hastle to update one table then to update x amount of columns.
hth
Thanks a TOn for the answer...so you think that I should have the 'link' column have an ID in it that corresponds to a 'link' table, which holds the same info that I was originially going to put into the link column?Seems like I'd have a whole lot more inserts and selects if I did it like that. Also, all the tablenames are read in from a config file into an array, so it'd be pretty easy to loop through the tables, and update them.
Thanks again.