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
