I want to link a number of tables together.

For example. I have a person table. Each person has a number of addresses (Home, Business, etc), E-mail addresses, telephone numbers, etc. So tbl_person has a unique key Person_ID (auto increment). Up to now I have been re-reading the tbl_person to get this key to write to tbl_address (unique key is Address_ID), etc.

I have now found that I can use $PersonID=mysql_insert_id() to get the id of the last insert row.

Will this still work (i.e., return the id I just added) if there are 5,000 people on at the same time updating the tbl_person or will it return the last id added by the last person adding to the table?

Next I want one tble_address for person addresses and supplier addresses, etc.

As I have no control of the key generated (i.e., tbl_person will have a key of 1 and tbl_supplier will have a key of 1 (not related). Therefore, I would need to write to Person_ID on tbl_address when adding a person relationship and Supplier_ID on tbl_address when adding a supplier relationship. I would therefore need a field for each relationship.

Another way is to set up a table of keys tbl_key_person_address (Tbl_ID, Person_ID and Address_ID), tbl_supplier_address (Tbl_ID, Supplier_ID and Address_ID). This way I would need to have a table for each new relationship. However, I would not need to add a new field to each table(i.e., tbl_address, tbl_telephone_number, etc) when ever I required a new relationship (e.g., tbl_contacts).

Am I on the right track or is there a far better way of doing this?

Regards,

Glyn

For example. I have a person table. Each person has a number of addresses (Home, Business, etc), E-mail addresses, telephone numbers, etc. So tbl_person has a unique key Person_ID (auto increment). Up to now I have been re-reading the tbl_person to get this key to write to tbl_address (unique key is Address_ID), etc.

If you have a person_ID then I would have a field in my address_table to reflect this which I would also call person_ID and I would hook them together like this:

select * from person_table p left join address_table a on p.Person_ID=a.Person_ID;
You can add a where clause at the end to single a specific user out.

The basic gist of whats going on here is it will grab the perons details and join all other details onto it with "left join address_table a on p.Person_ID=a.Person_ID". It can be extended further to left join other tables. (check out man pages for your DB about joins)

I think thats what your after.

Will this still work (i.e., return the id I just added) if there are 5,000 people on at the same time updating the tbl_person or will it return the last id added by the last person adding to the table?

I think you will find that you would see that last entry been updated each time a user hit it (no matter which user).

It appears your making llife hard for yourself mate.

If you have related tables then see what you can do to place a common identifier (or in DB terms a key) within them that will reflect which tuple is related to other tuples in other tables.

This way you can grab it all using joins like i demonstrated above. The data is linked by the ID which now removes any need for you to generate new keys while attempting to hook it all together (which I guess you are having no luck with).

The best practice with databases is to make them do as much of the work as possible. Of course if your not too clued up on DB syntax and functionality this can be a nightmare, but thats what places like WMT are for.

Hope that helps

Ibbo

use a left join
e.g:

table 1 =
name varchar(90) NULL,
age mediumint(2) NULL,
color varchar(70) NULL

table 2 =
name varchar(90) NULL,
number mediumint(90) NULL,
address text NULL

$sql = mysql_query("SELECT a.name, a.age, b.name, b.number FROM table_1 a LEFT JOIN table_2 b ON a.name=b.name where name = 'john'");

Thanks Guys,

First, I take it then that "$PersonID=mysql_insert_id() " should only be used if you only have one person using the DB at a time. Is that correct?

Second:

I know how to do left joins. What I am after here is "best practice".

I understand that I can have:
tbl_person:
Person_ID (Primary Key, autoincrement)
other columns

tbl_supplier
Supplier_ID (Primary Key, autoincrement)
Person_ID (Index)
other columns

tbl_address
Address_ID (Primary Key, autoincrement)
Person_ID (Index)
Supplier_ID (Index)
other columns

Here we have a table of persons (who have addresses) and a table of suppliers (who can have people working for them who have addresses and the supplier themselves have an address) and a table of addresses (people addresses and supplier addresses).

What I am trying to get away from is having to add a new column to tbl_address every time I have a new relationship.

One way is to have tables of keys:
tbl_person:
Person_ID (Primary Key, autoincrement)
other columns

tbl_supplier
Supplier_ID (Primary Key, autoincrement)
Person_ID (Index)
other columns

tbl_address
Address_ID (Primary Key, autoincrement)
Supplier_ID (Index)
other columns

tbl_person_address_keys
PAK_ID (Primary Key, autoincrement)
Person_ID (Index)
Address_ID (Index)

tbl_supplier_address_keys
SAK_ID (Primary Key, autoincrement)
Supplier_ID (Index)
Address_ID (Index)

tbl_supplier_person_keys
SPK_ID (Primary Key, autoincrement)
Supplier_ID (Index)
Person_ID (Index)

Then when ever I want to find a specific relationship it is a simple matter of joining three tables (i.e., tbl_person, tbl_address and tbl_person_address_keys).

The tricky part is knowing what the Primary Keys are when you INSERT into the table as they are created for you automatically (autoincrement) and are therefore effectively hidden from you at the time of insert.

This was where "$PersonID=mysql_insert_id() " came in. If that only returned the value of the Primary Key to the row you just INSERTED then that would make the whole thing a lot easier. However, if it is just as likely to return the Primary Key of the row created by someone else, in the split second between you INSERTING and calling "$PersonID=mysql_insert_id() ", then it is fairly useless.

I suppose the alternative is to have a table of keys that you read and increment and use this key as when you perform an INSERT rather than use autoincrement.

tbl_next_key
Next_key (Primary Key) no other rows.

Or is there a better way to do this?

Your views and suggestions are all greatly appreciated,

Glyn

Hi ibbo,

"I think you will find that you would see that last entry been updated each time a user hit it (no matter which user)."

You are incorrect.

In reading the mysql manual itself, there is some enlightening information on the fact that this does appear to be totally safe to use because it is on a per-connection basis.

Here's the relevant quote from the manual on LAST_INSERT_ID() which is located here: http://dev.mysql.com/doc/mysql/en/Information_functions.html

"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."

Sounds safe to me. I couldn't imagine this would be done any other way *but* on a per-connection basis, otherwise chaos would ensue.

So I hope you have all learned something.

Regards,

Glyn