MySQLi prepare and bind_param problem.

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

For the first time I'm using MySQLi and learning the prepare and bind_param and for some reason I can't get it to work. Looks like I'm doing everything correct, but it is telling me otherwise.

PHP Code: [ Select ]
$sql = $db->build_query(array('user_id', USER_TABLE, "user_name = '?'"));
$sql = $db->mysqli_link->prepare($sql);
$sql = $sql->bind_param('s', $vars['form']['username']);
  1. $sql = $db->build_query(array('user_id', USER_TABLE, "user_name = '?'"));
  2. $sql = $db->mysqli_link->prepare($sql);
  3. $sql = $sql->bind_param('s', $vars['form']['username']);

The SQL that is being created there is:
SQL Code: [ Select ]
SELECT `user_id` FROM `cms_users` WHERE user_name = '?'

Quote:
Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\www\cms\login.php on line 77
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

:lol: I figured it out. I was getting the SQL to be generated incorrectly. Instead of:
SQL Code: [ Select ]
SELECT `user_id` FROM `cms_users` WHERE user_name = '?'

I needed
SQL Code: [ Select ]
SELECT `user_id` FROM `cms_users` WHERE user_name = ?

(The weird thing is though is I added those single-quotes because I was getting a weird error. And those quotes fixed that error gave me this error. I removed the quotes again and it works...)...

So now that I have done this... How do I get number of rows with the prepared and bound (query?)
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Ok... I got that figured out :lol:

Different question though. I got the following SQL.
SQL Code: [ Select ]
SELECT `user_id` FROM `cms_users` WHERE username = ? AND password = ?

Could I bind both of those params separately? For example:
PHP Code: [ Select ]
$sql = "SELECT `user_id` FROM `cms_users` WHERE username = ? AND password = ?";
$sql = $db->mysqli_link->prepare($sql);
$sql = $sql->bind_param('s', $vars['form']['username']);
$sql = $sql->bind_param('s', $pass);
  1. $sql = "SELECT `user_id` FROM `cms_users` WHERE username = ? AND password = ?";
  2. $sql = $db->mysqli_link->prepare($sql);
  3. $sql = $sql->bind_param('s', $vars['form']['username']);
  4. $sql = $sql->bind_param('s', $pass);
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Reason I want to do this is because I have a database function that builds the query and I want it to automatically to prepare the query and bind the variables. The thing with this is that I will have x amount of params to bind... meaning it would be dynamic... would change from time to time.

How could I go about and do this?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

I also got that fixed :D
PHP Code: [ Select ]
// The arguments array
$array = array(array($where[1]), $where[2]);
 
// Merging al of the arrays into one array
$array = call_user_func_array("array_merge", $array);
 
// Recreating a reference array (to be used for bind_param)
foreach($array as $key => $value)
{
    // The reference array being created
    $refArray[$key] = &$array[$key];
}
 
// 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);
  1. // The arguments array
  2. $array = array(array($where[1]), $where[2]);
  3.  
  4. // Merging al of the arrays into one array
  5. $array = call_user_func_array("array_merge", $array);
  6.  
  7. // Recreating a reference array (to be used for bind_param)
  8. foreach($array as $key => $value)
  9. {
  10.     // The reference array being created
  11.     $refArray[$key] = &$array[$key];
  12. }
  13.  
  14. // Initiating the mysqli_stmt VIA ReflectionClass
  15. $ref = new ReflectionClass('mysqli_stmt');
  16.  
  17. // Getting the bind_param function
  18. $method = $ref->getMethod("bind_param");
  19.  
  20. // Invoking the function
  21. $method->invokeArgs($stmt, $refArray);

Where I have $where[2] as a numeric array "array('value1','value2')" and $where[1] a string holding the types "ss" made into an array (so I can later recreate one reference array since bind_param needs references).

Learned this VIA stackoverflow and comments in php.net documentation for bind_param.

Hope this helps some.

EDIT: This took me 3 hours to figure out... lol
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9099
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Glad you got it figured out! I haven't really got into prepared statements, how are you liking it compared to the regular way of doing things?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Not very much mainly because it's a bit confusing and they way I started to implement the database access wrapper class makes it kind of hard to code other parts of the class.

For instance:

For preparing and binding I need to call two functions mysqli::execute(); and mysqli::store_result(); but to retrieve it you need mysqli::query();

Also, every function likes to return true even when it needs to return a result set :/ For instance I do the following:
PHP Code: [ Select ]
var_dump($sql->fetch());exit;

But it returns
Quote:
bool(true)

Now I'm really confused on how to use it...
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9099
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Yeah I probably won't be much help with these. I think I had messed with it for around 15 minutes in the past, and didn't like it, so I moved on.

I looked up fetch:

http://www.php.net/manual/en/mysqli-stmt.fetch.php

Looks like that is what it does, returns TRUE if it worked, FALSE otherwise. I am probably wrong, but before you call fetch I think you need to bind it to some variables like:

PHP Code: [ Select ]
    /* bind result variables */
    $sql->bind_result($name, $code);
  1.     /* bind result variables */
  2.     $sql->bind_result($name, $code);


Then after you could do something like:

PHP Code: [ Select ]
    /* fetch values */
    while ($sql->fetch()) {
        printf ("%s (%s)\n", $name, $code);
    }
  1.     /* fetch values */
  2.     while ($sql->fetch()) {
  3.         printf ("%s (%s)\n", $name, $code);
  4.     }
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

I did, I bound 'user_id' to it and when I var_dump(); the returned $user_id, I get a value of &NULL when in reality I'm supposed to get a '1'!!

Here is my current function (also needs to take in dynamic amount of arguments).
PHP Code: [ Select ]
public function fetch_row($sql, $name)
{
    // Exploding the string into an array
    $names = explode(', ', $name);
    var_dump($names); echo '<br /><br />';
   
    // The reference array being created
    $refArray = $this->make_ref($names);
   
    // Initiating the mysqli_stmt VIA ReflectionClass
    $ref = new ReflectionClass('mysqli_stmt');
   
    // Getting the bind_result function
    $method = $ref->getMethod("bind_result");
   
    // Invoking the function
    $method->invokeArgs($sql, $refArray);
   
    // Initiating the return array
    $return = array();
   
    // Filling the return array with data
    while($sql->fetch())
    {
        foreach($names as $value)
        {
            $return[$value] = &$$value;
        }
    }
   
    var_dump($return);exit;
   
    // Returning the built array
    return $return;
}
  1. public function fetch_row($sql, $name)
  2. {
  3.     // Exploding the string into an array
  4.     $names = explode(', ', $name);
  5.     var_dump($names); echo '<br /><br />';
  6.    
  7.     // The reference array being created
  8.     $refArray = $this->make_ref($names);
  9.    
  10.     // Initiating the mysqli_stmt VIA ReflectionClass
  11.     $ref = new ReflectionClass('mysqli_stmt');
  12.    
  13.     // Getting the bind_result function
  14.     $method = $ref->getMethod("bind_result");
  15.    
  16.     // Invoking the function
  17.     $method->invokeArgs($sql, $refArray);
  18.    
  19.     // Initiating the return array
  20.     $return = array();
  21.    
  22.     // Filling the return array with data
  23.     while($sql->fetch())
  24.     {
  25.         foreach($names as $value)
  26.         {
  27.             $return[$value] = &$$value;
  28.         }
  29.     }
  30.    
  31.     var_dump($return);exit;
  32.    
  33.     // Returning the built array
  34.     return $return;
  35. }

Here is what I see
Code: [ Select ]
array(1) {
 [0]=>
 string(7) "user_id"
}
array(1) {
 ["user_id"]=>
 &NULL
}
  1. array(1) {
  2.  [0]=>
  3.  string(7) "user_id"
  4. }
  5. array(1) {
  6.  ["user_id"]=>
  7.  &NULL
  8. }

Any help here from anyone?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8416
  • Loc: USA

Post 3+ Months Ago

Another mystery solved :D
PHP Code: [ Select ]
public function fetch_row()
{
    // Getting metadata
    $meta = $this->last_stmt->result_metadata();
   
    // Iterating through fields and generating the result array
    while($field = $meta->fetch_field())
    {
        $field_name = $field->name;
        $parameters[$field->name] = &$$field_name;
    }
   
    // Binding the result to the fields
    call_user_func_array(array($this->last_stmt, 'bind_result'), $parameters);
   
    // Returning the
    while($this->last_stmt->fetch())
    {
        return $parameters;
    }
}
  1. public function fetch_row()
  2. {
  3.     // Getting metadata
  4.     $meta = $this->last_stmt->result_metadata();
  5.    
  6.     // Iterating through fields and generating the result array
  7.     while($field = $meta->fetch_field())
  8.     {
  9.         $field_name = $field->name;
  10.         $parameters[$field->name] = &$$field_name;
  11.     }
  12.    
  13.     // Binding the result to the fields
  14.     call_user_func_array(array($this->last_stmt, 'bind_result'), $parameters);
  15.    
  16.     // Returning the
  17.     while($this->last_stmt->fetch())
  18.     {
  19.         return $parameters;
  20.     }
  21. }

And usage:
PHP Code: [ Select ]
$sql = array('user_id',
             USER_TABLE,
             array(
                   array(
                         'field' => 'user_id',
                         'value' => $user_id,
                         'type'  => 'i'
                        ),
                   array(
                         'field' => 'user_pass',
                         'value' => md5($pass),
                         'type'  => 's'
                        )
                  )
            );
 
// Creating the SQL required to get the num rows
$sql = $db->build_query($sql);
 
// Executing our query
$db->rresource($sql);
 
// Retrieving number of rows
$num_rows = $db->num_rows();
 
// Retrieving the user_id
$user_id = $db->fetch_row();
  1. $sql = array('user_id',
  2.              USER_TABLE,
  3.              array(
  4.                    array(
  5.                          'field' => 'user_id',
  6.                          'value' => $user_id,
  7.                          'type'  => 'i'
  8.                         ),
  9.                    array(
  10.                          'field' => 'user_pass',
  11.                          'value' => md5($pass),
  12.                          'type'  => 's'
  13.                         )
  14.                   )
  15.             );
  16.  
  17. // Creating the SQL required to get the num rows
  18. $sql = $db->build_query($sql);
  19.  
  20. // Executing our query
  21. $db->rresource($sql);
  22.  
  23. // Retrieving number of rows
  24. $num_rows = $db->num_rows();
  25.  
  26. // Retrieving the user_id
  27. $user_id = $db->fetch_row();

I like! :D
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9099
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Glad you figured it out!

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: thanhpatin and 107 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
 
 

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