PHP replace characters in a string "one-by-one" in a loop

Question

There is MySQL query with unknown number of parameters (dynamically built) of the following format:

SELECT * FROM actions WHERE user1 = ? AND user10 = ? AND user11 = ? AND time = :time

Since I am using PDO prepared statement, I can't mix named and positional parameters in one query. So I need to repalce all question marks with named parameters sequentialy. I have an Array() that holds these parameters:

$parameters = Array();
$parameters['time'] = 1234567;

Now I need to replace every question mark with a sequential named parameter, so the query will look like this:

SELECT * FROM actions WHERE user1 = :user0 AND user10 = :user1 AND user11 = :user2 AND time = :time

And $parameters would contain every named parameter in it. I need to find every " ? " in the query string and looping through the occurences, replace them with an incrementing string. In JavaScript I would use a regex to find ? and pass them to a function, which would have a global incrementing variable to track the current named parameter number, but I have no idea how to do this in PHP.

P.S. Besides replacing them sequentially, I would also need to add parameters to array:

$parameters['user0'] = $user;
$parameters['user1'] = $user;
$parameters['user2'] = $user;

are there users indeed the same? why all that mess then? just have one placeholder for all. — Your Common Sense
– Your Common Sense, Commented Feb 23, 2014 at 18:41
@YourCommonSense SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens — Xeos
– Xeos, Commented Feb 23, 2014 at 19:14
@davidstrachan I already have a query built, but it contains ? and named parameters, which is 'illegal' format. I only need to change ? to named parameters in a loop. — Xeos
– Xeos, Commented Feb 23, 2014 at 19:47

Your Common Sense · Accepted Answer · 2014-02-23 18:45:49Z

1

You are doing something extremely strange.

First of all, there is not a single reason to combine different placeholders. Just make it

AND time = ?

and then just execute.

Yet if you want named parameters - add them at the same time you are building a query. Apparently, user1 = ? AND user10 = ? AND user11 = ? being dynamically built. Why don't you just add named placeholders at the same time?

BTW, if you have to bind the same variable to all the marks, then just use the the single named placeholder for all

WHERE user1 = :user AND user10 = :user AND user11 = :user AND time = :time

then turn emulation mode off and then execute with

$stmt->execute(array('user'=>$user,'time'=>123));

edited Feb 23, 2014 at 18:45

answered Feb 23, 2014 at 18:39

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

2 Comments

Xeos Over a year ago

I've tried array('user'=>$user,'time'=>123) before, but it did not work. What do you mean by emulation mode?

Xeos Over a year ago

The query is built from arrays and if() statements, so it is hard to know how many question marks there will be and in which order they will appear.

Xeos · Accepted Answer · 2014-02-23 23:31:31Z

-2

All I was looking for:

$count = 0;
preg_replace_callback(
    "/\\?/",
    function ($matches) {
        global $count;
        return ':user' . (++$count);
    },
    $query_string
);

Which would return exactly what is needed.

answered Feb 23, 2014 at 23:31

Xeos

6,56713 gold badges57 silver badges84 bronze badges

Collectives™ on Stack Overflow

PHP replace characters in a string "one-by-one" in a loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related