0

I'm trying to replace every "?" in string by values from array. Each "?" is the next value from array.

I was wondering if there is a better way to do the following:

$query = 'SELECT * FROM pages WHERE id = ? AND language = ?';
$values = array('1', 'en');
foreach ($values as $value) {
    $query = preg_replace('/\?/', '\''.$value.'\'', $query, 1);
}
echo '<pre>'.print_r($query, true).'</pre>';

Would like to do that with native PHP (not a PDO extension).

AndVla
  • 713
  • 2
  • 6
  • 18

2 Answers2

2

Mysqli and PDO are as native as it gets with PHP.

You can use bind_param from mysqli to accomplish this. Example:

$stmt = $mysqli->prepare("SELECT * FROM pages WHERE id = ? AND language = ?");
$stmt->bind_param('is', 1, "en");

In this case the i and s are referencing the type of the parameter, as seen in this table (available in link):

i   corresponding variable has type integer
d   corresponding variable has type double
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets
Menno
  • 12,175
  • 14
  • 56
  • 88
  • 1
    What letter should I use to bind an array to be used with `IN()` statement? – Your Common Sense Apr 30 '13 at 10:59
  • @YourCommonSense: As you can see in [this post](http://no2.php.net/manual/en/mysqli-stmt.bind-param.php#103622), there are some downsides on using `IN()` with `mysqli` due to the way `MySQL` handles prepared statements. Should mention that this post is 2 years old though. – Menno Apr 30 '13 at 11:20
  • I am curious, you you use mysqli yourself? – Your Common Sense Apr 30 '13 at 11:49
  • @YourCommonSense Yes I do, though I mainly write programs with `java`. In this case I would write a helper function to bind them individually. [Here](http://stackoverflow.com/questions/1534377/php-mysqli-where-in) is a question on exactly this issue. – Menno Apr 30 '13 at 16:09
  • Please forgive my curiosity, but did you try any solution from there? – Your Common Sense Apr 30 '13 at 18:18
  • No, though honestly I haven't run into any case recently in which I needed to use the `IN` operator from SQL (since I don't work that much with PHP anymore). I'm curious, do you happen to know the 'right' way to solve an issue like this? – Menno Apr 30 '13 at 18:23
  • Yes. You need to add something like this `$rvalues = array(); foreach($values as $value) $rvalues[] = &$value;` to the only proper answer there (call_user_func one). but the resulting amount of code exceeds any limits of sanity in my opinion. So, I count mysqli prepared statements pretty unusable for the real life usage. – Your Common Sense Apr 30 '13 at 18:52
  • You're right. Normally I don't use prepared statements with `mysqli` because I usually concatenate (my PHP-apps are mainly used in a private environment where I don't need to protect my database). Nevertheless, you may have convinced me here to consider `PDO` in the future ;) – Menno Apr 30 '13 at 18:57
2

Use binding

in PDO 

$sth = $dbh->prepare('SELECT name, colour, calories
                      FROM fruit
                      WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();

http://php.net/manual/pl/pdostatement.bindvalue.php

in mysqli

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

http://www.php.net/manual/en/mysqli-stmt.bind-param.php

if you want to do it in STUPID way you can use loop or recursion

$select = "SELECT * FROM pages WHERE id = ? AND language = ?";
$params = array('param', 'param2');
while(preg_match('/\?/', $select)) $select = str_replace("?", array_shift($params),     $select);

but it's stupid

Robert
  • 19,800
  • 5
  • 55
  • 85
  • Thank you. The only reason I'm trying to do that is because i want to print the full query with values on execution fail. Thank you any way ;) – AndVla Apr 30 '13 at 10:50