Jump to content

Insert Several Rows Into Table


son
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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;}

Link to comment
Share on other sites

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

Edited by son
Link to comment
Share on other sites

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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...