How can I retrieve a MySql statement as a string after binding?

Question

I am trying to debug a SQL statement I have not written myself:

    $stmt = $dbh->prepare("
        SELECT 
            Customer.Primary_Email AS EMAIL,
            ...
        FROM Order_SG
            INNER JOIN Customer USING (Customer__)
            INNER JOIN Order_SG_Detail USING (Order_SG__)
            INNER JOIN Product_Ref USING (Product_Ref__)
            INNER JOIN Reduction USING (Reduction__)
        WHERE Order_SG.Server__ IN (:servers)
            ...
            AND `DATE` BETWEEN :startDate AND :endDate
            LIMIT :limit
            OFFSET :offset");

    $stmt->bindValue(':servers', implode(',', $servers));
    $stmt->bindValue(':startDate', $startDate);
    $stmt->bindValue(':endDate', $endDate);
    $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);

How can I retrieve the statement after the binding? A string I could echo is fine.

The `in` statement won't work. The `in` list consists of a single string value with commas in it. — Gordon Linoff, Feb 10 '16 at 12:45
Parameter binding doesn't work like copy and paste. Especially when using database native prepared statements, the values aren't ever actually "put into the statement". The statement is still the statement as written and the values are still separate from it. — deceze, Feb 10 '16 at 12:48

score 1 · Accepted Answer · answered Feb 10 '16 at 12:46

The in statement will not work. One method is to include the list directly in the SQL (yucky, but that is the approach). Another is to use find_in_set():

WHERE find_in_set(Order_SG.Server__, :servers) > 0 AND
. . .

However, an index cannot be used for this function.

How can I retrieve a MySql statement as a string after binding?

1 Answers1