Writing to database using foreach

Question

I have Three arrays and i want to write them to database , The issue I face is whenever the values are written to the particular column the rest of the column is left empty. The

$name_array = array(3) { [0]"Name1" [1]=>"Name2" [2]=> "Name3" } 
$roll_array = array(3) { [0]=>"1" [1]=>"2" [2]=>"3" } 
$att_array =  array(3) { [0]=>"Present" [1]=>"Present" [2]=>"absent" }

I have three columns in DB "NAME" "ROLL" "ATTENDANCE" I want to store all the array data to the database at the same time. so it should look like this

NAME      ROLL     ATTENDANCE
Name1     1         present
Name2     2         present
Name3     3         absent

Here is the code i tried but it just add each values to the column and leaves the other column empty. So the first three rows has only ROLLNO and next three row has only NAME and last three rows has only ATTENDANCE.

$name_values = array();
$roll_values = array();
$att_values = array();


foreach ($name_array as $key => $name_values) {

    $name_values = mysqli_real_escape_string($connection,$name_values);
    $sql= "INSERT INTO `aclass12` (Name) VALUES ('$name_values')";
    mysqli_query($connection,$sql);

}

foreach ($roll_array as $key => $roll_values) {
    $roll_values = mysqli_real_escape_string($connection,$roll_values); 
    $sql= "INSERT INTO `aclass12` (RollNo) VALUES ('$roll_values')";
}

foreach ($att_array as $key => $att_values) {
    $att_values = mysqli_real_escape_string($connection,$att_values);   
    $sql= "INSERT INTO `aclass12` (attendance) VALUES ('$att_values')";
}

I know this is not the right way to do . and whats the way to do this ?

Rearrange your arrays so each foreach is one row of data (name, roll, attendance), then insert all three values in one query. — JimL
– JimL, Commented Apr 11, 2016 at 17:35

RiggsFolly · Accepted Answer · 2016-04-11 17:47:46Z

2

Simply use one array as the master, and the key of that array to access the other 2 arrays data.

Then insert all the data in a single INSERT

Its also a good idea to check that the INSERT actually worked, so I added a little bit of error checking

foreach ($name_array as $key => $value) {

    $name = mysqli_real_escape_string($connection,$value);
    $roll = mysqli_real_escape_string($connection,$roll_values[$key]); 
    $att  = mysqli_real_escape_string($connection,$att_array[$key]); 

    $sql = "INSERT INTO `aclass12` 
                   (Name, RollNo, attendance) 
            VALUES ('$value', '$roll', '$att')";

    $res = mysqli_query($connection,$sql);
    if ( $res === FALSE ) {
        echo mysqli_error();
        exit;
    }

}

edited Apr 11, 2016 at 17:47

answered Apr 11, 2016 at 17:38

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

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

Comments

Eduardo Galván · Accepted Answer · 2016-04-11 17:37:14Z

0

Use only one foreach and access the elements of the arrays there. Like this:

foreach ($name_array as $key => $name_values) {
    $name_values = mysqli_real_escape_string($connection,$name_values);
    $roll_values = mysqli_real_escape_string($connection,$roll_array[$key]);
    $att_values = mysqli_real_escape_string($connection,$att_array[$key]);
    $sql= "INSERT INTO `aclass12` (Name, RollNo, attendance) VALUES ('$name_values', '$roll_values', '$att_values')";
    mysqli_query($connection,$sql);
}

Also, it's recommended to use prepared statements, because they prevent SQL njection attacks. More information here.

answered Apr 11, 2016 at 17:37

Eduardo Galván

9877 silver badges17 bronze badges

Comments

Honza Musil · Accepted Answer · 2016-04-11 17:38:28Z

0

Try it this ways

for($i = 0; $i < count($name_array);$i++) {
    $name_values = mysqli_real_escape_string($connection,$name_array[$i]);
    $roll_values = mysqli_real_escape_string($connection,$roll_array[$i]);
    $att_values = mysqli_real_escape_string($connection,$att_array[$i]); 
    $sql= "INSERT INTO `aclass12` (Name, RollNo, attendance) VALUES ('$name_values', '$roll_values','$att_values')";
}

Other option is to use multidimensional array with foreach.

answered Apr 11, 2016 at 17:38

Honza Musil

3262 silver badges11 bronze badges

Comments

Dsda · Accepted Answer · 2016-04-11 17:38:52Z

0

foreach($name_array as $n_k=>$name) {
    $roll = (isset($roll_array[$n_k])) ? $roll_array[$n_k] : '';
    $att = (isset($att_array[$n_k])) ? $att_array[$n_k] : '';
    $name = mysqli_real_escape_string($connection,$name);
    $roll = mysqli_real_escape_string($connection,$roll);
    $att = mysqli_real_escape_string($connection,$att);
    $sql= "INSERT INTO `aclass12` (Name, RollNo, attendance) VALUES ('$name','$roll','$att')";
    mysqli_query($connection,$sql);
}

edited Apr 11, 2016 at 17:38

answered Apr 11, 2016 at 17:36

Dsda

5874 silver badges14 bronze badges

Comments

hummingBird · Accepted Answer · 2016-04-11 17:59:18Z

0

I do think it would be best to use since mysql query to inject it and simply concatenate everything before that. That's something like this:

$query = "INSERT INTO tbl_name (col1, col2, col3) VALUES ";

for ($i = 0; $i < count($name_array); $i++) {
    $name = mysqli_real_escape_string($conn, $name_array[$i]);
    $roll = mysqli_real_escape_string($conn, $roll_array[$i]);
    $att = mysqli_real_escape_string($conn, $att_array[$i]);
    $query .= "('{$name}', '{$roll}', '{$att}'),";       
}

$query = trim($query, ',');
$query = $query . ';';
mysqli_query($connection,$sql);

Add some damage control there (check for errors) and that's it.

answered Apr 11, 2016 at 17:59

hummingBird

2,5654 gold badges25 silver badges48 bronze badges

Collectives™ on Stack Overflow

Writing to database using foreach

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related