for each loop PHP, insert into SQL not inserting data

Question

$student_info = array(
                'student_number'=>$_POST['student_number'],
                'student_first_name'=>$_POST['student_first_name'],
                'student_middle_name'=>$_POST['student_middle_name'],
                'student_last_name'=>$_POST['student_last_name']);

foreach($student_info as $table_row=>$information){
    $sql = "INSERT INTO student_info_db (`$table_row`) VALUES(`$information`)";

    echo $table_row . " " . $information;
}

im not pretty sure why it doesnt insert any data on the database. the echo $table_row $information are just to se if it gets the value and it succeed, but still doesnt insert any data. the question is, what was wrong ? im pretty sure im doing the correct sql .. or am i not ?

You are not running the query. Use mysqli_query($query) after your query string — Vikas Arora, Feb 22 '14 at 05:04
All you're doing here is putting the query into a variable.... — jraede, Feb 22 '14 at 05:11

Awlad Liton · Accepted Answer · 2014-02-23T16:53:47.833

2

It seems that your sql query string is not correct. you are running query for each element! it will insert data to each column for each time! you will have 4 entries for one student info in your table!

you also not ran query in the loop.

you should create query inside loop and then execute the query after the loop

You need to make query string first from your array.

First make your query like this:

try like this:

$student_info = array(
                'student_number'=>mysql_real_escape_string($_POST['student_number']),
                'student_first_name'=>mysql_real_escape_string($_POST['student_first_name']),
                'student_middle_name'=>mysql_real_escape_string($_POST['student_middle_name']),
                'student_last_name'=>mysql_real_escape_string($_POST['student_last_name']));

foreach($student_info as $table_row=>$information){
  $cols .= "`".$table_row."` ,";
  $vals .= "'".$information . "' ,";
  }
$cols = rtrim($cols,",");

$vals = rtrim($vals,",");

$sql = "INSERT INTO student_info_db (".$cols . ") VALUES(".$vals .")";

live Demo with sample data : https://eval.in/104428

then You need to run this $sql query

like this:

if(mysqli_query($con, $sql)
 echo "successfully inserted";
else 
 echo "something is wrong!";

edited Feb 23 '14 at 16:53

answered Feb 22 '14 at 05:14

Awlad Liton

9,366
2
27
53

@Bobski : did you try this? – Awlad Liton Feb 22 '14 at 05:19
gotta try this, sorry need to understand it more >_< im new in php, thanks. – Bobski Feb 22 '14 at 05:22
-1, embedding unescaped input from `$_POST` into an SQL query is [a bad idea](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Ilmari Karonen Feb 23 '14 at 16:37
@Ilmari Karonen: is that okay now? – Awlad Liton Feb 23 '14 at 16:54
Yeah, although I'd prefer to do the escaping inside the `foreach`, at the point where you're generating the SQL. If you always escape input at the last possible moment, and always assume that input is unescaped unless you've *just* escaped it, you'll never mistakenly think "oh, I already escaped that, no need to escape it again". It also makes it more likely that, if you ever copy the SQL-generating code, you'll copy the escaping code along with it. – Ilmari Karonen Feb 23 '14 at 16:57

Atanu Saha · Answer 2 · 2014-06-23T05:10:59.003

You did not execute your query! At first establish the connection with database. Then add mysql_query($sql) for executing the query.

$student_info = array(
            'student_number'=>mysql_real_escape_string(htmlspecialchars($_POST['student_number'])),
            'student_first_name'=>mysql_real_escape_string(htmlspecialchars($_POST['student_first_name'])),
            'student_middle_name'=>mysql_real_escape_string(htmlspecialchars($_POST['student_middle_name'])),
            'student_last_name'=>mysql_real_escape_string(htmlspecialchars($_POST['student_last_name'])));

//First we need to make a connection with the database
$host='localhost'; // Host Name.
$db_user= 'root'; //User Name
$db_password= 'nopass';
$db= 'product_record'; // Database Name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());

$column = "";
$value = ""; 
foreach($student_info as $table_row=>$information){
  if($column != ""){
    $column .= ",";
    $value .= ","; 
  }

  $column .= $table_row;
  $value .= "'".$information."'";

}

$sql = "INSERT INTO student_info_db (".$column.") VALUES(".$value.")";

mysql_query($sql);  
mysql_close($conn);

-1, embedding unescaped input from `$_POST` into an SQL query is [a bad idea](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Ilmari Karonen, Feb 23 '14 at 16:38
It looks OK now, although I'll [echo my earlier comment to Awlad](http://stackoverflow.com/questions/21949944/for-each-loop-php-insert-into-sql-not-inserting-data/21966893?noredirect=1#comment33292440_21950038) about escaping as late as possible. But I'll give you both an upvote anyway, since your answers seem to be essentially correct. — Ilmari Karonen, Jun 23 '14 at 13:09

Vikas Arora · Answer 3 · 2014-02-23T17:15:16.970

0

In your foreach loop, run the query. like this:

$student_info = array(
        'student_number'=>$student_number,
        'student_first_name'=>$student_first_name,
        'student_middle_name'=>$student_middle_name,
        'student_last_name'=>$student_last_name);

foreach($student_info as $table_row=>$information)
{
    $sql = "INSERT INTO student_info_db (`$table_row`) VALUES('".mysqli_real_escape_string($connection, $information)."')";
    mysqli_run($connection, $sql);
    echo $table_row . " " . $information;
}

More info on mysqli_query here

edited Feb 23 '14 at 17:15

answered Feb 22 '14 at 05:07

Vikas Arora

1,666
2
17
38

2

you are running query for each array element! – Awlad Liton Feb 22 '14 at 05:24
you should have understand since OP new comer in php. and this is not redesign its logic – Awlad Liton Feb 22 '14 at 05:28
-1, embedding unescaped input from `$_POST` into an SQL query is [a bad idea](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Ilmari Karonen Feb 23 '14 at 16:38
@IlmariKaronen I have now included that. – Vikas Arora Feb 23 '14 at 16:56
Thanks, downvote removed. Although, [as I wrote on Awlad Liton's answer](http://stackoverflow.com/questions/21949944/for-each-loop-php-insert-into-sql-not-inserting-data/21949982#comment33292440_21950038), I'd prefer to see the escaping done inside the `foreach`, at the point where the input is actually being turned into SQL. – Ilmari Karonen Feb 23 '14 at 17:00

score 0 · Answer 4 · edited May 23 '17 at 12:06

The proper way to do this is to use a prepared statement with placeholders:

$sql = <<<'END'
    INSERT INTO student_info_db (
        student_number,
        student_first_name,
        student_middle_name,
        student_last_name
    ) VALUES (?, ?, ?, ?)
END;

$stmt = $dbConnection->prepare( $sql )

$stmt->bind_param( 's', $_POST['student_number'] );
$stmt->bind_param( 's', $_POST['student_first_name'] );
$stmt->bind_param( 's', $_POST['student_middle_name'] );
$stmt->bind_param( 's', $_POST['student_last_name'] );

$stmt->execute();

or, if you insist on using an array as an intermediate stage:

$student_info = array(
    'student_number'      => $_POST['student_number'],
    'student_first_name'  => $_POST['student_first_name'],
    'student_middle_name' => $_POST['student_middle_name'],
    'student_last_name'   => $_POST['student_last_name']
);

$keys = array_keys( $student_info );
$columns = implode( ',', $keys );
$holders = implode( ',', array_fill( 0, count($keys), '?' ) );

$sql = "INSERT INTO student_info_db ($columns) VALUES ($holders)";
$stmt = $dbConnection->prepare( $sql )

foreach ( $keys as $key ) {
    $stmt->bind_param( 's', $student_info[$key] );
}
$stmt->execute();

for each loop PHP, insert into SQL not inserting data

4 Answers4