#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    45
    Rep Power
    10

    Mysqli bind_result to return array.


    im trying to have my function return an array from a mysqli query using bind_result() but im stuck at the part where the fetching happens.

    so far ive got:
    PHP Code:
    public function getBlog($user_id) {
            
    $this->user_id $user_id;
            
    $query "select blogid, title, timestamp, body from pb_post where userid=? order by timestamp desc limit 1";
            if(
    $stmt $this->db->prepare($query)) {
                
    $stmt->bind_param('i'$this->user_id);
                
    $stmt->execute();
                
                
    $meta $stmt->result_metadata();
                while (
    $field $meta->fetch_field()) {
                    
    $parameters[] = $field->name;
                }
                
    call_user_func_array(array($stmt'bind_result'), $parameters);
                
                while(
    $stmt->fetch()) {
                    
    print_r($parameters);
                        
                }
                
                
    $stmt->close();
            }
            
               
        } 
    through my understanding (which is wrong, obviously) when i print_r() the array it should get the results according to the parameters[]. but its still an indexed array and no results show, just the field names. any ideas of what im doing wrong?
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,394
    Rep Power
    9645
    Couple things wrong:
    - The parameter array to call_user_func_array is not passed by reference. $parameters will not be updated if bind_result does something to it (which it doesn't).
    - bind_result wants variables, not strings. $parameters is an array of strings.

    So with that in mind,
    PHP Code:
    while ($field $meta->fetch_field()) {
        
    $var $field->name;
        $
    $var null;
        
    $parameters[] = &$$var;

    I think that will work.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    45
    Rep Power
    10
    thanks for the quick reply. the above code works, but before i incorporate that in my code id like to understand it fully.

    upon reading your post i looked up variable variables and the examples given were simple enough.

    what i dont understand in your code is why is $$var = null?

    and why does parameters[] = &$$var?

    for my final result of this query id like to return an associative array, column_name => value. but once step at a time i guess
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,394
    Rep Power
    9645
    The $$var=null is because of my habit of initializing variables before using them.
    PHP Code:
    foo($variable); // variable is not defined, I don't like this

    $variable null;
    foo($variable); // variable is defined, even though it's not necessary 
    So that statement doesn't serve any purpose than to appease me.

    You'll be calling bind_result using a variable number of arguments, so you need call_user_func_array. But like I said, those arguments are supposed to be variables, not strings.
    So instead of adding strings to $parameters it adds variables: $var is the name, $$var is the variable itself. However, bind_result probably needs those variables by reference, not by value: a normal $parameters[] = $$var; just copies $$var into the array. bind_result will modify that value so the stuff in that array need to be by-reference too. That's what the & does.

    If you want a column=>value result, simply change that one statement to
    PHP Code:
    $parameters[$var] = &$$var
    Remember, $var isn't just the variable name but the field name too.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    45
    Rep Power
    10
    good explanation. i did some experimenting with some test code just to get the feel of it and i think i know whats going on. learned something new today. just hope i can apply it when the time comes.

    thanks for taking the time requinix.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    2
    Rep Power
    0

    Amazing piece of code


    I dont usually post to these things but this thread was so helpful i just cant help but say thanks. I have been fighting with this bind_results for days and was just about to give up.

    Im new to OOP and Im having trouble with one thing. How do i get this to return an array to use?

    print_r($parameters) prints all the appropriate rows from the database, but I cant get it to return all the rows. If I change print_r($parameters) to return $parameters, all I get is the last row of the array. So I assume I'm catching the end of the loop. How do i get it to return ALL the rows in an array?
    Last edited by Stink Bait; April 23rd, 2010 at 03:48 PM. Reason: on further usage snippet not quite doing what i need

IMN logo majestic logo threadwatch logo seochat tools logo