Insert array values into database

[Scooby08]

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..

[Demiloy]

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.

[Scooby08]

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?

[Demiloy]

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?

[Scooby08]

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

[Demiloy]

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?

[Scooby08]

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..

[Demiloy]

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?

[Scooby08]

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..

[Demiloy]

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.

[Scooby08]

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!!!

[Demiloy]

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

[nav33n]

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

[Scooby08]

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!!

[nav33n]

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. :)

[Scooby08]

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..

[nav33n]

Looks good ! :)

[jerickduguran]

I have an idea for this..