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
– Vikas Arora, Commented Feb 22, 2014 at 5:04
All you're doing here is putting the query into a variable.... — jraede
– jraede, Commented Feb 22, 2014 at 5:11

Awlad Liton · Accepted Answer · 2014-02-23 16:53:47Z

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, 2014 at 16:53

answered Feb 22, 2014 at 5:14

Awlad Liton

9,3513 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Awlad Liton Over a year ago

@Bobski : did you try this?

Bobski Over a year ago

gotta try this, sorry need to understand it more >_< im new in php, thanks.

Ilmari Karonen Over a year ago

-1, embedding unescaped input from $_POST into an SQL query is a bad idea.

Awlad Liton Over a year ago

@Ilmari Karonen: is that okay now?

Ilmari Karonen Over a year ago

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.

Atanu Saha · Accepted Answer · 2014-06-23 05:10:59Z

2

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

edited Jun 23, 2014 at 5:10

answered Feb 23, 2014 at 10:11

Atanu Saha

5684 silver badges14 bronze badges

3 Comments

Ilmari Karonen Over a year ago

-1, embedding unescaped input from $_POST into an SQL query is a bad idea.

Atanu Saha Over a year ago

I edit my answer. Could you please check this out? :)

Ilmari Karonen Over a year ago

It looks OK now, although I'll echo my earlier comment to Awlad about escaping as late as possible. But I'll give you both an upvote anyway, since your answers seem to be essentially correct.

Community · Accepted Answer · 2017-05-23 12:06:25Z

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();

Community · Accepted Answer · 2023-11-17 19:55:19Z

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 Nov 17, 2023 at 19:55

CommunityBot

11 silver badge

answered Feb 22, 2014 at 5:07

Vikas Arora

1,6602 gold badges17 silver badges39 bronze badges

5 Comments

Awlad Liton Over a year ago

you are running query for each array element!

Awlad Liton Over a year ago

you should have understand since OP new comer in php. and this is not redesign its logic

Ilmari Karonen Over a year ago

-1, embedding unescaped input from $_POST into an SQL query is a bad idea.

Vikas Arora Over a year ago

@IlmariKaronen I have now included that.

Ilmari Karonen Over a year ago

Thanks, downvote removed. Although, as I wrote on Awlad Liton's answer, I'd prefer to see the escaping done inside the foreach, at the point where the input is actually being turned into SQL.

Collectives™ on Stack Overflow

for each loop PHP, insert into SQL not inserting data

4 Answers 4

5 Comments

3 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

3 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related