For loop in mysql query

PHP
1

Hi,
How can i loop a mysql query ? I have a loop like below, But it’s value is inserting only once.


for ($x=0; $x<$ParticiDetails; $x++){

$sql="INSERT INTO table (column1,column2,column3,column4) VALUES ('$_POST[field1]','$_POST[field2]','$_POST[field3]','$_POST[field4]')";
}
2

see Control Structures examples :slight_smile:

3

You would have to execute the query inside the loop; what your loop does is overwrite the $sql variable over and over, and when you get to the end, you execute it - the last instance of the loop.

Also, never do that.

(Seriously.)

Instead of looping the query, make the query insert multiple records at the same time.

4

Something like this would work:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\
", mysqli_connect_error());
    exit();
}

for ($i = 0; $i < 10; $i++) {
  $query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
  $mysqli->query($query);
  printf ("New Record has id %d.\
", $mysqli->insert_id);
}

/* close connection */
$mysqli->close();
?>

Like StarLion said, you need to execute the query (it’s what the $mysqli->query($query) line does).

Your code is just looping like this :


For 10 times loop and
  set the variable $SQL to "insert INTO table (...) values (...)";
End of loop

Execute the $SQL query on database;

See, at the last line, it will execute only use the last value of the SQL variable and execute it on the database.

Again, like StarLion said, it’s way more efficient to use only one query to insert multiple rows with MySQL. From the MySQL doc:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

That way, you only “talk” to the database once, it’s really more efficient than looping and inserting row by row.

A warning:
Be careful when you “craft” a SQL query right from parameters that you didn’t validate. Your code, as I see it now, is probably vulnerable to SQL injections. I wrote about injections on my blog:
http://www.mogosselin.com/injection-explained-and-how-to-fix-them/

Here’s how to use prepared statements to prevent SQL injections:
http://mattbango.com/notebook/code/prepared-statements-in-php-and-mysqli/

Also, make sure that you’re using mysqli_* methods or PDO, NOT mysql_* methods. Read this article on sitepoint for more details: http://www.sitepoint.com/forums/showthread.php?1182768-STOP-using-mysql-migrating-to-procedural-mysqli

5

Thanks xMog for the help ,

I have the fields is based on user selected, so how can i write this query according to that ?

Some times it will be like INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);

or INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6); etc… It depends on user selection, So how can i write the query ?

6

Inserting the same (4 values) X number of times with one query is not hard to do. Here’s a sample. Remove test data of course.

<?php
/////////////////////////
// for testing. Remove //
$ParticiDetails = 3;
$_POST['field1'] = "field1";
$_POST['field2'] = "field2";
$_POST['field3'] = "field3";
$_POST['field4'] = "field4";
/////////////////////////

//Any validation should be done here
$field1 = trim($_POST['field1']);
$field2 = trim($_POST['field2']);
$field3 = trim($_POST['field3']);
$field4 = trim($_POST['field4']);

$values = array();
for ($x=0; $x<$ParticiDetails; $x++){
	$values[] = "('$field1','$field2','$field3','$field4')";
}

$sql = "INSERT INTO table (column1,column2,column3,column4) VALUES";
$sql .= implode(",",$values);
echo "$sql";
?>

The result when echoing $sql

INSERT INTO table (column1,column2,column3,column4) VALUES(‘field1’,‘field2’,‘field3’,‘field4’),(‘field1’,‘field2’,‘field3’,‘field4’),(‘field1’,‘field2’,‘field3’,‘field4’),(‘field1’,‘field2’,‘field3’,‘field4’),(‘field1’,‘field2’,‘field3’,‘field4’),(‘field1’,‘field2’,‘field3’,‘field4’)

7

If however you are looping through your form fields so each would be an array it would be more like this.

<?php
/////////
//If you are looping form fields building array for each name like
// <input type="text" name="field1[]" />
// the output would look like this
$_POST['field1'] = array("F1_Value1","F1_Value5","F1_Value9","F1_Value13");
$_POST['field2'] = array("F2_Value2","F2_Value6","F2_Value10","F2_Value14");
$_POST['field3'] = array("F3_Value3","F3_Value7","F3_Value11","F3_Value15");
$_POST['field4'] = array("F4_Value4","F4_Value8","F4_Value12","F4_Value16");


$values = array();
foreach($_POST['field1'] as $k => $v){
	$field1 = trim($_POST['field1'][$k]);
	$field2 = trim($_POST['field2'][$k]);
	$field3 = trim($_POST['field3'][$k]);
	$field4 = trim($_POST['field4'][$k]);
	$values[] = "('$field1','$field2','$field3','$field4')";
}

$sql = "INSERT INTO table (column1,column2,column3,column4) VALUES";
$sql .= implode(",",$values);
echo "$sql";
?>

Output

INSERT INTO table (column1,column2,column3,column4) VALUES(‘F1_Value1’,‘F2_Value2’,‘F3_Value3’,‘F4_Value4’),(‘F1_Value5’,‘F2_Value6’,‘F3_Value7’,‘F4_Value8’),(‘F1_Value9’,‘F2_Value10’,‘F3_Value11’,‘F4_Value12’),(‘F1_Value13’,‘F2_Value14’,‘F3_Value15’,‘F4_Value16’)