Help me piece together how to add an order to my orders database?

Databases
1

Hi all,

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 email=‘dummy@test.com’, 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.

Thanks!

2

when you add the customer, use mysql’s LAST_INSERT_ID() function to grab the value of the customer id

(i’m assuming mysql because you mentioned “auto-incremented” but there are similar functions in other database systems)

then use that retrieved value for the id column when you create the orderinfo row (it would probably be better named as customerid)

by the way, your schema is fine :slight_smile:

3

Thanks for responding, but I’m not quite sure where to go with it. From what I am seeing about that function, it only works when called within the same connection, otherwise it returns 0. Right now I’m trying to type in an order by hand through the mysql interface just to make sure I’ve got the query syntax correct, and trying to call that function is just returning 0 as expected.

Any thoughts?

Thanks!

4

what language are you going to use to run your e-commerce app? php?

you’ll want to use php’s mysql_insert() function instead

as far as testing out your sql manually, that’s fine, you can replace the SELECT for LAST_INSERT_ID with a simple SELECT to get the id based on a WHERE clause using the same values that you added the customer with