I'm trying to put together a pseudo e-commerce solution for a school project. I think I have the schema down but I'm having a bit of trouble figuring out how to actually insert orders into the db.
Here is a diagram of my schema.
The "status" table is a lookup table for indicating the current state of an order, denoted by a numeric code. 1 - RECEIVED, 2 - CANCELLED, 6 - SHIPPED, etc.
id and orderno are both auto-incremented fields.
At this moment I am stuck trying to construct a query or sequence of such that would actually populate the database with a single order.
I made a query to enter customer info. That much works; he gets assigned an id number.
insert into customers set firstname.lastname@example.org', name='Ernie Bert';
Now I assume I need to make a new entry in the orderinfo table to establish a new orderno for this customer, so I can tie individual line items to it. How do I do this without having to personally look up customer.id and insert it in the query? Also how can I make sure the orderinfo table is treating the id field as customer.id? I used a series of alter table commands to establish foreign keys but I don't see any indication anywhere that it actually worked.