I have a small problem I'm thinking..

I have a form that lists items and next to each item there is a input text field for the user to choose the quantity of each item they wish to order..

When the form is run through it spits out this array:

( [8] => 3 [9] => 4 [10] => 5 [11] => 6 [12] => 7 )

I am trying to get 8,9,10,11,12 to be $item_id and 3,4,5,6,7 to be $item_qty..

Then I would like to insert that data, each being in their own row, into a database while the $item_id's match the $item_qty's..

If I were you, I would deal with this by using a foreach statement (and I'm presuming you're using mysql here):

<?php
$array = ( [8] => 3 [9] => 4 [10] => 5 [11] => 6 [12] => 7 );
foreach ($array as $item_id=>$item_qty)
{
// connect to mysql database
mysql_query("INSERT INTO itemtable (id, qty) VALUES ('$item_id', '$item_qty')");
}
?>

Alternatively, if you don't want to have to insert $item_id into the database every time, you could just put your $item_id column at AUTO_INCREMENT, and then match that up to your $item_qty.

Edited 3 Years Ago by happygeek: fixed formatting

Hey thanks for resoponding Demiloy!!

I still am having a problem implementing this for some reason.. I have put in this code on the page and this is what I get:

Array ( [8] => 12 [9] => 21 [10] => 41 [11] => 434 [12] => 100 ) Duplicate entry '0-8' for key 1

Here is the code I used:

$array = $_POST['item_qty'];
		print_r($array);
		
		foreach ($array as $item_id => $item_qty) {
			$query = "INSERT INTO dw_order_items (item_id, item_qty) VALUES ($item_id, $item_qty) ";
			$result = mysql_query($query) or die(mysql_error());
			echo $result."<br />";
			echo $item_id." - ";
			echo $item_qty;
		}

I am now getting a Duplicate entry error..

What am I doing incorrectly?

I am presuming you have logged onto the mysql database before you executed the mysql_query? That would be essential before you start doing queries. Also, what exactly is $_POST? Finally, what does your ouput look like?

Yes this page is connected to the database because the form is constructed from data in the database..

$_POST is the array

it outputs this:

Array ( [8] => 20 [9] => 40 [10] => 41 [11] => 36 [12] => 100 )

I know your code is correct because it is now saying another error..

Duplicate entry '0-8' for key 1

Here is code used:

$array = $_POST['item_qty'];
		print_r($array);
		echo "<br /><br />";
		
		foreach ($array as $item_id => $item_qty) {
			$query = "INSERT INTO dw_order_items (item_id, item_qty) VALUES ($item_id, $item_qty) ";
			$result = mysql_query($query) or die(mysql_error());
			echo $result."<br />";
			echo $item_id." - ";
			echo $item_qty;
		}

I'm just trying to echo the query before I actually send it to the database.. Just for error checking..

Thanks for you patience.. I know that helping out people like me takes a lot of it.. haha

Not really...

Anyway, "Duplicate entry '0-8' for key 1" tells me you may have a problem with your mysql table. How exactly is that configured?

Here is the table

CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY  (`order_id`, `item_id`)
)

This is my first SQL project and I have searched around for the right syntax and this is just what I came up with.. order_id cannot be auto-incremented because I am pulling that data out of another table.. item_id also comes out of another table..

Looking around, I think it may be advisable to put item_id as AUTO_INCREMENT. That seems to have fixed the problem before with other people.

And just curious, why are you starting at 8 for your item_id?

Here, maybe this will help a bit more.. These are the three tables that correspond to this query..

CREATE TABLE `dw_orders` (
  `order_id` INTEGER unsigned NOT NULL auto_increment,
  `customer_id` INTEGER unsigned NOT NULL,
  `order_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`order_id`)
)


CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY  (`order_id`, `item_id`)
)


CREATE TABLE `dw_items` (
  `item_id` INTEGER unsigned NOT NULL auto_increment,
  `item_category` varchar(50) NOT NULL default '',
  `item_description` varchar(255) NOT NULL default '',
  `item_price` float default NULL,
  PRIMARY KEY  (`item_id`)
)
INSERT INTO `dw_items` VALUES (1, 'wedding_packages', 'Wedding Package Slice of Paradise', 845);
INSERT INTO `dw_items` VALUES (2, 'wedding_packages', 'Wedding Package Barefoot Bliss', 1245);
INSERT INTO `dw_items` VALUES (3, 'wedding_packages', 'Wedding Package Island Sensation', 1945);
INSERT INTO `dw_items` VALUES (4, 'wedding_packages', 'Wedding Package Tropical Temptation', 2995);
INSERT INTO `dw_items` VALUES (5, 'wedding_packages', 'Wedding Package (one to three miles north)', 50);
INSERT INTO `dw_items` VALUES (6, 'wedding_packages', 'Wedding Package (three to seven miles north)', 95);
INSERT INTO `dw_items` VALUES (7, 'wedding_packages', 'Transportation Minister (one-seven miles north)', 14);

INSERT INTO `dw_items` VALUES (8, 'wedding_desserts', 'Cheesecakes 9 inch Round (see list)', 45);
INSERT INTO `dw_items` VALUES (9, 'wedding_desserts', 'Fresh Key Lime Pie', 40);
INSERT INTO `dw_items` VALUES (10, 'wedding_desserts', 'Fresh Coconut Pie', 40);
INSERT INTO `dw_items` VALUES (11, 'wedding_desserts', 'Wedding Cupcakes (per dozen)', 42);
INSERT INTO `dw_items` VALUES (12, 'wedding_desserts', 'Coconut Chocolate Brownies (serves 20)', 55);

The form is constructed out of the database so 8 is just the starting id of that section..

Maybe it's just my sql tables that are screwing things up..

I would suggest you put item_id as AUTO_INCREMENT, if you haven't done that already. Beyond that, I don't know if I can help you further, as MySQL is not so much my specialty.

Hey Thanks a bunch.. I think I got it resolved for now, until I find another problem with it..

I just changed my sql table from this:

CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY  (`order_id`, `item_id`)
)

to this:

CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL
)

And it worked out.. Seems that the primary keys were screwing it up when this table did not need one.. I guess this table was more of a storage area for the id's and quantity..

Couldn't have done it without your help.. THANK YOU!!!

Not at all...not to mention you got it done by yourself, anyway. Good luck going on...

Hey Thanks a bunch.. I think I got it resolved for now, until I find another problem with it..

I just changed my sql table from this:

CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY  (`order_id`, `item_id`)
)

to this:

CREATE TABLE `dw_order_items` (
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL
)

And it worked out.. Seems that the primary keys were screwing it up when this table did not need one.. I guess this table was more of a storage area for the id's and quantity..

Couldn't have done it without your help.. THANK YOU!!!

The obvious reason why you got that error was, you have order_id (or is it item_id) as primary key. Primary keys have to be unique and it shouldn't be null. I believe, you already had an entry for 8. Take Demiloy's suggestion. You don't have to make item_id/order_id an auto increment field, but, you can have an integer field (id or counter) in your mysql table and make it auto_increment and primary key. (I believe, if you make a key primary, its also becomes the index). If it isn't marked as 'index', you can do that.
I isn't a good thing for a table not to have indexes (or primary key). Since, you are calling this a 'storage area', you need index if you want to speed up your query execution.

Cheers,
Naveen

Hey thanks for the input Naveen!

I have changed my table to this:

CREATE TABLE `dw_order_items` (
  `order_item_id` INTEGER unsigned NOT NULL auto_increment,
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL DEFAULT 1
)

How does that look?? I wasn't aware that not having an index would slow down my query executions.. Excellent!!

CREATE TABLE `dw_order_items` (
  `order_item_id` INTEGER unsigned NOT NULL auto_increment  PRIMARY KEY,
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER UNSIGNED NOT NULL DEFAULT 1
)

This will also create an index on the primary key, order_item_id. :)

Yes indeed.. My mistake.. I forgot the PRIMARY KEY syntax.. Thanks again..

Since I got you here, can you tell me if you think that this is the best way to implement this code below..

foreach ($array_item_qty as $item_id => $item_qty) {
		if ($item_qty >= 1) {
			$query_order_items  = "INSERT INTO dw_order_items (order_id, item_id, item_qty) "; 
			$query_order_items .= "VALUES ($order_id, '$item_id', '$item_qty') ";
			mysql_query($query_order_items) or die(mysql_error());
		}
	}

I'm referring to just the if statement.. It is just to make sure that the user chooses a quantity of at least one of any item and stores that to the database, rather than all items available..

This article has been dead for over six months. Start a new discussion instead.