insert into 2 joined tables

tombell

Hi,

I have this problem:

I have two tables with the following details:

Clients
cid int not null auto_increment primary key,
clientname varchar(50) not null
email varchar(100) not null

Orders
oid int not null auto_increment primary key
order varchar(200) not null
date date
cid int [foreign key]

The form I have, has a field for the client name and a textarea for the order.

I know how to insert the clientname and order in the corresponding fields but I don't
know how to insert the corresponding cid in the Orders table. I mean, if the clientname already exists in the clients table and he makes a new order, how do I insert the order and the appropriate cid in the orders table?

I would appreciate some help.

Thanks

matto

you can do a search for the cid in the first place.

but a better way is not to use a text input for the client but a select box (or radio buttons) populated from the client table in the database.

Choose the client:
<select name=cid size=1>
<option value=1>John Doe</option>
<option value=2>Don Joe</option>
(...)
</select>

-> now you can insert the foreign key directly

Sxooter

What database are you using? The methodology of accessing the autoincrementing ID is different for different enginers.

tombell

I use mysql database and php 4.2.2

Sxooter

simple, use mysql_insert_id to get the id of the last inserted row.

tombell

Yes but what if the client already exists?

Sxooter

Just do a search on the client name first, and if it matches, then grab the id.

jeremuck

you will have to search for the customer to get the ID first. Once you have the id, you insert the cid into the other table along with the other information. (Two separate queries). The wildcard here is that the customer may or may not exist. This means, FIRST you look up to see if the customer exists. If he does, then you use their cid, if you find nothing, go ahead and Insert a new one. Then search for the customer using all the information you just used to insert. Use the resulting cid for the insert into the orders table.

What would make the whole thing a lot easier is to keep track of the client from the very very beginning.

Have the user sign in. Keep track of which one she is using a session variable that has her cid.

You didnt' mention anything about username or password. I would add a password field, and use the email as the username.

Have them sign in before you order. Use her sign-in info to look up the cid. Keep track of it using session_start() session_register("cid"); When she makes an order, you've already got the cid.
If sign-in fails, offer to sign up.

[deleted]

Check out

http://hotwired.lycos.com/webmonkey/99/21/index3a_page5.html?tw=programming

To see how this is done.

tombell

Thanks for all the answers guys.
They really helped