1

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)
5
  • 1
    So print out the resulting query $searchDetails_sql and look for where the issue is. You are right, messy code. I doubt anyone is going to desk check all this unless they have to, so please help us help you Commented Mar 6, 2018 at 0:17
  • Wel..noone 'has' to.. but if they are here in the site.. hopefully thats what they are 'into' doing. =) I updated the post with the SQL string output.. everything looks ok to me. 4 params. 4 placeholders in query.. Commented Mar 6, 2018 at 0:23
  • Try $searchDetails_stmt->bindValue($boundSearchValues[$i][0], $boundSearchValues[$i][1]); as the : is actually optional Commented Mar 6, 2018 at 0:29
  • Or $searchDetails_stmt->bindValue(":".$boundSearchValues[$i][0] , $boundSearchValues[$i][1]); Commented Mar 6, 2018 at 0:30
  • @RiggsFolloy That was it! (grrr)... This line was the key: $searchDetails_stmt->bindValue(":".$boundSearchValues[$i][0] , $boundSearchValues[$i][1]); I was not aware I did NOT need the single quotes.. Thanks Commented Mar 6, 2018 at 0:36

1 Answer 1

2

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]);
Sign up to request clarification or add additional context in comments.

1 Comment

You were correct, I was purposely trying to add in the single quotes around the placeholder name.. Answer accepted. (thanks for bearing with me through the ugly code!) =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.