CODEWITHSUNDEEP
phpmysqlpdo
Bramastic
Bramastic

Reputation: 399

Using MySQL variables with PDO Select statements

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

Upvotes: 0

Views: 57

Answers (1)

Nick
Nick

Reputation: 10163

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
        )

)

Upvotes: 2

Related Questions