Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

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


    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.

  • #2
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The issue is resolved. The problem was that I was treating the multi-dimensional array returned by preg_match.. as a single dimensional array. the foreach loop should be

    foreach ($parms as $key => $value) {
    $value = addcslashes($value, "%_");
    $valid_like_clauses[0]=str_replace($key, $value, $valid_like_clauses[0]);
    }

    I had to reference valid_like_clause as valid_like_clause[0].


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •