CODEWITHSUNDEEP
phpdynamicpdoparametersbind
whispers
whispers

Reputation: 962

PHP/PDO Dynamically binding values (invalid param count error)

I was tasked (stuck) with trying to update an old mysql_query code to be PDO compliant.

This was (is) a messy search form, that was dynamically creating the query string based on field values if (or not) there were any key words submitted along with the form. (ie: any key word is parsed by spaces, and used for BOTH column searches)

So if a search term of 'dog' was entered.. it would search name & title for the key word of 'dog'..

I think I made my way through it.. keeping the main 'function' in-tact for the most part.. and updating when I needed to.

My approach was to take the function that is dynamically adding more criteria to the query string.... and also add this value field name & value to an array, so I can loop through it later on and dynamically bindValues with it..

I am now stick with the ever so popular Invalid Parameters error!!

However its not saying the counts dont match.. its saying it was defined at all.

I'm not clear where my error is stemming from.. (or how to easily see the computed/parsed query string.. or the actual bound parameters) I can just output the sql statement (before it parses any data).. or echo out my values in the array I loop through to (potentially) bind the data to the PDO call..

WHen I echo out the query (string).. and even the values I am attempting to dynamically bind... they all look legit to me:

Query Check: SELECT * FROM pid_information WHERE 1=1 AND (((title LIKE :title0) OR (name LIKE :name0)) AND ((title LIKE :title1) OR (name LIKE :name1))) ORDER BY title, name, link
PARAM CHECK: ':title0' -> %cat%
PARAM CHECK: ':name0' -> %cat%
PARAM CHECK: ':title1' -> %dog%
PARAM CHECK: ':name1' -> %dog%

To re-cap: addCriteria() function is used to dynamically (concat) add to the query 'string'

I also populate an array to be used later to loop through and bindValues with.

Yes I know it is long.. yes I know ugly.. (please, just bear with me!) LOL

//dynamically add criteria to query
$boundSearchValues = array();
function addCriteria($targetFields, $criteriaString, $targetOperator='LIKE'){
    global $boundSearchValues;
    $fieldCount = 0;
    $tempString = "";
    if($criteriaString != ""){
        $criteriaArray = explode(" ", $criteriaString);
        $tempString .= " AND (";
        foreach($criteriaArray as $criteriaIndex => $criteriaValue){
            //is array of fields
            if(is_array($targetFields)){
                $tempString .= "(";
                foreach ($targetFields as $targetField => $fieldName){
                    if($targetOperator != 'LIKE') {
                        $tempString .= "($fieldName ".$targetOperator." :". $fieldName.$fieldCount .")";                            
                        $boundSearchValues[] = [$fieldName.$fieldCount, $criteriaValue];                        
                    }else{
                        $tempString .= "($fieldName LIKE :". $fieldName.$fieldCount .")";
                        $boundSearchValues[] = [$fieldName.$fieldCount, '%'.$criteriaValue.'%'];
                    }
                    if($targetField+1 < count($targetFields)){
                        $tempString .= " OR ";
                    }                       
                }                   
                $tempString .= ")";
                if($criteriaIndex+1 < count($criteriaArray)){
                    $tempString .= " AND ";
                }

            //not an array of fields
            }else{
                if($targetOperator != 'LIKE') {
                    $tempString .= "(".$targetFields . $targetOperator . " :" . $fieldName.$fieldCount . ")";                       
                    $boundSearchValues[] = [$fieldName.$fieldCount, $criteriaValue];
                } else {
                    $tempString .= "(". $targetFields . " LIKE " . $fieldName . $fieldCount . ")";                      
                    $boundSearchValues[] = [$fieldName.$fieldCount, '%'.$criteriaValue.'%'];
                }
            }

            $fieldCount++; //increment counter
        }           
        $tempString .= ")";

    }
    return $tempString;
}   

//start serach query
$searchDetails_sql = "SELECT * FROM $tablename ";   
//dynamically update query string
if($clean_keywords != "") {
    $whereClause = addCriteria(array('title', 'name'), $clean_keywords);
}else{            
    if($title != "" && $title != "all"){
        $whereClause .= " AND title = :" . $title;
    }        
    if($name != "" && $name != "all"){
        $whereClause .= " AND name = :" . $name;
    }            
    if($link != "" && $link != "all"){
        $whereClause .= " AND link = :" . $link ;
    }
}   
$searchDetails_sql .= "WHERE 1=1 ". $whereClause;
$searchDetails_sql .= " ORDER BY title, name, link";    
$searchDetails_stmt = $conn->prepare($searchDetails_sql);

//dynamically bind values   
for($i=0; $i<count($boundSearchValues); $i++){
    $searchDetails_stmt->bindValue("':".$boundSearchValues[$i][0] ."'", $boundSearchValues[$i][1]);
    //$searchDetails_stmt->bindParam("':".$boundSearchValues[$i][0] ."'", $boundSearchValues[$i][1]);
    echo '<br>PARAM CHECK: ' . $boundSearchValues[$i][0] . " / " .  $boundSearchValues[$i][1];
}   
$searchDetails_stmt->execute();
$searchDetails_stmt->setFetchMode(PDO::FETCH_ASSOC);        
$searchDetails = $searchDetails_stmt->fetchAll(); //returns multi-dimensional array (and correct count)

Upvotes: 1

Views: 166

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94662

I think you just messed up the string concatenation in this line

$searchDetails_stmt
    ->bindValue("':".$boundSearchValues[$i][0] ."'", $boundSearchValues[$i][1]);

You dont actually need the : so you could do this

$searchDetails_stmt
    ->bindValue($boundSearchValues[$i][0], $boundSearchValues[$i][1]);

Or fix the concatenation and keep the :

$searchDetails_stmt
    ->bindValue(":".$boundSearchValues[$i][0], $boundSearchValues[$i][1]);

Upvotes: 2

Related Questions