Click here to Skip to main content
14,735,523 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
 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


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
 <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
Updated 26-Jan-20 23:20pm
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.

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[^]
   
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
$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[^]
   
Comments
winsderlich@yahoo.fr 27-Jan-20 6:34am
   
thanks trying to use parameters and them bind them
Patrice T 27-Jan-20 6:38am
   
Try to insert 1 record at the time, it will be easier with parameters.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900