Possibly a relational DB problem

[Ian N]

Hi,

I've recently become really stuck trying to program a shopping cart
for a site i'm designing.

I have 2 Tables

OrderItems
Orders

Which need to be updated during the update process, the data that is
to be updated is stored in an array.

As i understand it, i need to update the the Orders Table, get an
OrderID (which i have set to an auto increment number) then update the
OrderItems with the following info

OrderID
ProductID
ProductQty
CustomerID

I've done this using 3 fairly messy sql statements and feel their must
be a better way to do this using some sort of relational database?

I'm really stuck here, and would appreciate any help that anyone could
offer.

Kind Regards,

Ian

[Geoff M]

ia*******@gmail.com says...

As i understand it, i need to update the the Orders Table, get an
OrderID (which i have set to an auto increment number) then update the
OrderItems with the following info I've done this using 3 fairly messy sql statements and feel their must
be a better way to do this using some sort of relational database?

Without any clue as to what brand of database you are using (MySQL?), I
suspect that you should be able to do it with just 2 SQL statements.

1. Insert new record into Orders table (using a "returning" clause to set
an order_id variable if using Oracle or any DBMS that supports this
syntax, or if MySQL immediately using the mysql_insert_id() function); and

2. Insert records into OrderItems table.

Geoff M

[Geoff Berrow]

I noticed that Message-ID:
<13**************************@posting.google.com > from Ian N contained
the following:

As i understand it, i need to update the the Orders Table, get an
OrderID (which i have set to an auto increment number) then update the
OrderItems with the following info

OrderID
ProductID
ProductQty
CustomerID

Shouldn't CustomerID be stored on the Orders table?
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Ian N]

Geoff M <gm************@scu.edu.au> wrote in message news:<MP************************@news.individual.n et>...

ia*******@gmail.com says...

As i understand it, i need to update the the Orders Table, get an
OrderID (which i have set to an auto increment number) then update the
OrderItems with the following info

I've done this using 3 fairly messy sql statements and feel their must
be a better way to do this using some sort of relational database?

Without any clue as to what brand of database you are using (MySQL?), I
suspect that you should be able to do it with just 2 SQL statements.

1. Insert new record into Orders table (using a "returning" clause to set
an order_id variable if using Oracle or any DBMS that supports this
syntax, or if MySQL immediately using the mysql_insert_id() function); and

2. Insert records into OrderItems table.

Geoff M

Hi Thanks for the response Geoff,

How do i use the mysq_insert_id() function and wat will it do?
I use Access at work and would link the tables and do an inner join on
the tables in the sql statement. I've always preferred using Mysql and
PHP, but seem to have been totally stuck by this.

Cheers for the response though!

[Geoff M]

ia*******@gmail.com says...

1. Insert new record into Orders table (using a "returning" clause to set
an order_id variable if using Oracle or any DBMS that supports this
syntax, or if MySQL immediately using the mysql_insert_id() function); and

How do i use the mysq_insert_id() function and wat will it do?
http://www.php.net/manual/en/functio...-insert-id.php
I use Access at work and would link the tables and do an inner join on
the tables in the sql statement. I've always preferred using Mysql and
PHP, but seem to have been totally stuck by this.

MS Access ... <shudder> ... no idea how to do it on that. I use Oracle
and MySQL only.

Geoff M

[Geoff Berrow]

I noticed that Message-ID:
<13*************************@posting.google.com> from Ian N contained
the following:

How do i use the mysq_insert_id() function and wat will it do?

It does what it says in the manual...

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/