PDO UPDATE array using php mysql

Question

Hello I'm trying to make a code to UPDATE or EDIT the survey answers and comments per answer but when I execute the function submiting the form, it did not save any value into the database. What can I do to fix it?

I'm new in PDO.

Thanks in advance.

Database Structure

"Questions" (idquestion, question)

"Surveys" (idsurvey, idquestion, answers, comments_per_question, survey_number)

Update function

public function ModifySurveyMulti($answer = array())
{                           
    if(!empty($answer)) {
        foreach($answer as $questi => $value  ) {
            $this->MyDB->Write("UPDATE survey SET(
                      `idquestion` = '".$questi."',                             
                      `answers` = '".$value[0]."',
                      `comments_per_answer`= '".$_POST["comment"][$questi]."')");
        }
    }
}

modify_surveyform.php

<th><?php echo $row["questions"];?></th>
<td>
  <input type  = "text" 
         name  = "answer[<?php echo $row['idquestion'];?>][]"
         value = "<?php echo $row["answers"];?>">
  </input>
</td>
<td>
  <Textarea type = "text"
            name = "comment[<?php echo $row['idquestion'];?>]"
            cols = "50" rows = "3"/> <?php echo $row["comment"];?
  </textarea>
</td>
</tr><?php } ?>

Mydbconnect.php

<?php
// I'm adding my PDO database because yours is deprecated
class DBConnect
{
    public   $con;
    // Create a default database element
    public  function __construct($host = '',$db = '',$user = '',$pass = '')
    {
        try {
            $this->con = new PDO("mysql:host=$host;
                                  dbname=$db",$user,
                                  $pass, array(
                                          PDO::ATTR_ERRMODE 
                                               => PDO::ERRMODE_WARNING
                                         )
                                 );
        }
        catch (Exception $e) {
            return 0;
        }
     }

     // Simple fetch and return method
     public  function Fetch($_sql)
     {
         $query  =   $this->con->prepare($_sql);
         $query->execute();
             if($query->rowCount() > 0) {
                 while($array = $query->fetch(PDO::FETCH_ASSOC)) {
                     $rows[]   =   $array;
                 }
             }
         return (isset($rows) && $rows !== 0 && !empty($rows))? $rows: 0;
      }

      // Simple write to db method
      public  function Write($_sql)
      {
          $query = $this->con->prepare($_sql);
          $query->execute();
      }
}?>

One of the primary reasons to use PDO is to support parametrized queries, and yet you're still just concatenating values directly from a form. Have you studied the documentation at any point? php.net/manual/en/book.pdo.php — Tieson T.
– Tieson T., Commented Oct 24, 2014 at 23:26
As long as you do not show us the code of $this->MyDB->Write() we wont be able to help you... — CodeZombie
– CodeZombie, Commented Oct 24, 2014 at 23:26
I already added mydbconnect.php there are all the pdo functions I'm using at the momment. — dcft
– dcft, Commented Oct 25, 2014 at 16:07
@dcft Did you check the connection to your database? For me the PDO object takes an odd connection string mysql:host=$host;dbname=$db" (or is your host and database name really called $host and $db, they should be maybe replaced by an hard coded value or the supposed variables need to be escaped). I have also noticed that you closed the <textarea> to early (after the rows attribute is a closing tag) rows = "3"/>. — Sascha
– Sascha, Commented Oct 25, 2014 at 16:45
Hell @Sascha I create the connection inserting this code $con = new DBConnect('localhost','sistema_bss','root',''); into the file wich contains the UPDATE function. — dcft
– dcft, Commented Oct 27, 2014 at 14:41

meda · Accepted Answer · 2014-10-25 16:58:49Z

2

Few things you need to do:

First of all ditch this code, it is useless and expose you to sql injection
Use PDO directly with prepared statement
The query you need is :

UPDATE survey SET(`answers`= ?,`comments_per_answer`= ?) WHERE idquestion = ?

You will need to adjust your class to only create the connection

class DBConnect
{
    public   $con;

    public  function __construct($host = '',$db = '',$user = '',$pass = '')
    {
        try {
                $this->con  =  new PDO(
                               "mysql:host=$host;dbname=$db",
                               $user,$pass, 
                               array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING)
                );
        }
        catch (Exception $e) {
            die($e);
        }
    }

    public function get_connection(){
        return $this->con;
    }

}

So that you can create it like this:

$db = new DBConnect(/*pass arguments here*/);
$this->MyDB = $db->get_connection();

Modify and use it in your function:

public function ModifySurveyMulti($answer = array(), $comments)
{
    $sql = 'UPDATE survey SET(`answers`= ?,`comments_per_answer`= ?) 
            WHERE idquestion = ?';
    $stmt->prepare($sql);
    foreach($answer as $questi => $value ) {
        $stmt->execute(array($value, $comments[$questi],$questi));
        $count = $stmt->rowCount();
        echo $count > 0 ? $questi.' updated' : $questi.' did not update';
    }
}

Call the function :

if(isset($_POST['answer'], $_POST['comments'])){
    $answers =  $_POST['answer'];
    $comments =  $_POST['comments'];
    ModifySurveyMulti($answers, $comments);
}

edited Oct 25, 2014 at 16:58

answered Oct 25, 2014 at 16:53

meda

45.5k14 gold badges96 silver badges123 bronze badges

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

2 Comments

dcft Over a year ago

sorry @meda I had to post again because I did not understand your answer, I added your connection class but It sends me an error with some variables, I don't know why?

meda Over a year ago

when that happens, you supposed to come back and give me the error so I can adjust my answer

Collectives™ on Stack Overflow

PDO UPDATE array using php mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related