MySQLi INSERT bind_param problem

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

I've got another problem here... getting an error I'm not supposed to get (as far as I know :lol: )
PHP Code: [ Select ]
// The values to be added to the database
$values = array('null', // user id
                'null', // user session id
                '1',    // user permission id
                '1',    // user group id
                '?',    // [s] (username)
                '?',    // [s] (user password)
                'null', // Avatar
                '?',    // [s] (user email)
                '?',    // [i] (Date of birth)
                time()  // Date of register
                );
 
// The values to be bound (mostly because they are user submitted)
$vars = array($vars['form']['username'],
              $pass,
              $vars['form']['email'],
              $dob
             );
 
// No errors occurred... we may go ahead and insert the user into the database
$stmt = $db->insert(USER_TABLE, $user_table_schema, $values, 'sssi', $vars);
  1. // The values to be added to the database
  2. $values = array('null', // user id
  3.                 'null', // user session id
  4.                 '1',    // user permission id
  5.                 '1',    // user group id
  6.                 '?',    // [s] (username)
  7.                 '?',    // [s] (user password)
  8.                 'null', // Avatar
  9.                 '?',    // [s] (user email)
  10.                 '?',    // [i] (Date of birth)
  11.                 time()  // Date of register
  12.                 );
  13.  
  14. // The values to be bound (mostly because they are user submitted)
  15. $vars = array($vars['form']['username'],
  16.               $pass,
  17.               $vars['form']['email'],
  18.               $dob
  19.              );
  20.  
  21. // No errors occurred... we may go ahead and insert the user into the database
  22. $stmt = $db->insert(USER_TABLE, $user_table_schema, $values, 'sssi', $vars);

The INSERT class
PHP Code: [ Select ]
public function insert($tbl_name, $fields, $values, $types, $vars)
{
    // Initiating our insert SQL
    $sql = "INSERT INTO `$tbl_name`";
   
    // Generating the fields
    $sql .= " (`" . implode("`, `", $fields) . "`)";
   
    // Generating the values of the SQL
    $sql .= " VALUES ('" . implode("', '", $values) . "')";
   
    // Initiating the mysqli statement
    $stmt = $this->mysqli_link->stmt_init();
   
    // Setting the last sql used
    $this->last_sql = $sql;
   
    // Preparing the statement
    $stmt->prepare($sql) or die($stmt->error);
   
    // The variable array ([0] => $types, [1+] $vars)
    $array = array(array($types), $vars);
           
    // Merging all of the arrays into one array
    $array = call_user_func_array('array_merge', $array);
   
    // The reference array being created
    $refArray = $this->make_ref($array);
   
    // Initiating the mysqli_stmt VIA ReflectionClass
    $ref = new ReflectionClass('mysqli_stmt');
   
    // Getting the bind_param function
    $method = $ref->getMethod('bind_param');
   
    // Invoking the function (actually binding here)
    $method->invokeArgs($stmt, $refArray);
   
    // Checking against errors in the execution of the SQL
    if(!$stmt->execute())
    {
        // There was an error
        throw die($stmt->error);
    }
   
    // Returning true on success
    return true;
}
  1. public function insert($tbl_name, $fields, $values, $types, $vars)
  2. {
  3.     // Initiating our insert SQL
  4.     $sql = "INSERT INTO `$tbl_name`";
  5.    
  6.     // Generating the fields
  7.     $sql .= " (`" . implode("`, `", $fields) . "`)";
  8.    
  9.     // Generating the values of the SQL
  10.     $sql .= " VALUES ('" . implode("', '", $values) . "')";
  11.    
  12.     // Initiating the mysqli statement
  13.     $stmt = $this->mysqli_link->stmt_init();
  14.    
  15.     // Setting the last sql used
  16.     $this->last_sql = $sql;
  17.    
  18.     // Preparing the statement
  19.     $stmt->prepare($sql) or die($stmt->error);
  20.    
  21.     // The variable array ([0] => $types, [1+] $vars)
  22.     $array = array(array($types), $vars);
  23.            
  24.     // Merging all of the arrays into one array
  25.     $array = call_user_func_array('array_merge', $array);
  26.    
  27.     // The reference array being created
  28.     $refArray = $this->make_ref($array);
  29.    
  30.     // Initiating the mysqli_stmt VIA ReflectionClass
  31.     $ref = new ReflectionClass('mysqli_stmt');
  32.    
  33.     // Getting the bind_param function
  34.     $method = $ref->getMethod('bind_param');
  35.    
  36.     // Invoking the function (actually binding here)
  37.     $method->invokeArgs($stmt, $refArray);
  38.    
  39.     // Checking against errors in the execution of the SQL
  40.     if(!$stmt->execute())
  41.     {
  42.         // There was an error
  43.         throw die($stmt->error);
  44.     }
  45.    
  46.     // Returning true on success
  47.     return true;
  48. }

And I'm getting the following error:
Quote:
Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\www\cms\includes\mmysqli.php on line 406

Line 406 is:
PHP Code: [ Select ]
    $method->invokeArgs($stmt, $refArray);

$refArray shows as:
Code: [ Select ]
Array
(
  [0] => sssi
  [1] => userName
  [2] => passWord
  [3] => email@somesite.com
  [4] => 651189600
)
  1. Array
  2. (
  3.   [0] => sssi
  4.   [1] => userName
  5.   [2] => passWord
  6.   [3] => email@somesite.com
  7.   [4] => 651189600
  8. )

The first key ( [0] ) are the $types of params to be bound... in this case 3 strings and 1 integer (so 4 parameters to be bound overall)

The other keys ( [1] - [4] ) are the actual parameters... 4 of them... just like there are 4 types in the first key (s s s i)... so variables DO match the parameters, and yet, MySQLi gives me the warning that it doesn't.

And there are 4 question marks in the query so that matches as well... MySQLi has forgotten how to do math :lol:

I check in my database, and instead of the username and all those other user-submitted data that I'm supposed to be seeing there, I see those question marks (as expected, because of the warning).

I'm a bit lost here... I'm doing the same technique, same EVERYTHING as I have done in selecting information from the table, and yet, this gives me this problem and that one doesn't.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Here is the function where that works fine!
PHP Code: [ Select ]
public function build_query($sql)
{
    // Checking if $sql was an array
    if(!is_array($sql))
    {
        if($this->silent === false)
        {
            trigger_error('The variable $sql is not an array ', E_USER_ERROR);
        }
        return false;
    }
   
    // Setting a default where clause
    $where = array(null,null,array(),null);
   
    // Checking the WHERE clause
    if(isset($sql[2]) && is_array($sql[2]))
    {
        // Getting our WHERE clause generated
        $where = $this->build_where($sql[2]);
    }
   
    // Checking the ORDER BY clause
    $orderby = ((isset($sql[3])) ? ' ORDER BY ' . trim($sql[3]) : null);
   
    // Checking the LIMIT clause
    $limit = ((isset($sql[4])) ? ' LIMIT ' . trim($sql[4]) : null);
   
    // The generated SQL ready to be prepared and bound (if need be for either)
    $sql = "SELECT {$sql[0]} FROM `{$sql[1]}`{$where[0]}{$orderby}{$limit}";
   
    // Checking if the built query is of correct format
    if($this->valid_query($sql))
    {
        // Initiating the mysqli statement
        $stmt = $this->mysqli_link->stmt_init();
       
        // Setting the last sql used
        $this->last_sql = $sql;
       
        // Preparing the statement
        $stmt->prepare($sql) or die($stmt->error);
       
        // Making sure we have anything to bind
        if(!empty($where[2]))
        {
            // The arguments array
            $array = array(array($where[1]), $where[2]);
           
            // Merging all of the arrays into one array
            $array = call_user_func_array("array_merge", $array);
           
            // The reference array being created
            $refArray = $this->make_ref($array);
           
            // Initiating the mysqli_stmt VIA ReflectionClass
            $ref = new ReflectionClass('mysqli_stmt');
           
            // Getting the bind_param function
            $method = $ref->getMethod("bind_param");
           
            // Invoking the function
            $method->invokeArgs($stmt, $refArray);
        }
       
        // The last stmt object we used
        $this->last_stmt = $stmt;
       
        // Returning the built and prepared SQL query
        return $stmt;
    }
    else
    {
        // There was an error in the SQL Generation, return false
        return false;
    }
}
  1. public function build_query($sql)
  2. {
  3.     // Checking if $sql was an array
  4.     if(!is_array($sql))
  5.     {
  6.         if($this->silent === false)
  7.         {
  8.             trigger_error('The variable $sql is not an array ', E_USER_ERROR);
  9.         }
  10.         return false;
  11.     }
  12.    
  13.     // Setting a default where clause
  14.     $where = array(null,null,array(),null);
  15.    
  16.     // Checking the WHERE clause
  17.     if(isset($sql[2]) && is_array($sql[2]))
  18.     {
  19.         // Getting our WHERE clause generated
  20.         $where = $this->build_where($sql[2]);
  21.     }
  22.    
  23.     // Checking the ORDER BY clause
  24.     $orderby = ((isset($sql[3])) ? ' ORDER BY ' . trim($sql[3]) : null);
  25.    
  26.     // Checking the LIMIT clause
  27.     $limit = ((isset($sql[4])) ? ' LIMIT ' . trim($sql[4]) : null);
  28.    
  29.     // The generated SQL ready to be prepared and bound (if need be for either)
  30.     $sql = "SELECT {$sql[0]} FROM `{$sql[1]}`{$where[0]}{$orderby}{$limit}";
  31.    
  32.     // Checking if the built query is of correct format
  33.     if($this->valid_query($sql))
  34.     {
  35.         // Initiating the mysqli statement
  36.         $stmt = $this->mysqli_link->stmt_init();
  37.        
  38.         // Setting the last sql used
  39.         $this->last_sql = $sql;
  40.        
  41.         // Preparing the statement
  42.         $stmt->prepare($sql) or die($stmt->error);
  43.        
  44.         // Making sure we have anything to bind
  45.         if(!empty($where[2]))
  46.         {
  47.             // The arguments array
  48.             $array = array(array($where[1]), $where[2]);
  49.            
  50.             // Merging all of the arrays into one array
  51.             $array = call_user_func_array("array_merge", $array);
  52.            
  53.             // The reference array being created
  54.             $refArray = $this->make_ref($array);
  55.            
  56.             // Initiating the mysqli_stmt VIA ReflectionClass
  57.             $ref = new ReflectionClass('mysqli_stmt');
  58.            
  59.             // Getting the bind_param function
  60.             $method = $ref->getMethod("bind_param");
  61.            
  62.             // Invoking the function
  63.             $method->invokeArgs($stmt, $refArray);
  64.         }
  65.        
  66.         // The last stmt object we used
  67.         $this->last_stmt = $stmt;
  68.        
  69.         // Returning the built and prepared SQL query
  70.         return $stmt;
  71.     }
  72.     else
  73.     {
  74.         // There was an error in the SQL Generation, return false
  75.         return false;
  76.     }
  77. }
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

I figured out the problem... in the generated SQL the question marks had quotes around them ('?') which to the SQL looked like a value rather then a parameter/variable (whichever one it is) that is needed to be bound. The correct working function is:
PHP Code: [ Select ]
public function insert($tbl_name, $fields, $values, $types, $vars)
{
    // Initiating our insert SQL
    $sql = "INSERT INTO `$tbl_name`";
   
    // Generating the fields
    $sql .= " (`" . implode("`, `", $fields) . "`)";
   
    // Generating the values of the SQL
    $sql .= " VALUES (";
   
    // Counting the number of values we've got here
    $num_vals = count($values);
   
    // Resetting the counter
    $counter = 1;
   
    // Looping through the values to retrieve the column values
    foreach($values as $column_value)
    {
        // Making sure we keep the datatype of the values
        if($column_value == "?")
        {
            $sql .= $column_value;
        }
        elseif(is_string($column_value))
        {
            $sql .= (string) "'{$column_value}'";
        }
        else
        {
            $sql .= (($column_value === null) ? 'null' : (($column_value === false) ? 'false' : $column_value));
        }
       
        // Making sure we put commas where appropriate
        if($counter !== $num_vals)
        {
            $sql .= ', ';
        }
       
        // Increasing the counter
        ++$counter;
    }
   
    // Finishing up the SQL
    $sql .= ')';
   
    // Initiating the mysqli statement
    $stmt = $this->mysqli_link->stmt_init();
   
    // Setting the last sql used
    $this->last_sql = $sql;
   
    // Preparing the statement
    $stmt->prepare($sql) or die($stmt->error);
   
    // The variable array ([0] => $types, [1+] $vars)
    $array = array(array($types), $vars);
   
    // Merging all of the arrays into one array
    $array = call_user_func_array('array_merge', $array);
   
    // The reference array being created
    $refArray = $this->make_ref($array);
   
    // Initiating the mysqli_stmt VIA ReflectionClass
    $ref = new ReflectionClass('mysqli_stmt');
   
    // Getting the bind_param function
    $method = $ref->getMethod('bind_param');
   
    // Invoking the function (actually binding here)
    $method->invokeArgs($stmt, $refArray);
   
    // Saving the current STMT so we could use it later
    $this->last_stmt = $stmt;
   
    // Checking against errors in the execution of the SQL
    if(!$stmt->execute())
    {
        // There was an error
        throw die($stmt->error);
    }
   
    // Returning true on success
    return true;
}
  1. public function insert($tbl_name, $fields, $values, $types, $vars)
  2. {
  3.     // Initiating our insert SQL
  4.     $sql = "INSERT INTO `$tbl_name`";
  5.    
  6.     // Generating the fields
  7.     $sql .= " (`" . implode("`, `", $fields) . "`)";
  8.    
  9.     // Generating the values of the SQL
  10.     $sql .= " VALUES (";
  11.    
  12.     // Counting the number of values we've got here
  13.     $num_vals = count($values);
  14.    
  15.     // Resetting the counter
  16.     $counter = 1;
  17.    
  18.     // Looping through the values to retrieve the column values
  19.     foreach($values as $column_value)
  20.     {
  21.         // Making sure we keep the datatype of the values
  22.         if($column_value == "?")
  23.         {
  24.             $sql .= $column_value;
  25.         }
  26.         elseif(is_string($column_value))
  27.         {
  28.             $sql .= (string) "'{$column_value}'";
  29.         }
  30.         else
  31.         {
  32.             $sql .= (($column_value === null) ? 'null' : (($column_value === false) ? 'false' : $column_value));
  33.         }
  34.        
  35.         // Making sure we put commas where appropriate
  36.         if($counter !== $num_vals)
  37.         {
  38.             $sql .= ', ';
  39.         }
  40.        
  41.         // Increasing the counter
  42.         ++$counter;
  43.     }
  44.    
  45.     // Finishing up the SQL
  46.     $sql .= ')';
  47.    
  48.     // Initiating the mysqli statement
  49.     $stmt = $this->mysqli_link->stmt_init();
  50.    
  51.     // Setting the last sql used
  52.     $this->last_sql = $sql;
  53.    
  54.     // Preparing the statement
  55.     $stmt->prepare($sql) or die($stmt->error);
  56.    
  57.     // The variable array ([0] => $types, [1+] $vars)
  58.     $array = array(array($types), $vars);
  59.    
  60.     // Merging all of the arrays into one array
  61.     $array = call_user_func_array('array_merge', $array);
  62.    
  63.     // The reference array being created
  64.     $refArray = $this->make_ref($array);
  65.    
  66.     // Initiating the mysqli_stmt VIA ReflectionClass
  67.     $ref = new ReflectionClass('mysqli_stmt');
  68.    
  69.     // Getting the bind_param function
  70.     $method = $ref->getMethod('bind_param');
  71.    
  72.     // Invoking the function (actually binding here)
  73.     $method->invokeArgs($stmt, $refArray);
  74.    
  75.     // Saving the current STMT so we could use it later
  76.     $this->last_stmt = $stmt;
  77.    
  78.     // Checking against errors in the execution of the SQL
  79.     if(!$stmt->execute())
  80.     {
  81.         // There was an error
  82.         throw die($stmt->error);
  83.     }
  84.    
  85.     // Returning true on success
  86.     return true;
  87. }

Maybe it can look neater, but it is how it is at the moment. I'll try to optimize it a bit later (if you guys got any ideas, that would be great)!

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 122 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.