Auto Increment in variable rows

[ds2r]

Hi guys, mysql noob here. :)

So here's my problem.

I have 2 tables, Order(order_id,product_id) and Product(product_id,product_name). For one order, I can have many products. So my question is how can I auto increment order_id? When I insert multiple products in the order table, it generates an order_id for each of the products. I want to have the same order_id for all the products that are inserted and then auto increment it for the next order.

I tried hardcoding it in php but is there anyway to do this in mysql?

Thanks in advance ^^

[urtrivedi]

You have to add one more table to your system say "order_hdr" with columns, order_id, order_date, customer_name, etc..... Here keep order id autoincrement.

rename your order table order_dtl table and without autoincrement in order_id,

first Insert record in order_hdr table. get order_id generated in some variable
now insert products and above order id in order_dtl table.

[tyson.crouch]

im not entirely sure, but you might want to consider a 3rd table which holds JUST the orderID.

So your tables will look like this;
Order(order_ID)
ProductOrder(order_ID, product_ID)
Product(product_ID, product_Name)

CREATE TABLE IF NOT EXISTS `order` (
  `order_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `product` (
  `product_ID` int(11) NOT NULL AUTO_INCREMENT,
  `product_Name` varchar(255) NOT NULL,
  PRIMARY KEY (`product_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `productorder` (
  `order_ID` int(11) NOT NULL,
  `product_ID` int(11) NOT NULL,
  PRIMARY KEY (`order_ID`,`product_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

So when you create a new order, you update the Order table so

// Create new order number
$sql_1 = "INSERT INTO order (`order_ID`) VALUES (NULL)";

// Get the last created order_ID
$sql_2 = "SELECT * FROM order ORDER BY order_ID DESC LIMIT 1";
$res_2 = // result from sql_2;

for($i = 0; $i < numberOfItems; $i ++){
    $sql_3 = "INSERT INTO productorder VALUES ( '".$res_2."', '" .product[i]. "');
}

my syntax might not be correct because im not used to using PHP, but the idea remains the same.

Steps:
1. create new order ID
2. Get new order ID from table.
3. Insert rows into new table using the new order number and products

[tyson.crouch]

sorry urtrivedi, i was typing this while you posted.

exactly the same ideas

[drjohn]

In other words it's a standard many to many relationship between orders and products, and so the standard linking table between orders and products is required, as described by urtrivedi .

[ds2r]

Thanks.