Function for PDO preapred stament not work and get error

Question

I make 2 function with same use to take over PDO Preapred Statement, but both of not work .

Function 1 :

function doSave($array, $table) {
if (count($array) == 0) {
    throw new Exception('Array cant be empty');
} else {
    global $connect;
    //prepare the query first
    $prepare_1 = 'INSERT INTO' . ' ' . $table . ' '; //start preparing
    $columns = array();
    foreach ($array as $key => $value) {
        $columns[] = ':' . $key; //gets all columns and add commas
    }
    foreach ($array as $key => $value) {
        $keye[] =  $key; //gets all columns and add commas
    }
    $keyes = implode(', ', $keye);
    $column = implode(', ', $columns);
    //now you can combine everything and prepare
    $stmt99 = $connect->prepare($prepare_1 .'('.$keyes.')'. ' VALUES (' . $column . ')'); 
    //remember to add the values. also test this section as its not tested

    foreach ($array as $key => $value) {
    $test[] = "':" . $key ."' => ". $value;
         }
        $tests = implode(', ', $test);          
        $stmt99->execute($tests);
}
}

When i insert the data i got no error and no data get insert to my database

Function 2 :

function doSave($array, $table) {
if (count($array) == 0) {
    throw new Exception('Array cant be empty');
} else {
    global $connect;
    //prepare the query first
    $prepare_1 = 'INSERT INTO' . ' ' . $table . ' '; //start preparing
    $columns = array();
    foreach ($array as $key => $value) {
        $columns[] = ':' . $key; //gets all columns and add commas
    }
    foreach ($array as $key => $value) {
        $keye[] =  $key; //gets all columns and add commas
    }
    $keyes = implode(', ', $keye);
    $column = implode(', ', $columns);
    //now you can combine everything and prepare
    $stmt99 = $connect->prepare($prepare_1 .'('.$keyes.')'. ' VALUES (' . $column . ')'); 
    //remember to add the values. also test this section as its not tested

    foreach ($array as $key => $value) {
    $test[] = '$stmt99->bindparam('.'":' . $key .'",'. $value.'); ';
         }
        $tests = implode(' ', $test);
            $tests;
        $stmt99->execute();
}
}

i got error when use this function :

SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

This How i use the function :

$array = array('categoryName' => $categoryName, 'categorySort' => $categorySort);
doSave($array, 'category');

This the source of the array :

if (!empty($_POST["categoryName"])) {
        $categoryName = ($_POST["categoryName"]);
        if (!preg_match("/^[a-zA-Z ]*$/",$categoryName)) {
          $errMsg = "<div class='alert alert-danger text-center'><strong>Hanya boleh huruf.</strong></div>"; 
        }
    }  
    if ($_POST["categorySort"] == $check['categorySort']) {
         $errMsg = "<div class='alert alert-danger text-center'><strong>Urutan sudah digunakan.</strong></div>"; 
    }else{
        $categorySort = ($_POST["categorySort"]);
        if (!is_numeric($_POST['categorySort'])) {
          $errMsg = "<div class='alert alert-danger text-center'><strong>Hanya boleh angka.</strong></div>"; 
        } 
    }

What possibly go wrong from this 2 function both function for same use. Function 1 (named param) Function 2 (bindparam) ?

Answer 1

The following is not fully tested but displayed what I expected when I tested it using echo statements.

You should check the return value of prepare before attempting to execute the statement because it will return false if the statement failed to be prepared correctly.

function doSave( $array, $table ) {
    try{
        /* if you throw exceptions you should catch them!! */

        if( empty( $array ) )throw new Exception('Array cant be empty');
        if( empty( $table ) )throw new Exception('Table name cannot be empty');
        global $connect;

        /* placeholder variables */
        $prepare = $columns = $values = array();
        $result = false;

        $table = preg_replace("@[',\.]@",'',$table);// whatever chars deemed appropriate to replace
        $prepare[]="insert into `{$table}` ";

        /* iterate through source array */
        foreach( $array as $key => $value ) {
            $columns[] = $key;
            $values[ $key ] = $value;
        }

        $strcolumns = implode('`,`',$columns);
        $strplaceholders = ':'.implode(', :',$columns);

        /* append columns and placeholders */
        $prepare[]="( `$strcolumns` ) values ( $strplaceholders );";

        /* finalise sql statement */
        $sql=implode('',$prepare);

        $stmt = $connect->prepare( $sql );
        if( $stmt ){
            /* bind the params */
            foreach( $values as $key => $value ) $stmt->bindParam( ':'.$key, $value );

            /* execute the statement */
            $result = $stmt->execute();
        } else {
            throw new Exception('Error preparing sql statement');
        }
        return $result;
    }catch( Exception $e ){
        exit( $e->getMessage() );
    }
}

The assumption I made for the code was an input array like this

$t='mytable';
$a=array(
    'id'    =>  '303',
    'name'  =>  'bob',
    'size'  =>  'small',
    'weight'=>  'heavy'
);

Answer 2

NOTE:
You have two functions both with the same name. How is PHP meant to know the which function you're calling?

Function 2:

foreach ($array as $key => $value) {
    $test[] = '$stmt99->bindparam('.'":' . $key .'",'. $value.'); ';
         }

Because you encased this in [single] quotes, this value is no longer an object method call but is just a string. This means that when you then implode this array all you're making is a longer string.

Also, because you're using single quotes, PHP will not recognise the value $stmt99 as being a PHP object reference, instead taking it literally as dollar sign, s character, t character, m character, etc....

So PDO has no values to bind into the SQL given.

Fix:

 foreach ($array as $key => $value) {
     $stmt99->bindparam(":" . $key , $value);
     }
  unset($key,$value);  // always tidy up after foreach loops.

• A better fix can be found exampled here