List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 8 1999 4:29am
Subject:Re: syntax/sql question
View as plain text  
sam1600@stripped wrote:
> 
> Hello,
> 
> I have a syntax/sql question.
> 
> I have a database with the following three simple tables.
> 
> mysql> describe custorder;
> +------------+---------+------+-----+---------+----------------+
> | Field      | Type    | Null | Key | Default | Extra          |
> +------------+---------+------+-----+---------+----------------+
> | order_id   | int(20) |      | MUL | 0       | auto_increment |
> | package_id | int(20) |      |     | 0       |                |
> +------------+---------+------+-----+---------+----------------+
> 
> mysql> describe product;
> +--------------+---------+------+-----+---------+----------------+
> | Field        | Type    | Null | Key | Default | Extra          |
> +--------------+---------+------+-----+---------+----------------+
> | product_id   | int(20) |      | MUL | 0       | auto_increment |
> | product_name | int(20) |      |     | 0       |                |
> +--------------+---------+------+-----+---------+----------------+
> 
> mysql> describe product_package;
> +------------+---------+------+-----+---------+-------+
> | Field      | Type    | Null | Key | Default | Extra |
> +------------+---------+------+-----+---------+-------+
> | package_id | int(20) |      |     | 0       |       |
> | product_id | int(20) |      |     | 0       |       |
> +------------+---------+------+-----+---------+-------+
> 
> Problem:
> 
> I would like to add a row to the custorder table... and at the same time
> create a package ( add multiple rows to the  product_package table
> with the same package_id ). This product_package.package_id will get its
> value from the *current* auto_incrementing custorder.order_id that we
> are *currently* inserting.
> 
> I guess what I'm trying to say here is I want to insert the
> individual product.product_id's into the package.product_id and
> have each package.package_id have the same "value" ( for this particular
> insert... therefore making it a package ) and have that
> "value" be the same as the current custorder.order_id.
> And then insert this ID into the current custorder.package_id.
> ... But how would I get the custorder.order_id before the custorder
> row is actually inserted?
> ( I hope that made senSe :-).
> 
> I've tried different variations of LAST_INSERT_ID with no luck.
> 
> Here is a very "unreal\wrong" statement to best describe what I'm attempiing:
> 
> //$product is gathered from a drop down list box made from the product table
> 
> INSERT INTO custorder(package_id)
> 
>         {
>         INSERT INTO product_package(package_id, product_id)
>         VALUES (this.custorder.order_id,$product),(...),...
>         }
> 
>         VALUES (this.custorder.order_id OR package_id)



It looks like your problem is a circular dependency if I understand what
you are trying to do. You cannot know the value of order_id before you
obtain package_id and vice versa. Even if I do not understand you
correctly, I would still suggest redesigning your tables and/or your
insertion process. It looks too convoluted, and even if you figure it
out now, someone looking at it, possibly you in a month, will have a
hard time understanding what is going on. There has to be a simpler way.
Maybe if you share with us the original problem, we could help you find
a better solution.


-- 
Sasha Pachev
http://www.sashanet.com
Thread
syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionMike Machado8 May
Re: syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionJules Bean11 May