SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Capture.GIF

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •