str_replace with array parameter not working

$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);
}

I expect $1 and $4 in the $valid_like_clauses array elements to replaced with parms array value, but it doesn’t happen?

Thanks for any help you can provide.

alex

$valid_like_clauses=str_replace($key, $value, $valid_like_clauses);

Should be

$sql_command = str_replace($key, $value, $sql_command );

Or maybe i’m not understanding you right. There is nothing in $valid_like_clauses based off just that code above.

preg_match_all(‘/ LIKE \$\d+ | LIKE \$\d+$/’, $sql_command, $valid_like_clauses) create $valid_like_clauses with following elements:

0: LIKE $1
1: LIKE $20
2: LIKE $4

after $valid_like_clauses=str_replace($key, $value, $valid_like_clauses) I expect $valid_like_clauses to be:
0: LIKE parm one \%
1: LIKE $20
2: LIKE parm two

Ah I see,

It’s because $valid_like_clauses is a multi-dimensional array.

You’ll have to access it as

$valid_like_clauses[0]=str_replace($key, $value, $valid_like_clauses[0]);

$valid_like_clauses is a single dimentional array. When I experimented by setting $valid_like_clauses with the values that I expect from preg_match… then it str_replace works as expected. When I put the following line right before the foreach loop, the results are what I expect them to be.

$valid_like_clauses = array("LIKE $1 ", “LIKE $20”, “LIKE $4”);

You have this:


$valid_like_clauses=str_replace($key, $value, $valid_like_clauses);

That is telling $valid_like_clauses to be set to the results of str_replace (a string). My guess is you are seeing $valid_like_clauses is just the last value.

If you want to replace them, you need to do this:


$valid_like_clauses[$key]=str_replace($key, $value, $valid_like_clauses);

When I ran your code, it returned a 2 dimensional array, did you try running my code?

You are right, valid_like_clause remains unchanged. May be I am missing something obvious or my thinking is all wrong. 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

As I mentioned in my earlier post that it works exactly like I described here when I hard code $valid_like_clauses = array("LIKE $1 ", “LIKE $20”, “LIKE $4”); Its only when $valid_like_clauses array is established using preg_match … that its not working.

Thank your posting reply and helping

Thank you for your help, i appreciate it.

Yes, I tried your code but its not giving the desired result. I have posted this with the reply before yours, but I will post it for as well.

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

As I mentioned in my earlier post that it works exactly like I described here when I hard code $valid_like_clauses = array("LIKE $1 ", “LIKE $20”, “LIKE $4”); Its only when $valid_like_clauses array is established using preg_match … that its not working.

This is what I get:

http://i4c.org/BN

When I run:


<?php
$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
print_r($valid_like_clauses);
foreach ($parms as $key => $value) {
$value = addcslashes($value, "%_");


$valid_like_clauses[0]=str_replace($key, $value, $valid_like_clauses[0]);
}

print_r($valid_like_clauses[0]);


Note where the two print_r() are.

Got it!!

I was treating the multi-dimensional array returned by preg_match… as a single dimensional array. Even though you had mentioned it in your earlier post, your code made me see it.

Thank you so much !