Replace "?" with array data

Question

I'm using a software that use PDO to do request. The database requests are like this :

$query = "SELECT * FROM res_attachments WHERE res_id = ? AND status = ?";
$data = array('152', 'DEL');

The question I have is, how can I get the whole request with the "?" replace by $data elements ? I get this infos ($query and $data) after the query, I just have to "merge" it. I have to make a dynamic things, to works with a request with 2 arg or 10 args.

The result I want have to looks like :

"SELECT * FROM res_attachments WHERE res_id = '152' AND status = 'DEL'"

Solution

For those who have a similar issue, here is the solution I make :

$query = "SELECT * FROM res_attachments WHERE res_id = ? AND status = ?";
$data = array('152', 'DEL');
$tab = explode("?",$query);
for($i =0; $i < count($tab); $i++){
    $Request .= $tab[$i] . "'" . $data[$i] . "'";
    $finalRequest = str_replace('\'\'', '', $Request); // delete the double quote at the end
}
var_dump($finalRequest);

Read more http://php.net/manual/en/pdo.prepare.php statement must be prepared to bind values to it. `query` is for execute an query without binding parameters. You will find that all in the docs. — JustOnUnderMillions, Apr 10 '17 at 13:50
Your question isn't clear. Please edit and explain what you mean by "without the "?"" -- IOW, do you mean without the WHERE clause? — Sloan Thrasher, Apr 10 '17 at 13:51
I don't think the people who gave answers (so far) understood this question fully. — Funk Forty Niner, Apr 10 '17 at 14:00
Yes @Fred-ii- , I've update my inital question to be as clear as I could — Nathan30, Apr 10 '17 at 14:02
@Nathan30 I may not be able to help solve this but a question that does come to mind and may be relevant to what you're asking is: Will the number of entries in the array always remain the same, being two? You may want to re-edit it, if it stands to contain more items in the array. This looks more like auto-population from an array into multiple columns. — Funk Forty Niner, Apr 10 '17 at 14:05

Masivuye Cokile · Answer 1 · 2017-04-10T14:11:04.673

3

What you are looking for is prepare() then execute rather than query()

query() runs a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues.

This is what you want :

<?php

$query = "SELECT * FROM res_attachments WHERE res_id = ? AND status = ?";
$data = array($res_id, $status);
$stmt = $db->prepare($query);
if($stmt->execute($data)){
    //fetch your results

}
?>

or :

<?php

$query = "SELECT * FROM res_attachments WHERE res_id = :id AND status = :status";
$stmt  = $db->prepare($query);
if ($stmt->execute(array(
    ':id' => $res_id,
    ':status' => $status
))) {
    //fetch results
}

?>

Update :

The result I want have to looks like :

    SELECT * FROM res_attachments WHERE res_id = '152' AND status = 'DEL'

You then simple use debugDumpParams() which dumps the information contained by a prepared statement directly on the output. It will provide the SQL query in use, the number of parameters used (Params),

<?php

$query = "SELECT * FROM res_attachments WHERE res_id = ? AND status = ?";
$data = array($res_id, $status);
$stmt = $db->prepare($query);
if($stmt->execute($data)){
   $stmt->debugDumpParams();
}
?>

edited Apr 10 '17 at 14:11

answered Apr 10 '17 at 13:58

Masivuye Cokile

4,754
3
19
34

This is not really what I want. I've updated my initial question, maybe it wasn't clear enough :/ – Nathan30 Apr 10 '17 at 14:01
I re update my initial question, because I can't modify the execution of the query, I just get the request and the parametesr in an array – Nathan30 Apr 10 '17 at 14:07
@Nathan30 updated my answer – Masivuye Cokile Apr 10 '17 at 14:11
Yes I see, thanks. But when I use the debugDumpParams() I have the number of arg, but not the value. And I can't use prepare and then execute. The request use query, I just have the string request and the array of parameters – Nathan30 Apr 10 '17 at 14:17

score 1 · Answer 2 · answered Apr 10 '17 at 13:53

1

If $db in your code snippet is an instance of PDO, then you could use debugDumpParams() to see the SQL

answered Apr 10 '17 at 13:53

libregeek

1,264
11
23

tushar · Answer 3 · 2017-04-10T13:59:33.857

1

I hope this will help you, try this

 $sth = $db->prepare('SELECT * FROM res_attachments WHERE res_id = ? AND status = ?');
    $sth->bindParam(1, $res_id, PDO::PARAM_INT);
    $sth->bindParam(2, $status, PDO::PARAM_STR);
    $sth->execute();

OR

$q = $db -> prepare('SELECT * FROM res_attachments WHERE res_id = ? AND status = ?');
$q->execute(array($res_id,$status));

edited Apr 10 '17 at 13:59

answered Apr 10 '17 at 13:54

tushar

161
1
2

This is not really what I want. I've updated my initial question, maybe it wasn't clear enough :/ – Nathan30 Apr 10 '17 at 14:00
WHAT ABOUT THIS? $q = $db -> prepare('SELECT * FROM res_attachments WHERE res_id = ? AND status = ?'); $q->execute(array($res_id,$status)); – tushar Apr 10 '17 at 14:01
I re update my initial question, because I can't modify the execution of the query, I just get the request and the parametesr in an array – Nathan30 Apr 10 '17 at 14:07

score 1 · Answer 4 · answered Apr 10 '17 at 14:38

1

Try this, approach I used preg_replace_callback:

$index = -1;
echo preg_replace_callback(
    '/\?/',
    function () use ($data, &$index) {
        $index++;
        return "'{$data[$index]}'";
    },
    $query);

answered Apr 10 '17 at 14:38

meda

45,103
14
92
122

Replace "?" with array data

Solution

4 Answers4