PDO identify missing bound variables

Question

One of the more tedious to work with PDO is that it says that some variables are missing

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

... but does not tell which ones. Is there any solution to identify them? Eg

$sql = "SELECT id, name WHERE id = :id AND name like  :search_tem";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', '1');
$stmt->execute(); // throws exception - "**search_term missing"

It's so obvious everyone needs something like this. But I can't a simple solution.

Answer 1

Because of my low reputation I can't comment directly. So imagine this as a reply to the Post of Ragen Dazs on Jan 19 '13 at 15:58. I know this Topic is a few days old, but if some one like me stumble up on this by a google search...

How ever, I had problems with the regular expression in the third last line. As you can see in this example, the expression matches also time values with 00:00:00 as time. So I suggest to use the regular expression from this example.

I also wanted to know if there are needless parameters. This is how I did it, it takes a SQL query like that one in the examples above and a parameter array (See php doc example #2 for PDOStatement::execute).

    /**
 * Checks an parameter array against the sql query. it will tell you if there are any missing or needless parameters
 *
 * @param string $query      Sql query
 * @param array  $parameters Parameters array
 *
 * @return bool|array Returns TRUE if no missing or needless parameters where found or a list with the missing
 *                    or needless parameters
 */
private function checkParameters($query, $parameters)
{
    $parameterTMP = $parameters;
    $parameterCount = count($parameterTMP);
    $regexMatchCounter = preg_match_all("/:[^]\\D\\w*/", $query, $regexMatches);

    // if there are parameter in the $parameters array oder parameters in the sql query
    if( $parameterCount > 0 || $regexMatchCounter > 0 )
    {
        // take every parameter found in the sql query
        foreach( $regexMatches[ 0 ] as $parameterName )
        {
            // check if the required parameter is in the parameters array
            if( !array_key_exists($parameterName, $parameters) )
            {
                // if it is not in the parameters array, add it to the list of missing parameters
                // and continue with the next parameter from the query
                $result[ 'missing' ][] = $parameterName;
                continue;
            }

            // if the required parameter is in the parameter array, delete it from this array
            // so we get a list of parameters that are needless
            unset($parameterTMP[ $parameterName ]);
        }

        // check if there are (needless) parameters left
        if( count($parameterTMP) > 0 )
        {
            // if so, add them to the list of needles parameters
            $result[ 'needless' ] = array_keys($parameterTMP);
        }

        // if at this point $result is an array,
        // some parameters are missing or needless, so we return the result list(s)
        if( isset($result) && is_array($result) )
        {
            return $result;
        }
    }

    // if we reach this point, no missing or needless parameters where found,
    // you are good to go
    return true;
}

If some one want it to throw an exception if something is wrong, just replace "return $result;" with the following lines of code:

    $missingCount = 0;
    $missing = "";

    $needlessCount = 0;
    $needless = "";

    if( array_key_exists('missing', $parameters) )
    {
        $missingCount = count($parameters[ 'missing' ]);
        $missing = " (" . implode(", ", $parameters[ 'missing' ]) . ") ";
    }

    if( array_key_exists('needless', $parameters) )
    {
        $needlessCount = count($parameters[ 'needless' ]);
        $needless = " (" . implode(", ", $parameters[ 'needless' ]) . ")";
    }

    $msg = "There are " . $missingCount . " missing parameter(s)".$missing." and ".$needlessCount." needless parameter(s)".$needless.".";

    throw new Exception($msg);

have fun.