How do a multiple row insertion

Question

0.00/5 (No votes)

See more:

i have a piece of code i wrote in mysqli to insert multiple rows of data inside the database.the code actually is working.i'm tring to write the same code in PDO but i'm returned an error

<pre lang="PHP"> Catchable fatal error: Object of class PDOStatement could not be converted to string in C:\wamp64\www\test\Nouveau dossier\quiz\index.php on line 148

i do not really understand where is the problem.Please for help.
this is the piece of code in mysqli

PHP

 if (isset($_POST['register']) && !empty($_POST['answer1']) && !empty($_POST['answer2']) 
 && !empty($_POST['answer3']) && !empty($_POST['answer4'])) {

     $answer1 = $_POST['answer1'];
     $answer2 = $_POST['answer2'];
     $answer3 = $_POST['answer3'];
     $answer4 = $_POST['answer4'];

     $servername = "localhost";
     $username = "root";    
 $password = "";
     $dbname = "test";
    
     $con = new mysqli($servername, $username, $password, $dbname);
     if($con->connect_error){
         die("Connection to db failed:" .$con->connect_error);
     }
    
     //Create an array and fill it with values coming from the form
     $answers_array = array(
         array('AnswerText' => $answer1, 'AnswerStatus' => 0, 'QuestionId' => 1),
        array('AnswerText' => $answer2, 'AnswerStatus' => 0, 'QuestionId' => 1),
         array('AnswerText' => $answer3, 'AnswerStatus' => 1, 'QuestionId' => 1),
         array('AnswerText' => $answer4, 'AnswerStatus' => 0, 'QuestionId' => 1)     );
       $sql = "INSERT INTO answers (AnswerText, AnswerStatus, QuestionId) VALUES ";
    
    $total = count($answers_array);
    
     $iterator = new ArrayIterator($answers_array);
    
//     //Iterate over the values in the object ArrayObject
     while($iterator->valid()){
        $currentItem = $iterator->current();
         $sql .="('".$currentItem['AnswerText']."','".$currentItem['AnswerStatus']."','".$currentItem['QuestionId']."')";
        

//$sql.='('.$currentItem['AnswerText'].','.$currentItem['AnswerStatus'].','.$currentItem['QuestionId'].')';/         $iterator->next();
         $sql .= $iterator->key()? ',' : ';';
     }
    
    //Now we insert the data into the database
   /* $con = mysql_connect($servername, $username, $password);
    mysql_select_db($dbname, $con);
    mysql_query($sql, $con);*/
    

     if ($con->multi_query($sql) === TRUE) {
        echo "New records created successfully";
     } else 
     {
         echo "Error: " . $sql . "<br>" . $con->error;
     }
    
     $con->close();
 }else
 echo "Please, fill the form fields !";

thanks in advance

What I have tried:

this is the same code in PDO

PHP

if (isset($_POST['register']) && !empty($_POST['answer1']) && !empty($_POST['answer2']) 
&& !empty($_POST['answer3']) && !empty($_POST['answer4'])) {

    $answer1 = $_POST['answer1'];
    $answer2 = $_POST['answer2'];
    $answer3 = $_POST['answer3'];
    $answer4 = $_POST['answer4'];

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test";
    $con = new PDO('mysql:$servername;$dbname', $username, $password);
$answers_array = array(
    array('AnswerText' => $answer1, 'AnswerStatus' => 0, 'QuestionId' => 1),
    array('AnswerText' => $answer2, 'AnswerStatus' => 0, 'QuestionId' => 1),
    array('AnswerText' => $answer3, 'AnswerStatus' => 1, 'QuestionId' => 1),
    array('AnswerText' => $answer4, 'AnswerStatus' => 0, 'QuestionId' => 1)
);

$sql=$con->prepare("INSERT INTO answers (AnswerText, AnswerStatus, QuestionId) VALUES ");

$total = count($answers_array);

$iterator = new ArrayIterator($answers_array);

//Iterate over the values in the object ArrayObject
 
while($iterator->valid()){
    
    $currentItem = $iterator->current();
 $sql .="('".$currentItem['AnswerText']."','".$currentItem['AnswerStatus']."','".$currentItem['QuestionId']."')";// line 148
    
//$sql.='('.$currentItem['AnswerText'].','.$currentItem['AnswerStatus'].','.$currentItem['QuestionId'].')';
    $iterator->next();
    $sql .= $iterator->key()? ',' : ';';
    };
    
    

//Now we insert the data into the database
/* $con = mysql_connect($servername, $username, $password);
mysql_select_db($dbname, $con);
mysql_query($sql, $con);*/


if ($sql === TRUE)  {
    echo "New records created successfully";
} else 
{
    echo "Error: " . $sql . "<br>" . $con->error;
}

$con->close();
}else
echo "Please, fill the form fields !";

below is the form from which i get my values

HTML

 <form action="index.php" method="POST">
    <legend>This form helps type in answers proposition to question</legend>
    <br>
    <label for="Answer1">Answer 1 :</label>
    <br>
    <textarea name="answer1" id="answer1" cols="90" rows="5">Bonjour le monde</textarea>

    <br>
    <label for="Answer2">Answer 2 :</label>
    <br>
    <textarea name="answer2" id="answer2" cols="90" rows="5">Hello world</textarea>
<br>

    <label for="Answer3">Answer 3 :</label>
    <br>
    <textarea name="answer3" id="answer3" cols="90" rows="5">Mè tsa téh</textarea>
<br>

    <label for="Answer4">Answer 4:</label>
    <br>
    <textarea name="answer4" id="answer4" cols="90" rows="5">Guten tag</textarea>
    <br>

    <input type="submit" name="register" value="Save">
    </form>
</body>

Posted 25-Jan-20 5:01am

winsderlich@yahoo.fr

Updated 26-Jan-20 22:20pm

Add a Solution

Comments

Richard MacCutchan 25-Jan-20 11:14am

"I do not really understand where is the problem"
According to the error message it is on line 148. But since we have no idea which line that is, it is difficult to suggest anything.

Patrice T 25-Jan-20 11:19am

Which line is 148 ?

winsderlich@yahoo.fr 27-Jan-20 2:34am

$sql .="('".$currentItem['AnswerText']."','".$currentItem['AnswerStatus']."','".$currentItem['QuestionId']."')";// line 148

Patrice T 27-Jan-20 3:33am

Use Improve question to update your question.
So that everyone can pay attention to this information.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2020-01-25T06:02:00

Solution 1

It seems that you prepare the statement using only part of the insert query. Later on you try to concatenate more SQL text to the prepared statement thus the error message.

To fix the problem first build the whole statement and then use it for prepare call.

Related to that and more importantly, always use parameters. This keeps you safe from SQL injections and also makes it possible for you to prepare a single statement and then just assign the values for the bind parameters before execution.

For an example, see PHP: PDOStatement::bindParam - Manual[^]

Posted 25-Jan-20 6:02am

Wendelius

Comments

winsderlich@yahoo.fr 27-Jan-20 2:43am

i'm trying t understand what you said and this is what i did

$sql="INSERT INTO answers (AnswerText, AnswerStatus, QuestionId) VALUES ";

$total = count($answers_array);

$iterator = new ArrayIterator($answers_array);

//Iterate over the values in the object ArrayObject

while($iterator->valid()){

$currentItem = $iterator->current();

$sql .="('".$currentItem['AnswerText']."','".$currentItem['AnswerStatus']."','".$currentItem['QuestionId']."')";

$iterator->next();
$sql .= $iterator->key()? ',' : ';';
$sql=$con->prepare($sql);

$total=$sql->fetchColumn();
};

Wendelius 27-Jan-20 22:39pm

What I mean is that you first prepare the statement with binds. Then later on you use the prepared statement and between executions you change the values of the parameters.

Consider the following

$sql = "INSERT INTO answers (AnswerText, AnswerStatus, QuestionId) VALUES (:AnswerText, :AnswerStatus, :QuestionId)"
$stmt=$con->prepare($sql)
$stmt->bindParam(':AnswerText', $answer, PDO::PARAM_STR, 100);
$stmt->bindParam(':AnswerStatus', $status, PDO::PARAM_INT);
$stmt->bindParam(':QuestionId', $id, PDO::PARAM_INT);
...
while loop...
   $answer=...
   $status=...
   $id=...
   $stmt->execute();
...

winsderlich@yahoo.fr 30-Jan-20 16:36pm

thanks for youe solution it helped me

Patrice T · Accepted Answer · 2020-01-26T22:21:00

PHP

$sql .="('".$currentItem['AnswerText']."','".$currentItem['AnswerStatus']."','".$currentItem['QuestionId']."')";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]