PHP Replace character with string from an array

Question

For mysql I use format:

$sql = "select * from table where area_id = ? and item_id = ?";

Then prepare and bind the parameters etc. If the query fails and I log the $sql variable then I get exactly the string above which isn't that useful. What I want is the sql string with the bound values in. As I understand it there is no easy way to do this so I am thinking I can do something like:

sql_log(str_replace('?', array($area_id, $item_id), $sql));

To get something like this in my log:

"select * from table where area_id = West and item_id = West" (spot the error!)

So I know what my error is. But it doesn't work. I get this:

"select * from table where area_id = Array and item_id = Array"

Solution here is preg_replace_callback as your items are always ? — u_mulder
– u_mulder, Commented Oct 16, 2018 at 20:22
Possible duplicate of MySQLi prepared statements error reporting — user10226920
– user10226920, Commented Oct 16, 2018 at 20:24

splash58 · Accepted Answer · 2018-10-16 20:22:35Z

3

Use preg_replace_callback function

$sql = "select * from table where area_id = ? and item_id = ?";
$replace = array('area_id', 'item_id');
echo preg_replace_callback('/\?/', function($x) use(&$replace) { return array_shift($replace);}, $sql);
// select * from table where area_id = area_id and item_id = item_id

answered Oct 16, 2018 at 20:22

splash58

26.2k3 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Pearce · Accepted Answer · 2018-10-16 20:24:30Z

2

Unfortunately, mysqli does not have a good way to get just the query. You could use a method to do the replacement of your parameters:

function populateSql ( string $sql, array $params ) : string {
    foreach($params as $value)
        $sql = preg_replace ( '[\?]' , "'" . $value . "'" , $sql, 1 );
    return $sql;
}

answered Oct 16, 2018 at 20:24

Justin Pearce

5,1172 gold badges26 silver badges37 bronze badges

Comments

Carlos Bello · Accepted Answer · 2018-10-16 20:28:13Z

1

try with this:

sprintf('select * from table where area_id = %s and item_id = %s', $area_id, $item_id);

or

sprintf('select * from table where area_id = "%s" and item_id = "%s"', $area_id, $item_id);

if your fields in the database are integers the %s you have to replace it with %d and do not use quotes

answered Oct 16, 2018 at 20:28

Carlos Bello

212 bronze badges

2 Comments

funnyfish Over a year ago

That won't bind properly!

mickmackusa Over a year ago

@funnyfish this question isn't about constructing a viable sql query. This is about replacing the placeholders with the bound values for the sake of storing in an error log.

bmatovu · Accepted Answer · 2019-12-18 09:45:43Z

Laravel has a beautiful helper for just that.

/**
  * Replace a given value in the string sequentially with an array.
  *
  * @param  string  $search
  * @param  array   $replace
  * @param  string  $subject
  * @return string
  */
function replaceArray($search, array $replace, $subject)
{
    $segments = explode($search, $subject);

    $result = array_shift($segments);

    foreach ($segments as $segment) {
        $result .= (array_shift($replace) ?? $search).$segment;
    }

    return $result;
}

$sql = 'SELECT * FROM tbl_name WHERE col_b = ? AND col_b = ?';

$bindings = [
  'col_a' => 'value_a',
  'col_b' => 'value_b',
];

echo replaceArray('?', $bindings, $sql);

// SELECT * FROM tbl_name WHERE col_b = value_a AND col_b = value_b

Source: Str::replaceArray()

Collectives™ on Stack Overflow

PHP Replace character with string from an array

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related