CODEWITHSUNDEEP
phpsqlpdo
FabioLG
FabioLG

Reputation: 11

How enumerate/count all parameters presents on a query with PHP PDO?

I have the following query: 

$query = "SELECT * FROM a_table WHERE col1=:param1 AND col2=:param2 OR col3=:param3"

Does PHP PDO (or any other thing/library) have some function that lists all parameters names presents on this query, that writing something like this:

print_r(list_query_parameters($query));

Produces a output like this:

Array ( [0] => :param1 [1] => :param2 [2] => :param3 )

Upvotes: 0

Views: 135

Answers (1)

meda
meda

Reputation: 45490

EDIT:Final solution to the answer (see below for other alternatives)

Function

function list_query_parameters($query) {
    preg_match_all('/=(:\w+)/', $query, $params, PREG_PATTERN_ORDER);
    return $params[1]
}

Usage:

$query = "SELECT * FROM a_table WHERE col1=:param1 AND col2=:param2 OR col3=:param3"
print_r(list_query_parameters($query));

Program Output

Array
(
    [0] => :param1
    [1] => :param2
    [2] => :param3
)

DEMO

You can also make use of debugDumpParams() for that

$stmt->debugDumpParams();

Description

public void PDOStatement::debugDumpParams ( void ) Dumps the informations contained by a prepared statement directly on the output. It will provide the SQL query in use, the number of parameters used (Params), the list of parameters, with their name, type (paramtype) as an integer, their key name or position, and the position in the query (if this is supported by the PDO driver, otherwise, it will be -1).

Things to note:

You can only get the dumps after you run execute() so your code would look like this:

$stmt = $pdo->prepare($query)
$stmt->execute($params);
$stmt->debugDumpParams();

debugDumpParams() is not doing what you really wanted.

I just wrote this function that should do what you wanted.

please note it is quick and dirty:

function

function list_query_parameters($query) {
$params = explode(":", $query);
unset($params[0]);
foreach ($params as $param) {
    $param = split(" ", $param);
    $result[] = $param[0];
}

return $result;
}

Usage:

$query = "SELECT * FROM a_table WHERE col1=:param1 AND col2=:param2 OR col3=:param3"
print_r(list_query_parameters($query));

Output

Array ( [0] => param1 [1] => param2 [2] => param3 )

DEMO

Using Regex:

Here is the ReGex

preg_match_all('/\s*\w+\s*[=<>]\s*\:(\w+)/', $subject, $matches); 
print_r($matches[1]);

Upvotes: 3

Related Questions