How can i add array elements in diferent columns in my sql database using php

Question

My array is something like this

Array
(
    [0] => Array
        (
            [questionNumber] => 1
            [question] => Which paper are your preparing for?
            [option] => IELTS General
        )

    [1] => Array
        (
            [questionNumber] => 2
            [question] => How much time do you have at hand to complete your preparation?
            [option] => 1 to 2 months
        )

    [2] => Array
        (
            [questionNumber] => 3
            [question] => Have you taken the IELTS exam earlier?
            [option] => Yes
        )

    [3] => Array
        (
            [questionNumber] => 4
            [question] => How many attempts did you take?
            [option] => 2
        )

    [4] => Array
        (
            [questionNumber] => 5
            [question] => What were your overall band scores
            [option] => 5 to 6 bands
        )

    [5] => Array
        (
            [questionNumber] => 6
            [question] => Which module do you need coaching for?
            [option] => Specific Modules only
        )

)

My SQL table looks something like this

id | email |    q1 |    a1 |    q2 |    a2 |    q3 |    a3 | q4 |   a4 |    q5 |    a5 |    q6 |    a6

I want to add question number 1 question element to q1 column and its option to a1 column. similarly add question number 2 question element to q2 and its option to a2 column and repeat this process till last question.

My php code is given below

<?php


$servername = 'localhost';
$dbname = 'chartjs';
$username = 'root';
$password = '';

//----------------------------------------------------------Connection Part---------------------------------------------------
$conn = mysqli_connect($servername,  $username, $password, $dbname);
if(!$conn){
    die("Connection Failed". mysqli_connect_error()); 
}



if(isset($_POST["response"])) {

    $responsearray=json_decode($_POST['response'], true);
    print_r($responsearray);
    
    foreach($responsearray as $response){

        $questionNumber =($response['questionNumber']);
        $question =($response['question']);
        $option =($response['option']);
      

     
        $query="INSERT INTO `ielts_quiz_db`(`q1`, `a1`) VALUES (' $question','$option')  ";
    
        
        $result=mysqli_query($conn,$query);
    }
}
?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 17 '21 at 12:46

Shailesh Jadav · Accepted Answer · 2021-09-17T13:12:27.927

0

instead of foreach loop you can use for loop like below

for($i=0;$i<=5;$i++){
 $questionNumber =$responsearray[$i]['questionNumber'];
 $question =$responsearray[$i]['question'];
 $option =$responsearray[$i]['option'];
 
 $num = $i+1;
 
 $query="INSERT INTO `ielts_quiz_db`(q$num, a$num) VALUES (' $question','$option')  ";
}

edited Sep 17 '21 at 13:12

answered Sep 17 '21 at 12:46

Shailesh Jadav

64
5

its not working error - Uncaught Error: Undefined constant "i" in C:\xampp\htdocs\form_design\ielts_database.php:24 Stack trace: #0 {main} thrown in C:\xampp\htdocs\form_design\ielts_database.php on line 24
– Hrithik Sep 17 '21 at 12:53
I have typo in code and updated codes. can you try now? – Shailesh Jadav Sep 17 '21 at 12:58
did you updated the code?? – Hrithik Sep 17 '21 at 13:02
yes, is it worked? – Shailesh Jadav Sep 17 '21 at 13:02
let me check... – Hrithik Sep 17 '21 at 13:03
Warning: Undefined variable $response in C:\xampp\htdocs\form_design\ielts_database.php on line 24

Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\form_design\ielts_database.php on line 24

Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\form_design\ielts_database.php on line 24
– Hrithik Sep 17 '21 at 13:08
Row is creating in sql database but values are not inserting, it is generating a blank row – Hrithik Sep 17 '21 at 13:09
I have updated code, Can you try now? – Shailesh Jadav Sep 17 '21 at 13:13
yep..its taking the input but only last question and its option is entering in the database table.....other fields like q1 a1 q1 a2 are remaining blank. – Hrithik Sep 17 '21 at 13:20
may be for q1, a1 are blank in array. try to debug in for loop. Accept my answer if you got answer. Thanks. – Shailesh Jadav Sep 17 '21 at 13:25

How can i add array elements in diferent columns in my sql database using php

1 Answers1