CODEWITHSUNDEEP
phpmysqlarrays
Naveen
Naveen

Reputation: 27

Writing to database using foreach

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 ?

Upvotes: 1

Views: 107

Answers (5)

hummingBird
hummingBird

Reputation: 2555

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.

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94682

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

}

Upvotes: 2

Dsda
Dsda

Reputation: 577

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

Upvotes: 0

Honza Musil
Honza Musil

Reputation: 326

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.

Upvotes: 0

Eduardo Galv&#225;n
Eduardo Galv&#225;n

Reputation: 962

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.

Upvotes: 0

Related Questions