I have created two tables as order & product. In order table, order details are stored & in product table according to order, product details are stored. Now I want to give row no./line no. for that product table. Say for order no. 100 there are 3 prodcuts-:
1 Nokia 8000 20
2 Samsung 5000 50
3 Sony 7000 30
So how to give row no/line no. for product in Mysql? Is there any function for the same? And also that no. must start from 1 for each order. Say after order no. 100, for order no. 101, it would be again start from 1,2...like this. I have maintained proper PK & FK...like PK of order is FK for product table..
My Schema is like this-: First I am inserting data in Order table using insert query so that will generate id i.e. mysql_insert_id() & I am using that for reference in Product table. Now my code is like this-:
global $id;
$id = mysql_insert_id();
$query2 = "SELECT a.name, a.sku, a.qty_ordered, a.price, a.row_total, a.base_subtotal,
b.base_shipping_amount, b.base_grand_total,".$id."
FROM sales_flat_order b
JOIN sales_flat_order_item a
ON a.order_id = b.entity_id
WHERE b.increment_id = ". $order_id ;
$result2 = mysql_query($query2);
while($row = mysql_fetch_array($result2))
{
$result_str_product .= "('". $row["name"] . "',". "'" . $row["sku"] . "'," . "'" . $row["qty_ordered"] . "',". "'" . $row["price"] . "'," . "'" . $row["row_total"] . "'," . "'" . $row["base_subtotal"]. "'," . "'" . $row["base_shipping_amount"] . "'," . "'" . $row["base_grand_total"] ."',". $id."),";
}
$query3 = "INSERT into product(name, sku, qty_ordered, price, row_total, base_subtotal, base_shipping_amount,base_grand_total,prod_f) VALUES ".$result_str_product;
$final_result = substr_replace($query3,";",-1);
$result_query_product = mysql_query($final_result);
Now my requirement is that I want to generate line no./row no. for these products like I explain above(Plz chek my above example that Nokia,Samsung. So How I can give row no./line no. for these item? Any in-built function for the same that I can use in while loop or any other approach?
