0

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);
Improve this question
8
  • Read more 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. Commented Apr 10, 2017 at 13:50
  • 1
    Your question isn't clear. Please edit and explain what you mean by "without the "?"" -- IOW, do you mean without the WHERE clause? Commented Apr 10, 2017 at 13:51
  • @SloanThrasher I've edited my question :) Commented Apr 10, 2017 at 13:57
  • 2
    I don't think the people who gave answers (so far) understood this question fully. Commented Apr 10, 2017 at 14:00
  • Yes @Fred-ii- , I've update my inital question to be as clear as I could Commented Apr 10, 2017 at 14:02

4 Answers 4

Reset to default
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();
}
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is not really what I want. I've updated my initial question, maybe it wasn't clear enough :/
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 updated my answer
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
1

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

Improve this answer

Comments

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));
Improve this answer

3 Comments

This is not really what I want. I've updated my initial question, maybe it wasn't clear enough :/
WHAT ABOUT THIS? $q = $db -> prepare('SELECT * FROM res_attachments WHERE res_id = ? AND status = ?'); $q->execute(array($res_id,$status));
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
1

Try this, approach I used preg_replace_callback:

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.