Using MySQL variables with PDO Select statements

Question

I have a series of MySql queries that need to be called with PDO. Many of them are using MySql variables. Surely, a solution would be to use PDO prepared statements but that's not always the case. Insert and Update statements are running with no problems but Select is throwing an error. (I'm unable to paste it for you right now. Please say if it's necessary)

Select queries are running fine when executed in MySQL Workbench.

SET 
    @CY = YEAR(DATE_ADD(CURRENT_DATE,INTERVAL -17 DAY));

SELECT
   *
FROM
    TableX
WHERE
    TableX.Year IN (@CY, @CY-1)

Any ideas?

Additional info: PHP 5.3.29 and MYSQL 5.5.47

What error is the SELECT throwing?

Jay Blanchard
– Jay Blanchard

2016-02-01 15:52:05 +00:00
Commented Feb 1, 2016 at 15:52 — Jay Blanchard
– Jay Blanchard, Commented Feb 1, 2016 at 15:52

Nick · Accepted Answer · 2016-02-01 18:50:02Z

2

You may execute similar queries with two steps:

$stmt = $pdo->query('SET @CY = YEAR(DATE_ADD(CURRENT_DATE,INTERVAL -17 DAY));');
$stmt = $pdo->query('SELECT @CY, @CY-1');
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($data);

Outputs:

Array
(
    [0] => Array
        (
            [@CY] => 2016
            [@CY-1] => 2015
        )

)

answered Feb 1, 2016 at 18:50

Nick

10.2k8 gold badges65 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

Using MySQL variables with PDO Select statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related