Insert Multiple rows help

[webweever]

The code below kind of works, I don't get any errors. It has 2 problems; it only inserts the second row of data I get nothing for the first row. It also does not insert the 4id correctly I just get 1 instead of the actually user id number.

 

I'd appreciate some help, been trying to hack this out for a couple hours.

 

 

<?php
// Begin the script for this page
$id = $_SESSION['id'];
if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form

} else {
	//Assign each array to a variable
	$id = $_SESSION['id'];
	$store = $_POST['store'];
	$item = $_POST['item'];
	$itemprice = $_POST['itemprice'];
	$itemnumber = $_POST['itemnumber'];
	$couponvalue = $_POST['couponvalue'];

	$limit = count($id);

	for($i=0;$i<$limit;$i++){
		$id[$i] = $id[$i];
		$store[$i] = check_input($store[$i]);
		$item[$i] = check_input($item[$i]);
		$itemprice[$i] = check_input($itemprice[$i]);
		$itemnumber[$i] = check_input($itemnumber[$i]);
		$couponvalue[$i] = check_input($couponvalue[$i]);
	}

	$query = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES ('".$id[$i]."','".$store[$i]."', '".$item[$i]."', '".$itemprice[$i]."', '".$itemnumber[$i]."', '".$couponvalue[$i]."')";

	if (!mysql_query($query,$link)){
		die('Error: ' . mysql_error());
	} else {
		echo "$row record added";
	}
}	

?>
<div class="pageContent">
    <div id="main">
	<div class="container">
		<form action="" method="post">
			<table>
				<tr>
					<input type="text" name="id[]" id="id[]" value="<?php echo $_SESSION['id'];?>" />
					<td><DIV CLASS="p2"><center><b>Store</b></center></div>
					<input type="text" name="store[]" id="store[]" size="15" maxlength="255"/></td>
					<td><DIV CLASS="p2"><center><b>Item</b></center></div>
					<input type="text" name="item[]" id="item[]" size="15" maxlength="255"/></td>
					<td><DIV CLASS="p2"><center><b>Item Price</b></center></div>
					<input type="text" name="itemprice[]" id="itemprice[]" size="15" maxlength="255"/></td>
					<td><DIV CLASS="p2"><center><b>Item Number</b></center></div>
					<input type="text" name="itemnumber[]" id="itemnumber[]" size="15" maxlength="255"/></td>
					<td><DIV CLASS="p2"><center><b>Coupon Value</b></center></div>
					<input type="text" name="couponvalue[]" id="couponvalue[]" size="15" maxlength="255"/></td>
				</tr>
				<tr>
					<input type="text" name="id[]" id="id[]" value="<?php echo $_SESSION['id'];?>" />
					<td><input type="text" name="store[]" id="store[]" size="15" maxlength="255"/></td>
					<td><input type="text" name="item[]" id="item[]" size="15" maxlength="255"/></td>
					<td><input type="text" name="itemprice[]" id="itemprice[]" size="15" maxlength="255"/></td>
					<td><input type="text" name="itemnumber[]" id="itemnumber[]" size="15" maxlength="255"/></td>
					<td><input type="text" name="couponvalue[]" id="couponvalue[]" size="15" maxlength="255"/></td>
				</tr>
			</table>
		<input type="submit" name="submit" value="Submit Item">
		</form>
	</div>
</div>
</div>

[DavidAM]

1) Is $_SESSION['id'] REALLY an ARRAY? You assign it to $id, and insert it as the user_id, so I'm suspecting it is NOT an array, but a scalar value containing the current user's ID. But you are using count($id) to determine how many entries are in the POSTed array. I suspect you should be counting one of the actual arrays.

 

2) Your INSERT statement is OUTSIDE of your FOR loop, so it is only being executed AFTER the last pass of the loop, which means you are only INSERTing the last element of the array.

[webweever]

Fixed the id piece of it but I move the query inside the loop and it still only inserts the last row.

 

With this arent I counting the instances of the id[] from my form>

$limit = count($id);

 

 

<?php
// Begin the script for this page
if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form

} else {
	//Assign each array to a variable
	$id = $_POST['id'];
	$store = $_POST['store'];
	$item = $_POST['item'];
	$itemprice = $_POST['itemprice'];
	$itemnumber = $_POST['itemnumber'];
	$couponvalue = $_POST['couponvalue'];

	$limit = count($id);

	for($i=0;$i<$limit;$i++){
		$id[$i] = $id[$i];
		$store[$i] = check_input($store[$i]);
		$item[$i] = check_input($item[$i]);
		$itemprice[$i] = check_input($itemprice[$i]);
		$itemnumber[$i] = check_input($itemnumber[$i]);
		$couponvalue[$i] = check_input($couponvalue[$i]);

	$query = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES ('".$id[$i]."','".$store[$i]."', '".$item[$i]."', '".$itemprice[$i]."', '".$itemnumber[$i]."', '".$couponvalue[$i]."')";

	}

	if (!mysql_query($query,$link)){
		die('Error: ' . mysql_error());
	} else {
		echo "$row record added";
	}
}	

?>

[PaulRyan]

Try the following, it will insert the data all at once, instead of running multiple queries.

 

<?php

// Begin the script for this page
if (isset($_POST['submit'])) { // if page is not submitted to itself echo the form

} else {
	//Assign each array to a variable
	$id = $_POST['id'];
	$store = $_POST['store'];
	$item = $_POST['item'];
	$itemprice = $_POST['itemprice'];
	$itemnumber = $_POST['itemnumber'];
	$couponvalue = $_POST['couponvalue'];

	$limit = count($id);

	for($i=0;$i<$limit;$i++){
                  $insertValues[] = "('".$id[$i]."','".check_input($store[$i])."','".check_input($item[$i])."','".check_input($itemprice[$i])."','".check_input($itemnumber[$i])."','".check_input($couponvalue[$i])."')";
	}

                $query  = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES ";
                $query .= implode(', ',$insertValues).';';


	if (!mysql_query($query,$link)){
		die('Error: ' . mysql_error());
	} else {
		echo "$row record added";
	}
}	

?>

 

Try it out an tell me how it goes, please note this is untested any issues, just post back here.

 

Regards, PaulRyan.

[Pikachu2000]

If it isn't already there somewhere, you need to have session_start() placed before anything is output to the browser, in all scripts that utilize any $_SESSION data.

 

Your query is outside of your while loop, so it will only be executed with the values from the last iteration of it. That is actually a good thing, though as it's generally a bad idea to run queries in a loop. The query string should be built in the loop, then the query executed once, after the loop has terminated.

 

<?php
// Begin the script for this page
$id = $_SESSION['id'];
if (isset($_POST['submit'])) {
//Assign each array to a variable
$id = $_SESSION['id'];
$store = $_POST['store'];
$item = $_POST['item'];
$itemprice = $_POST['itemprice'];
$itemnumber = $_POST['itemnumber'];
$couponvalue = $_POST['couponvalue'];

$limit = count($id);
$values = array(); // initialize an empty array to hold the values
for($i=0;$i<$limit;$i++){

	$store[$i] = check_input($store[$i]);
	$item[$i] = check_input($item[$i]);
	$itemprice[$i] = check_input($itemprice[$i]);
	$itemnumber[$i] = check_input($itemnumber[$i]);
	$couponvalue[$i] = check_input($couponvalue[$i]);

	$values[$i] = "( '{$id[$i]}', '{$store[$i]}', '{$item[$i]}', '{$itemprice[$i]}', '{$itemnumber[$i]}', '{$couponvalue[$i]}')"; // build the array of values for the query string
}
$query = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES " . implode( ', ', $values ); // Form the query string and add the implod()ed values
if (!mysql_query($query,$link)){
	die('Error: ' . mysql_error());
} else {
	echo "$row record added";
}
}
?>

[DavidAM]

You moved the building of the SQL inside the loop, but you left the execution of the query (mysql_query()) outside the loop. So it is only executing the last query.

 

		for($i=0;$i<$limit;$i++){
		$id[$i] = $id[$i];
		$store[$i] = check_input($store[$i]);
		$item[$i] = check_input($item[$i]);
		$itemprice[$i] = check_input($itemprice[$i]);
		$itemnumber[$i] = check_input($itemnumber[$i]);
		$couponvalue[$i] = check_input($couponvalue[$i]);

	$query = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES ('".$id[$i]."','".$store[$i]."', '".$item[$i]."', '".$itemprice[$i]."', '".$itemnumber[$i]."', '".$couponvalue[$i]."')";

	}

	// THIS STATEMENT EXECUTES THE QUERY - IT NEEDS TO BE INSIDE THE LOOP, TOO
	if (!mysql_query($query,$link)){
		die('Error: ' . mysql_error());
	} else {
		echo "$row record added";
	}
}	

 

Or you can use Pikachu's or Paul's suggestion and build a single query to execute after the loop is done. (I don't belong to the "NEVER run a query in a loop" club - I believe there are times when it is appropriate. Since I don't know your application or the importance of this data, or what else your application is doing; I can't really advise other than to say, if you run the separate queries in the loop, you need to capture the errors and let the user know which one(s) (if any) failed.)

 

By the way, your echo statement says "$row record added", but I don't see $row defined anywhere. So, unless it is defined elsewhere, it will be blank.

 

[fugix]

you need to have session_start() before you can call any session vars

[webweever]

I have started the session, it's part of another piece of the page that I didnt include in the post.

[webweever]

Anyone have any ideas how I can fix this?

[wildteen88]

Have you tried PaulRyan or Pikachu2000's suggestions?

[webweever]

Somehow I glossed over those two replies, guess I should have read a little more carefully.

 

PaulRyan's changes worked beautifully although I'm not sure I fully understand why mine only inserted the last item. Need to do a little reading on loops.

 

Thanks again guys