[SOLVED] How to structure multiple insert query?

[phpdragon]

How would I structure the insert query for multiple inserts to a table in a mysql DB.

 

Information is coming from session cart data and there is a session variable ($ses_basket_items) which has the amount of unique items in the cart to insert. I know how to retreive the session data for each item, but I can only get as far as the first item being inserted into the DB and the rest are ignored.

 

[Q695]

$sql="INSERT INTO database_name (uid, pw, e_mail)

VALUES ('$uid', '$pw', '$e_mail')";

[phpdragon]

Thanks for your reply I have tried this inside a for statement, but it only inserts the first item, and not any others that may be in the cart.

 

I use an or die option for the errors and it gives me an error message stating the next row of data to insert, on the examples I have seen you need to put a comma after each row insert except the last, but I dont know how to acheive this.

[Q695]

you could either base it off a session id that ties to their e-mail address, or something similar that stores the shopping cart for the lifetime of the site then you monitor if the order has been filled or not, just in case they decide they want to finish the order later.  Hey, space is cheap these days.

[phpdragon]

This is what I am trying, but I seem to be missing something that inserts each new entry

 

for ($i=0; $i<$ses_basket_items; $i++){
  $price=sprintf("%01.2f",$ses_basket_price[$i]);
  $quantity=$ses_basket_amount[$i];
  $code=$ses_basket_stockcode[$i];
  $itemID=$ses_basket_id[$i];
  $product=$ses_basket_name[$i];
  $unit=sprintf("%01.2f",($price/$quantity));
// add item to users order table
$sql="INSERT INTO $orderTable VALUES ('$thisID', '$product', '$itemID', '$quantity', '$code', '$unit')";
$result=mysql_query($sql) or die ("Error in query: $sql");
}

 

This only inserts the first row in the order, then produces the following error message

 

Error in query: INSERT INTO 5_orders VALUES ('35', 'moo', '8', '11', '23153', '20.00')

 

which is the correct information for the second row of test data I was trying to add from my session cart.

[phpdragon]

Should I be trying a different way to input all the rows at once or is this the correct method?

[Mchl]

Do not query inside loop.

You can use query like this one"

 

INSERT INTO 5_orders VALUES ('35', 'moo', '8', '11', '23153', '20.00'),('36', 'foo', '8', '11', '23153', '20.00'),('37', 'boo', '8', '11', '23153', '20.00');

 

For now change:

 

$result=mysql_query($sql) or die ("Error in query: $sql");

 

to

 

$result=mysql_query($sql) or die (mysql_error().": $sql");

[phpdragon]

Thanks Mchl, it appears the problem lies in my first value being the same for each item, how my cart works is i have an orders table which stores orderid userid and some other details, then i have an orders table for every user eg 1_orders, 2_orders etc which is the userid followed by the underscore orders. this table stores information about the products ordered like productid quantity, charge price and stock code

The orderid relates to the orderid in the orders table so in the users orders table the orderid will be repeated many times to tie it into each unique order.

I have this set as the primary key in mysql table, I am now guessing I should have the table set some other way.

 

here is the new error message i get

 

Duplicate entry '38' for key 1: INSERT INTO 5_orders VALUES ('38', 'moo', '8', '11', '23153', '20.00')

[redarrow]

That means the database field name is unique in the database.

 

Your have to alter the database to non unique for that field.

 

$thisid needs to be non unique to do it your way.

 

Also suggest you learn to name your insert's, to the database

[Mchl]

Thanks Mchl, it appears the problem lies in my first value being the same for each item, how my cart works is i have an orders table which stores orderid userid and some other details, then i have an orders table for every user eg 1_orders, 2_orders etc which is the userid followed by the underscore orders. this table stores information about the products ordered like productid quantity, charge price and stock code

 

This is wrong design.

You should store all orders in one table.

[phpdragon]

It works now, I had to change the KEY in the userID_orders table from PRIMARY to INDEX on the orderID and all entries went straight in.

$thisid gets selected from the orders table after the insert of the order, then placed in the userID_orders table for each product in the related order.

 

I also took redarrows advice about naming the entries and have modified it to include the naming. This problem has plagued me for about 5 days now, thank you.

 

The current table design for orders is

 

TABLE orders (orderID, userID, person, dispatch, payment, cName, cPhone, cAccount, cReference, street, suburb, state, country, pcode, datestart, status, lastaction)

 

TABLE userID_orders (orderID, productName, productID, quantity, stockcode, price, allocated)

 

This site is for a B2B system where an importer/exporter sells to retailers, the userID_orders table is created when a user is aproved by an admin as a retailer, then all their orders are logged in the orders table, and all the products they order are logged in their userID_orders table. I did this because I beleive for reporting instances this would improve the performance, it also gives me easier flexability and performance for editing/tracking/viewing individual orders and their status.

I am open to comments why this may not be a good idea and why another method would be more preferential for this scenario?

I appreciate your input and your help, thank you

[redarrow]

Well done, But it so true you need to sit with paper and pen and

work out the database structure correctly, Because in the future your need some info trust.

 

 

[Mchl]

I am open to comments why this may not be a good idea and why another method would be more preferential for this scenario?

I appreciate your input and your help, thank you

 

While it might seem like a good idea, in reality it is (most likely) not. For example, how do you show all orders for given productName ?

Read on database normalisation. When done correctly, it will give you both performance and ease of use.

[phpdragon]

For example, how do you show all orders for given productName ?

I have another table called productRank

 

TABLE productRank (orderID, productID, quantity, date)

 

This table was created to show popularity of not only the product in general, but also show what products certain users prefer aswell, it can also show a seasonal trend based on date.

Another reason for the extra tables is i dont wish to keep repeating other information like the courier and shipping details for each product when really its a per order not per product sold.

Your comments are well taken and apreciated is their anything else you may believe will cause me some issue.

[Mchl]

You could do all that with one table storing all orders

 

I'm not going to try to convince you. Just read about normalisation. Most articles about it give also reasons why it is useful.

 

[Q695]

Mchl:

Why don't you ever want to query within a loop?

[Mchl]

Because it's slow.

Of course sometimes there is no other way, but in general it should be avoided.

 

Other consideration: some hosting companies limit the number of queries user can perform within an hour (MySQL has a built in mechanism for that).

[Q695]

How can they let you do that, if you're paying for the hosting service?  Why wouldn't they just limit your bandwidth instead?

[Mchl]

That's the fine print in some of those 'unlimited disk space and bandwidth' hosting companies. You get for what you pay.

[phpdragon]

Thank you so much for your help.

How would I move the same query outside the loop with a dynamic amount of entries involved?

[Mchl]

I usually do it like this

 

$sql="INSERT INTO $orderTable VALUES";
for ($i=0; $i<$ses_basket_items; $i++){
  $price=sprintf("%01.2f",$ses_basket_price[$i]);
  $quantity=$ses_basket_amount[$i];
  $code=$ses_basket_stockcode[$i];
  $itemID=$ses_basket_id[$i];
  $product=$ses_basket_name[$i];
  $unit=sprintf("%01.2f",($price/$quantity));
  $sql .= " ('$thisID', '$product', '$itemID', '$quantity', '$code', '$unit'),"
}
$sql = substr($sql,0,-1); //this removes last comma
$result=mysql_query($sql) or die (mysql_error().": $sql");

[phpdragon]

nice job thanks Mchl 

 

For everyone else reading the end of the $sql build line inside the loop needs a ;

 

replace

$sql .= " ('$thisID', '$product', '$itemID', '$quantity', '$code', '$unit'),"

with

$sql .= " ('$thisID', '$product', '$itemID', '$quantity', '$code', '$unit'),";

 

to prevent unexpected close error, but aside from the typo it works great.