alexv55
02-17-2012, 08:20 PM
$sql_command = "select * from table where col1 like $1 " .
"and col2 like $20 " .
"and col3 like $1and col4 like $4 " .
"and col5 = $1";
$parms = array('$1' => "parm one %", '$4' => "parm two", );
// tidy up sql_command
$sql_command = trim($sql_command);
$sql_command = preg_replace('/\s+/', ' ', $sql_command); // strip all white spaces.
$sql_command=strtoupper($sql_command);
// extract all like clause from sql command into array.
if ( preg_match_all('/ LIKE \$\d+ | LIKE \$\d+$/', $sql_command, $valid_like_clauses) < 1 )
$valid_like_clauses = array();
// replace variables in like clause with values of parms array
foreach ($parms as $key => $value) {
$value = addcslashes($value, "%_");
$valid_like_clauses=str_replace($key, $value, $valid_like_clauses);
}
The way I think my foreach should work is as follows:
With each iteration of my foreach loop, I get a new $key and $value strings. During each iteration, str_replace(..) function spins through all elements of $valid_like_clause array and replaces $key with $value if it finds them in any of the array elements and returns an updated $valid_like_clauses. The updated array is used in the next iteration of foreach with a new key and value to be replaced in all elements of the array.
So,
ITERATION ONE before str_replace is executed
$key = $1
$value = parm one \%
$value_like_value[0] = LIKE $1
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION ONE after str_replace is executed
$key = $1
$value = parm one \%
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION TWO before str_replace is executed
$key = $4
$value = parm two
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION TWO after str_replace is executed
$key = $4
$value = parm two
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE parm two
When I experiment with hard coding $valid_like_clauses = array("LIKE $1 ", "LIKE $20", "LIKE $4"), it works as I described here. Its only when $valid_like_clauses array is established using preg_match .... that its not working.
Thanks for any insight you can provide.
"and col2 like $20 " .
"and col3 like $1and col4 like $4 " .
"and col5 = $1";
$parms = array('$1' => "parm one %", '$4' => "parm two", );
// tidy up sql_command
$sql_command = trim($sql_command);
$sql_command = preg_replace('/\s+/', ' ', $sql_command); // strip all white spaces.
$sql_command=strtoupper($sql_command);
// extract all like clause from sql command into array.
if ( preg_match_all('/ LIKE \$\d+ | LIKE \$\d+$/', $sql_command, $valid_like_clauses) < 1 )
$valid_like_clauses = array();
// replace variables in like clause with values of parms array
foreach ($parms as $key => $value) {
$value = addcslashes($value, "%_");
$valid_like_clauses=str_replace($key, $value, $valid_like_clauses);
}
The way I think my foreach should work is as follows:
With each iteration of my foreach loop, I get a new $key and $value strings. During each iteration, str_replace(..) function spins through all elements of $valid_like_clause array and replaces $key with $value if it finds them in any of the array elements and returns an updated $valid_like_clauses. The updated array is used in the next iteration of foreach with a new key and value to be replaced in all elements of the array.
So,
ITERATION ONE before str_replace is executed
$key = $1
$value = parm one \%
$value_like_value[0] = LIKE $1
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION ONE after str_replace is executed
$key = $1
$value = parm one \%
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION TWO before str_replace is executed
$key = $4
$value = parm two
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE $4
ITERATION TWO after str_replace is executed
$key = $4
$value = parm two
$value_like_value[0] = LIKE parm one \%
$value_like_value[1] = LIKE $20
$value_like_value[2] = LIKE parm two
When I experiment with hard coding $valid_like_clauses = array("LIKE $1 ", "LIKE $20", "LIKE $4"), it works as I described here. Its only when $valid_like_clauses array is established using preg_match .... that its not working.
Thanks for any insight you can provide.