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]

Re: Possibly a relational DB problem

iannorton@gmail .com says...
[color=blue]
> 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[/color]
[color=blue]
> 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?[/color]

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]

Re: Possibly a relational DB problem

I noticed that Message-ID:
<139b7ea9.05020 71431.265c775c@ posting.google. com> from Ian N contained
the following:
[color=blue]
>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[/color]

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]

Re: Possibly a relational DB problem

Geoff M <gmuldoonnospam @scu.edu.au> wrote in message news:<MPG.1c72a aeba6825ce39896 af@news.individ ual.net>...[color=blue]
> iannorton@gmail .com says...
>[color=green]
> > 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[/color]
>[color=green]
> > 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?[/color]
>
> 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[/color]

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]

Re: Possibly a relational DB problem

iannorton@gmail .com says...
[color=blue][color=green]
> > 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[/color][/color]
[color=blue]
> How do i use the mysq_insert_id( ) function and wat will it do?[/color]

http://www.php.net/manual/en/function.mysql-insert-id.php

[color=blue]
> 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.[/color]

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

Geoff M

[Geoff Berrow]

Re: Possibly a relational DB problem

I noticed that Message-ID:
<139b7ea9.05020 91355.cf8ceaa@p osting.google.c om> from Ian N contained
the following:
[color=blue]
>
>How do i use the mysq_insert_id( ) function and wat will it do?[/color]

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/