Not having foreign keys is really

[Anon]

messing me up.

I have the following tables:

Order(orderID (pk), custFName, custLName)
Status(statusID (pk), statusDescrip)
History(user, notes, date)

On the first page, the user will insert the customer First and Last name and notes. The OrderID is assigned by auto_increment. How do I write this insert statement since the information will go to two different tables? I'm not used to not having foreign keys and it is really throwing me for a loop.

Thanks!

[Anon]

I don't see linking fields there anyway???

You need to insert into the order table, read in the orderid that was assigned (mysql_last_id)and then use that as the foreign key when you insert the status or whatever.

Of course, the above needs to be done in a transaction, otherwise many problems can occur. However, MySQL doesn't really have transactions either, so you're screwed. If you make sure all the tables involved are InnoDB, you can use InnoDB transactions.

Or switch to Postgres or something.

Chris

[Anon]

First of all, I'd say you need to revise your table structure:

Customers (CustID (pk, auto_increment), CustLastName, CustFirstName)
Orders (OrderID (pk, auto_increment), CustID, StatusID)
Statuses (StatusID (pk), StatusDescription)
History (CustID, Notes, Datestamp)

Referencing your customers by CustID rather than lastname/firstname will ensure that your database is more fully normalized. For more info, do a Google search on "database normalization".

Your customer enters their identifying info; if they don't exist, do an INSERT, then use mysql_insert_id right after the INSERT to get the auto_number you just created. Then you can INSERT into the Orders table using the CustID, and retrieve the OrderID using mysql_insert_id again, and so on. You get the picture.

As Chris noted, this is not really safe outside a transactional environment. Unfortunately, your web provider may not offer you any alternatives (InnoDB tables may not be available, for instance).

[Anon]

Thank you for the information regarding the normalization of my tables. I'm more of a network admin than a DBA or programmer, so I've got a lot of learning to do. It's been years since that DB class I took in college.

As far as the transactional issue, this site will be run in-house. I'm running an IIS server, mySQL and PHP. I'm just now experimenting with mySQL and PHP, is there something you would suggest to try and achieve the transactional situation that would be ideal?

Thanks again,