Fatel error at PDOStatement execute(Array)

Question

i had created function that works to insert into database , just pass into it 2 parameters [ table name , associative array "table schema" ] . the array is contain column name and it's value. first that is array look:

$tableSchema = [
        'member_name'       => $member_name,
        'member_pass'       => $member_pass,
        'member_email'      => $member_email,
        'member_fullname'   => $member_fullname,
        'member_image'      => $profileImage,
        'member_role'       => $member_role,
        'member_gender'     => $member_gender,
        'member_phone'      => $member_phone,
        'member_country'    => $member_country,
    ];

and that's it my function code :

 function insertInto($table, $columns)
 {   

    // This function to echo parameters at sql stmt 
    function setParams($columns)
    {
        $param = '';
        foreach($columns as $key => $value) {
            $param .= $key . ' = :' . $key . ', ';
        }
        return trim($param, ',');
    }


    $array = [];
    foreach ($columns as $key => $value) {
        array_push($array, $key);
    }

    global $db_connect;
    $stmt = $db_connect->prepare('INSERT INTO '. $table .' SET '. setParams($columns) .'');
    $stmt->execute($array); /* Here is my problem */
 }

When i try to run function this error appear:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\xampp\htdocs\blogfive\core\functions\functions.php:87 Stack trace: #0 C:\xampp\htdocs\blogfive\core\functions\functions.php(87): PDOStatement->execute(Array) #1 C:\xampp\htdocs\blogfive\dashboard\template\pages\members\insert.member.php(65): insertInto('members', Array) #2 C:\xampp\htdocs\blogfive\dashboard\members.php(32): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\blogfive\core\functions\functions.php on line 87

Why do you need a nested function? It would be just as easy to run the loop and set a string with the result. — Nigel Ren, Dec 11 '19 at 07:41

score 0 · Answer 1 · answered Dec 11 '19 at 07:11

$array is an array of the parameter names which is wrong. You need an associative array with keys being the parameter names and values the actual values like below:

 function insertInto($table, $columns)
 {   

    // This function to echo parameters at sql stmt 
    function setParams($columns)
    {
        $param = '';
        foreach($columns as $key => $value) {
            $param .= $key . ' = :' . $key . ', ';
        }
        return trim($param, ',');
    }
    global $db_connect; // Bad practice, try to find an alternative

    $stmt = $db_connect->prepare('INSERT INTO '. $table .' SET '. setParams($columns) .'');
    $stmt->execute($columns); 
 }

@Akam We can use SET in INSERT as well. apokryfos anwsers is correct — BhAvik Gajjar, Dec 11 '19 at 07:20

Fatel error at PDOStatement execute(Array)

1 Answers1