CODEWITHSUNDEEP
phpmysqliprepared-statement
majid
majid

Reputation: 51

using prepared statements and dynamic param in mysqli

I'm using a prepared statement and these functions are part of a mysqli class .They work well for singl condition But do not right answer for multiple condition like this:

SelectByOrderCondi('user','username=? AND name=? AND email=? ' , $Array )

Here's my functions :

public function SelectByOrderCondi($Table_Name, $Conditions='' ,$Array_Conditions_Limit=null, $OrderBy='', $Limit='', $Selected_Fields='*')
{
    $Query = "SELECT ".$Selected_Fields." FROM ".$Table_Name;
    if(!empty($Conditions))
        $Query .= " WHERE ".$Conditions;
    if(!empty($OrderBy))
        $Query .= " ORDER BY ".$OrderBy;
    if(!empty($Limit))
        $Query .= " LIMIT ".$Limit;

    $Statment = $this->ConnectionResult->prepare($Query);
    if(isset($Array_Conditions_Limit)  )
     {
        $Statment = $this->DynamicBindVariables($Statment, $Array_Conditions_Limit);
        $Statment->execute();
        return $Statment->get_result();
     }
     else
        return false ;

}

Add my class this function:

Private function GetType($Item)
{
    switch (gettype($Item)) {
        case 'NULL':
        case 'string':
            return 's';
            break;

        case 'integer':
            return 'i';
            break;

        case 'blob':
            return 'b';
            break;

        case 'double':
            return 'd';
            break;
    }
    return '';
}

and change DynamicBindVariables functions as follows:

public function DynamicBindVariables($Statment, $Params)
{
    if (is_array($Params) && $Params != null)
    {
        // Generate the Type String (eg: 'issisd')
        $Types = '';
        foreach($Params as $Param)
        {
            $Types .= $this->GetType($Param);
        }
        // Add the Type String as the first Parameter
        $Bind_names[] = $Types;

        // Loop thru the given Parameters
        for ($i=0; $i<count($Params);$i++)
        {
            $Bind_name = 'bind' . $i;
            // Add the Parameter to the variable 
            $$Bind_name = $Params[$i];
            // Associate the Variable as an Element in the Array
            $Bind_names[] = &$$Bind_name;
        }
        // Call the Function bind_param with dynamic Parameters
        call_user_func_array(array($Statment,'bind_param'), $Bind_names);
    }
    else
    {
        $Types .= $this->GetType($Param);
        $Statment->bind_param($Types ,$Params);
    }

    return $Statment;
}

Now it works properly

Upvotes: 0

Views: 1758

Answers (1)

Jason OOO
Jason OOO

Reputation: 3552

Just side note, your code might be incorrect for determining types, see this test:

var_dump(is_int("1"));      //bool(false)
var_dump(is_float("1.1"));  //bool(false)
var_dump(is_string("1.1")); //bool(true)

You can use:

ctype_digit() for integers or if((int) $Param == $Param)

is_numeric() for float or if((float)$Param == $Param)

Upvotes: 2

Related Questions