Insert Several Rows Into Table

[son]

I finally manage to get data from array inserted into database just to find that it only inserts one row even if more than one option is selected from drop-down (is called $category). It is always the last option. My insert query is:

			foreach ($category as $v)      {		$productAssocQ = "INSERT INTO productCat (category_id, product_id) VALUES ('$v', '$id')";      }

How would I write a query that actually inserts a row into db for each selected option from $category select?Son

[justsomeguy]

Are you actually running the query or just building a string?

[son]

Are you actually running the query or just building a string?

Yes I run it and it inserts the last one ok into database, just not all. The code after the quoted bit is:

if ($productAssocRes = mysqli_query ($dbc, $productAssocQ))					{				echo "<p>The product has been added!<br />";				echo "<span class=\"small\"><a href=\"products.php\"><< Go back</a></span></p>";				include('inc/footer.php');				exit;				}

Son

[justsomeguy]

You have the foreach loop that creates the query, are you running the query inside of that loop or after the loop? If you're not running the query until after the loop, it's only going to have the last query you created, not all of them.

[son]

You have the foreach loop that creates the query, are you running the query inside of that loop or after the loop? If you're not running the query until after the loop, it's only going to have the last query you created, not all of them.

It is inside the loop, but does only insert the last category. The code is:

foreach ($category as $v)      		{			$productAssocQ = "INSERT INTO productCat (category_id, product_id) VALUES ('$v', '$id')";				if ($productAssocRes = mysqli_query ($dbc, $productAssocQ))					{				echo "<p>The product has been added!<br />";				echo "<span class=\"small\"><a href=\"products.php\"><< Go back</a></span></p>";				include('inc/footer.php');				exit;				}      		}

Any ideas?Son

[justsomeguy]

That should work. Use print_r on $category to make sure it contains what you think it does.

[djp1988]

if(count($category) > 0){ $sql = "INSERT INTO productCat(category_id, product_id) VALUES "; foreach ($category as $v){	$sql .= "('$v', '$id'),"; } $sql = substr($sql,-1);}

If your array is has a value, it'll concatenate the values to insert plus a comma, at the end of the loop the comma is dropped, and your $sql variable is an insert with all the info you need in it, this way you execute only one query and not a query per value in the loop, my way is faster as there is a time for each execution

[son]

if(count($category) > 0){ $sql = "INSERT INTO productCat(category_id, product_id) VALUES "; foreach ($category as $v){	$sql .= "('$v', '$id'),"; } $sql = substr($sql,-1);}

If your array is has a value, it'll concatenate the values to insert plus a comma, at the end of the loop the comma is dropped, and your $sql variable is an insert with all the info you need in it, this way you execute only one query and not a query per value in the loop, my way is faster as there is a time for each execution

Did print_r ($_POST['category']);which correctly showed Array ( [0] => 1 [1] => 2 ) Still only one row being inserted.Trying the short version throws the error 'File cannot be moved'?Son

[justsomeguy]

Did print_r ($_POST['category']);which correctly showed Array ( [0] => 1 [1] => 2 )

You're not looping over $_POST['category'], you're looping over $category. Are those the same? Did you just set $category to $_POST['category'] and not alter it?If there was more than one element in the array, it's going to loop twice. It's going to loop for each element in the array, it's not going to loop once if there are 2 elements.

if(count($category) > 0){echo "<pre>categories:\n" . print_r($category, true) . '</pre>';$sql = "INSERT INTO productCat(category_id, product_id) VALUES ";foreach ($category as $v){	$sql .= "('$v', '$id'),";}$sql = substr($sql,-1);echo $sql;}

[son]

It is now with this code not working at all. The printed query (2nd bit) does only show the comma. And even stranger. When I echo $v and $id inside the loop as:

foreach ($category as $v)		{    	$sql .= "('$v', '$id'),";		echo "<br />";	echo $id;	echo "<br />";	echo $v;		echo "<br />";		}

it shows fine as:211212Why is the query not working/showing up? I really do not understand...Son

[justsomeguy]

Replace this:$sql = substr($sql,-1);with this:$sql = substr($sql, 0, -1);

[son]

Replace this:$sql = substr($sql,-1);with this:$sql = substr($sql, 0, -1);

If I had a bit more of coding experience I would say: Great thinkers alike! Just before I read your reply went to php.net website to check right syntax of substr() and then changed it with the result that it is working now. Many thanks for your great help! I certainly learned quite a bit new stuff and would not have figured the last bit (also) out myself:-)Son