Binding array of values in prepared statement PDO PHP

Question

I'm trying to bind my values into a prepared statement in PDO.

Here is the pre requisite codes that that uses the prepared statement block:

$tab = 'air_user';
$fie = array('USER_NAME', 'USER_PASSWORD' , 'USER_EMAIL');
$name = $_POST['name'];
$pass = $_POST['password'];
$email = $_POST['email'];
$val = array(
    'name' => $name,
    'pass' => $pass,
    'email' => $email
);
$this->connect($tab,$fie,$val);

And here is the part wherein I prepare those values and make the necessaru insertions:

public function connect($table,$fields,$values)
{

    try{
        $con = new PDO ('mysql:host=localhost;dbname=air','root','123456');
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $fields = implode(", ", $fields);
        echo $fields;
        $values = implode(", ", $values);
        echo $values;

        // have to make this prevent sql injection //
        $stmt = $con->prepare("INSERT INTO $table(ID,$fields) VALUES (?,?,?,?)");
        $stmt->execute(array('',$values));

    } catch(PDOException $e) {
        die("this cant connect the database");
    }
}

so why isit my INSERT not Working ? isit can anyone help me take a look of it , i tryed so many things , none of them work.

Kevin · Accepted Answer · 2015-05-04 03:23:15Z

9

No, don't implode the values that your going to pass inside the ->execute(), it must be an array:

$fields = implode(", ", $fields);
// $values = implode(", ", $values); // DONT IMPLODE!
$values = array_values($values);

$stmt = $con->prepare("INSERT INTO $table(ID,$fields) VALUES (NULL, ?,?,?)");
$stmt->execute($values);

Or @Augwa's suggestion:

// $fields = implode(", ", $fields); // not needed
// $values = implode(", ", $values); // DONT IMPLODE!

$placeholders = substr(str_repeat('?,', sizeOf($fields)), 0, -1);
// $placeholders = implode(', ', array_fill(0, count($values), '?'));

$stmt = $con->prepare(
    sprintf(
        "INSERT INTO %s (%s) VALUES (%s)", 
        $table, 
        implode(',', $fields), 
        $placeholders
    )
);
$stmt->execute($values);

edited May 4, 2015 at 3:23

answered May 4, 2015 at 2:39

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

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

4 Comments

Jonathan Over a year ago

while the above is correct, you could also just omit the ID all together.

Kevin Over a year ago

@Darren you could have posted also the same idea, just pressed the answer button earlier :D

user3233074 Over a year ago

sorry i still cant get it work , well it didt have the PDO error anymore ,but there is none data insert in to database.

Kevin Over a year ago

@user3233074 since you're using question mark placeholders, remove the associative indices inside $values using array_values

Collectives™ on Stack Overflow

Binding array of values in prepared statement PDO PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related