php mysql insert query is populating every column with the same values

Question

I am developing a class for a site that has a lot of mysql querys. The idea of the class is to make it easier ans more unified. The class works but on the insert query it fills every column with the last data entry of the array.

Here is my function:

public function insert($table, $values){

         $sql = "INSERT INTO ".$table." (";
         $x = 0;
         $vals = "";
         foreach($values as $col => $val){
             $sql .= ($x < (count($values) - 1)) ? $col.", " : $col." ";
             $vals .= ($x < (count($values) - 1)) ? "?, " : "? ";
             //$vals .= ($x < (count($values) - 1)) ? $val.", " : $val." ";
             $x++;
         }
         $sql .= ") VALUES (".$vals.")";
         $stmt = $this->db_connection->prepare($sql);
         $x = 0;
         foreach($values as $col => $val){
             //echo 'bindParam('.($x + 1).', '.$val.', PDO::PARAM_INT);';
             $stmt->bindParam(($x + 1), $val, PDO::PARAM_INT);
             $x++;
         }
         //print_r($stmt);
         $stmt->execute();
         print_r($stmt->errorInfo());    
     }

I have echoed out the sql which shows no errors:

INSERT INTO accounts (first_name, last_name, email, address, post, country, password ) VALUES (?, ?, ?, ?, ?, ?, ? )

And also the params, which again no error:

bindParam(1, john, PDO::PARAM_INT);
bindParam(2, smith, PDO::PARAM_INT);
bindParam(3, [email protected], PDO::PARAM_INT);
and so on...

This is the outcome in the database: Database Error

I cant seem to find where I have gone wrong with this but obviously somewhere i have. I greatly appreciate the help to fix this.

And this is how the function is called:

$this->insert('accounts', array(
                    'first_name' => $f_name,
                    'last_name' => $l_name,
                    'email' => $email,
                    'address' => $address,
                    'post' => $zip,
                    'country' => $country,
                    'password' => $pass
            ));

Called for a different table:

$this->insert('newsletter', array( 'name' => $f_name.' '.$l_name, 'email' => $email )

But this one works!! //////// EDIT SOLVED ///////////////// The problem was the ->bindParam Loop, I switched it to this

public function insert($table, $values){

         $sql = "INSERT INTO ".$table." (";
         $x = 0;
         $vals = "";
         $querys = array();
         foreach($values as $col => $val){
             $sql .= ($x < (count($values) - 1)) ? $col.", " : $col." ";
             $vals .= ($x < (count($values) - 1)) ? "?, " : "? ";
             array_push($querys, $val);
             $x++;
         }
         $sql .= ") VALUES (".$vals.")";
         $stmt = $this->db_connection->prepare($sql);
         for($x = 0; $x < count($querys); $x++){
             $stmt->bindParam(($x + 1), $querys[$x], PDO::PARAM_INT);
         }
         //print_r($stmt);
         $stmt->execute();
         print_r($stmt->errorInfo());    
     }

And it was works fine, not sure why but the foreach loop was causing the error

And when you dump for example $f_name right before the insert query, does it also give you the correct result? — Matheno
– Matheno, Commented Feb 2, 2015 at 10:48
MySQL’s utf8 using MySQL’s utf8 charset for databases, tables, and columns, assuming it mapped to the UTF-8 encoding . By using utf8, You’d be able to store any symbol you want in your database — Pankaj katiyar
– Pankaj katiyar, Commented Feb 2, 2015 at 10:59

Phate01 · Accepted Answer · 2015-02-02 10:59:06Z

1

I tried this script for testing:

function insert($table, $values){

     $sql = "INSERT INTO ".$table." (";
     $x = 0;
     $vals = "";
     foreach($values as $col => $val){
         $sql .= ($x < (count($values) - 1)) ? $col.", " : $col." ";
         $vals .= ($x < (count($values) - 1)) ? $val.", " : $val." ";
         $x++;
     }
     $sql .= ") VALUES (".$vals.")";
     var_dump($sql);
 }
 insert('accounts', array(
                'first_name' => "f_name",
                'last_name' => "l_name",
                'email' => "email",
                'address' => "address",
                'post' => "zip",
                'country' => "country",
                'password' => "pass"
        ));

This just creates the query. I initially got your error too, and it was because the single variables $f_name, $l_namewere NULL. The script above works perfectly, so your problem is in the vars you use as parameters of the method.

answered Feb 2, 2015 at 10:59

Phate01

1,8573 gold badges34 silver badges58 bronze badges

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

1 Comment

Paul Ledger Over a year ago

Yeah it was the params causing the problem, Ive added the bit i changed to the question. Thanks

Collectives™ on Stack Overflow

php mysql insert query is populating every column with the same values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related