Inserting into multiple tables

[meshhat]

A bit of a newbie. I would like to insert information into multiple tables from one form. I can insert the information just fine. However, because the tables are related with id's, I'm having a bit of a problem.

For example, I have 2 tables and the corresponding fields:
Table 1: Person
Fields: Personid, Name, Company

Table 2: Company
Fields: Companyid, Companyname

Person.company and company.companyid should correspond. For example, many people could work at Ajax (companyid =1, meaning company.companyid and person.company both equal 1).

When inserting, this would require the application to first query the 'company' table, checking to see if the company name already exists. Correct? If it does exist, it would pull the id and insert into the 'person' table. If it doesn't exist, it would insert the company info and corresponding id into 'person'.

I just don't understand how I can do the initial query, pull the id (if existing) and insert it into the 'Person' table. Anyone help guide me through this?

[jatar_k]

Welcome to WebmasterWorld meshhat,

you would need something to query with, such as the company name from your form. You could then issue a query similar to this

select Companyid from Company where Companyname='valuefromform';

then you could use something like mysql_num_rows [php.net] to see if anything was returned. Let's say we pulled the result to $query

if (mysql_num_rows($query) > 0) {
// grab your id from $query since something was returned
} else {
// insert new company row and possibly use mysql_insert_id [php.net] to get the id from your insert
}

then you have your company id and can pop it into your insert for your Person table