SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to bind mysqli bind_param arguments dynamically in PHP?

    Hi,

    I have been learning to use prepared and bound statements for my sql queries, and I have come out with this so far, it works ok but it is not dynamic at all when comes to multiple parameters or when there no parameter needed,

    PHP Code:
    public function get_result($sql,$parameter)
            {
                
    # create a prepared statement
            
    $stmt $this->mysqli->prepare($sql);
        
                
    # bind parameters for markers
            # but this is not dynamic enough...
                
    $stmt->bind_param("s"$parameter);
        
                
    # execute query 
                
    $stmt->execute();
                
            
    # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
                
    $meta $stmt->result_metadata(); 
                
                while (
    $field $meta->fetch_field()) { 
                    
    $var $field->name
                    $
    $var null
                    
    $parameters[$field->name] = &$$var
                }
                
                
    call_user_func_array(array($stmt'bind_result'), $parameters); 
                     
                while(
    $stmt->fetch()) 
                { 
                    return 
    $parameters;
                    
    //print_r($parameters);      
                
    }
                
                
                
    # close statement
                
    $stmt->close();
            } 
    This is how I call the object classes,

    PHP Code:
     $mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
        
    $output = new search($mysqli); 
    Sometimes I don't need to pass in any parameters,

    PHP Code:
     $sql "
        SELECT *
        FROM root_contacts_cfm
        "
    ;
        
        
    print_r($output->get_result($sql)); 
    Sometimes I need only one parameters,

    PHP Code:
    $sql "
        SELECT *
        FROM root_contacts_cfm
        WHERE root_contacts_cfm.cnt_id = ?
        ORDER BY cnt_id DESC
        "
    ;
        
        
    print_r($output->get_result($sql,'1')); 
    Sometimes I need only more than one parameters,

    PHP Code:
    $sql "
        SELECT *
        FROM root_contacts_cfm
        WHERE root_contacts_cfm.cnt_id = ?
        AND root_contacts_cfm.cnt_firstname = ?
        ORDER BY cnt_id DESC
        "
    ;
        
        
    print_r($output->get_result($sql,'1','Tk')); 
    So, I believe that this line is not dynamic enough for the dynamic tasks above,

    PHP Code:
     $stmt->bind_param("s"$parameter); 
    To build a bind_param dynamically, I have found this on other posts online.

    PHP Code:
    call_user_func_array(array(&$stmt'bind_params'), $array_of_params); 
    And I tried to modify some code from php.net but I am getting nowhere,

    PHP Code:
     if (strnatcmp(phpversion(),'5.3') >= 0//Reference is required for PHP 5.3+ 
            

                
    $refs = array(); 
                foreach(
    $arr as $key => $value
                    
    $array_of_param[$key] = &$arr[$key]; 
        
               
    call_user_func_array(array(&$stmt'bind_params'), $array_of_params);
                  
             } 

    Why?? Any ideas how I can make it work?

    Or maybe there are better solutions??

    Thanks.

  2. #2
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again lauthiamkok,

    I would encourage you to check out the PDO class and see if you like how binding works with that better - check out the examples here:
    http://us.php.net/manual/en/pdostatement.bindparam.php

    Doesn't directly answer your question, but as I said I don't use mysqli and after looking at the hoops you have to jump through to bind variables I'm glad
    aaron-fisher.com - PHP articles and more

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey View Post
    Hi again lauthiamkok,

    I would encourage you to check out the PDO class and see if you like how binding works with that better - check out the examples here:
    http://us.php.net/manual/en/pdostatement.bindparam.php

    Doesn't directly answer your question, but as I said I don't use mysqli and after looking at the hoops you have to jump through to bind variables I'm glad
    Hi there again! thanks for this. Yes I am thinking to move to PDO now!! lol

    Could you please advise me how I can switch/ modify my mysqli database class to PDO database class? Any good references that I can follow?

    Thanks!

    PHP Code:
    # if you want to take full advantage of the mysqli class then you should extend it, and just overriding where needed.
    # this will allow mysqli to handle the errors and allow you to easily access them, there is not point in creating a class 
    # that would just mimic a class, your better extending the class itself.
    # its bad practice to space out your code, it should not effect the way php interpretes the code but it can confuse 
    # further developers and cause issues in the long run when it comes down to shared development.
    class __database extends mysqli
    {
        
    # overwrite parent __construct
        
    public function __construct($hostname null,$username null,$password null,$database null,$port null$socket null)
        {
            
    $hostname $hostname !== null $hostname ini_get("mysqli.default_host");
            
    $username $username !== null $username ini_get("mysqli.default_user");
            
    $password $password !== null $password ini_get("mysqli.default_pw");
            
    $database $database !== null $database "";
            
    $port     $port     !== null $port     ini_get("mysqli.default_port");
            
    $socket   $socket   !== null $socket   ini_get("mysqli.default_socket");
            
            
    parent::__construct($hostname,$username,$password,$database,$port,$socket);

            
    # check if connect errno is set
            
    if (mysqli_connect_errno()) 
            {
                throw new 
    RuntimeException('Cannot access database: ' mysqli_connect_error());
            }
            
    # or:
            # if (mysqli_connect_errno()) 
            # {
            #     throw new Exception('Connect failed: ' . mysqli_connect_error());
            # }
            #
            # or:
            # if ($this->connect_errno != 0)
            # {
            #    # error has occoured, throw our DBConnectException with 
            #    # error message and error code
            #    throw new db_connect_exception($this->connect_error, $this->connect_errno);
            # }
            #
            # to catch the exception, simply use a try / catch block, eg
            # try 
            # {
            #     $database = new database();
            # } 
            # catch (Exception $ex) 
            # {
            #     echo $ex->getMessage(); // or whatever
            # }
        
    }
            
        
    /** 
         * below are the custom/ user-defined methods to overwrite the parent methods
         * @param string $query SQL to execute
         * @return mysqli_result Object when ok
         * @return get_error when error occurs
         * @throws databse_query_exception when error occurs(optional)
         **/
         
        # fetches all result rows as an associative array, a numeric array, or both
        # mysqli_fetch_all (PHP 5 >= 5.3.0)
        
    public function fetch_all($query
        {
            
    $result parent::query($query);
            if(
    $result
            {
                
    # check if mysqli_fetch_all function exist or not
                
    if(function_exists('mysqli_fetch_all')) 
                {
                    
    # NOTE: this below always gets error on certain live server
                    # Fatal error: Call to undefined method mysqli_result::fetch_all() in /.../class_database.php on line 28
                    
    return $result->fetch_all(MYSQLI_ASSOC);
                }
                
                
    # fall back to use while to loop through the result using fetch_assoc
                
    else
                {
                    while(
    $row $result->fetch_assoc())
                    {
                        
    $return_this[] = $row;
                    }

                    if (isset(
    $return_this))
                    {
                        return 
    $return_this;
                    }
                    else
                    {
                        return 
    false;
                    }
                }
            }
            else
            {
                return 
    self::get_error();
            }
        }
        
        
    # fetch a result row as an associative array
        
    public function fetch_assoc($query)
        {
            
    $result parent::query($query);
            if(
    $result
            {
                return 
    $result->fetch_assoc();
            } 
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }
        }
        
        
    # return the current row of a result set as an object
        
    public function fetch_object($query
        {
            
    $result parent::query($query);
            if(
    $result)
            {
                
    $function_result = array();
                
    $i 0;
                while(
    $row $result->fetch_object())
                {
                    
    # you should store each row in an array and then return the array
                    
    $function_result[$i] = $row;
                    
    $i++;
                }
                return 
    $function_result;
            }
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }
            
        }
        
        
    # get a result row as an enumerated array
        
    public function fetch_row($query)
        {
            
    $result parent::query($query);
            if(
    $result
            {
                return 
    $result->fetch_row();
            } 
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }
        }
        
        
    # get the number of rows in a result
        
    public function num_rows($query)
        {
            
    $result parent::query($query);
            if(
    $result
            {
                return 
    $result->num_rows;
            } 
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }
        }
        
        
    # performs a query on the database
        
    public function query($query)
        {
            
    $result parent::query($query);
            if(
    $result
            {
                return 
    $result;
            }
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }

        }
        
        
    # escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection
        
    public function real_escape_string($string)
        {
            
    $result parent::real_escape_string($string);    
            if(
    $result
            {
                return 
    $result;
            } 
            else
            {
                
    # call the get_error function
                
    return self::get_error();
                
    # or:
                # return $this->get_error();
            
    }

        }
        
        
    # display error
        
    public function get_error() 
        {
            if(
    $this->errno || $this->error)
            {
                return 
    sprintf("Error (%d): %s",$this->errno,$this->error);
            }
        }
        
        
    # close the database connection when object is destroyed.
        # the idea with a destructor is that it is automatically called when the object goes out of scope. 
        # as such, there will be nothing to assign a return value to.
        # the best practice is for destructors (and constructors) to not return anything (void).
        # program code should never call the destructor directly.
        
    public function __destruct()
        {
           
    parent::close();
            
    //echo "Destructor Called";
        
    }


  4. #4
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lauthiamkok View Post
    Could you please advise me how I can switch/ modify my mysqli database class to PDO database class? Any good references that I can follow?

    Thanks!
    The nice thing is you don't need to create your own db class wrapper - the PDO class contains all the methods you need. I posted a simple example of how to use it today. Best of luck
    aaron-fisher.com - PHP articles and more

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2008
    Location
    Plymouth, United Kingdon
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey View Post
    The nice thing is you don't need to create your own db class wrapper - the PDO class contains all the methods you need. I posted a simple example of how to use it today. Best of luck
    Thanks for the post. I am now so keen on PDO!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •