PDO call failing in PHP but successfully executes in MySQL Workbench

Question

I'm connecting to a MySQL database via PDO, but when I try to execute the SQL call in PHP it fails with the error below. When I echo out the SQL and run it in MySQL Workbench, it returns exactly what I'd expect it to. This is the output of $prepared->errorInfo() when it fails:

Array (
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "5" at line 17
)

So it looks like something is wrong with the prepared statement and passing values to it upon execute, but I'm not sure what. http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_parse_error doesn't really help other than telling me the same issue.

function __construct($params = array()) {

    $DB = new PDO('mysql:host='.$config['host'].';port='.$config['port'].';dbname='.$config['name'], $config['user'], $config['pswd']);
    if (!$DB) {
        throw new Exception('Could not connect to database');
    }

    $type = $params['topic'];

    if (isset($params['topic']) && $params['topic'] !== '') {
        $typeSlugSql = "SELECT id from topics WHERE slug = ? LIMIT 1";

        $prepared = $DB->prepare($topicSlugSql);
        $list = $prepared->execute(array($params['topic'])); //NOTE: this is working correctly and returns the ID I expect
        if (!$list) {
            throw new Exception('topic slug execute failed');
        }
        $row = $prepared->fetch(PDO::FETCH_ASSOC);

        $topic = $row['id']; //id is correctly 6 at this point
    }

    //generate SQL query
    $sql = "SELECT
                articles.id AS id,
                articles.slug AS slug,
                articles.title AS title,
                articles.headline AS headline,
                articles.description AS description,
                articles.keywords AS keywords,
                articles.content AS content,
                DATE(articles.published_date) AS date,
                articles.created AS created
            FROM
                articles articles
            WHERE
                articles.published_date <= CURRENT_TIMESTAMP
                AND (articles.published IS NOT NULL OR articles.published = 1)
            ORDER BY
                articles.published_date DESC LIMIT 0 , ?";

    //run the query and get the feed data
    $prepared = $DB->prepare($sql);
    $items = $prepared->execute(array(5)); //this will make the SQL fail...

    echo $sql;

    if (!$items) {
        print_r($DB->errorInfo()); //this prints the 'errorInfo' listed above
        throw new Exception('execution error...');
    }

    $options = array('ellipsis' => '...', 'exact' => false, 'html' => false);
    while ($row = $prepared->fetch(PDO::FETCH_ASSOC)) {
        array_push($this->resources, new Resource($row['title'], $row['slug'], $row['date'], $this->cleanAndTruncate($row['content'])));
    }

}

The weirdest part is that $prepared->execute(array($params['type'])) works correctly and returns the ID I'm expecting. Again, if I copy/paste the echo'd SQL and replace ? with 5 it works as expected in MySQL Workbench. If I do:

    $sql = "SELECT
                articles.id AS id,
                articles.slug AS slug,
                articles.title AS title,
                articles.headline AS headline,
                articles.description AS description,
                articles.keywords AS keywords,
                articles.content AS content,
                DATE(articles.published_date) AS date,
                articles.created AS created
            FROM
                articles articles
            WHERE
                articles.published_date <= CURRENT_TIMESTAMP
                AND (articles.published IS NOT NULL OR articles.published = 1)
            ORDER BY
                articles.published_date DESC LIMIT 0 , 5";

    $prepared = $DB->prepare($sql);
    $items = $prepared->execute();

It works in PDO and MySQL Workbench, but I need the LIMIT to change depending on parameters passed in. Any suggestions to figuring out what's breaking this?

That was just part of the syntax I was using with multiple tables so it was more clear, `FROM articles articles, articles_topics at, articles_something_else ase`, that doesn't affect the execution at all. — WOUNDEDStevenJones, Dec 18 '14 at 19:38
possible duplicate of [LIMIT keyword on MySQL with prepared statement](http://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement) — Anthony, Dec 18 '14 at 19:43
To "catch" errors, use a `try/catch` method: `try{...} catch(PDOException $e){ $e->getMessage(); for the real error }` and add `$DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. See the answer below. — Funk Forty Niner, Dec 18 '14 at 19:44
@Anthony yup! It turns out that was the reason for it not working, which is odd because we have other scripts on the same server working as coded in my question, but something must be configured slightly differently for this. Thanks! — WOUNDEDStevenJones, Dec 18 '14 at 21:04

Mike Purcell · Accepted Answer · 2014-12-18T20:08:45.303

0

Try this:

$sql = "SELECT
            articles.id AS id,
            articles.slug AS slug,
            articles.title AS title,
            articles.headline AS headline,
            articles.description AS description,
            articles.keywords AS keywords,
            articles.content AS content,
            DATE(articles.published_date) AS date,
            articles.created AS created
        FROM
            articles articles
        WHERE
            articles.published_date <= CURRENT_TIMESTAMP
            AND (articles.published IS NOT NULL OR articles.published = 1)
        ORDER BY
            articles.published_date DESC LIMIT :limit , 5";

$stmt = $DB->prepare($sql);

$limit = 5;

$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

$items = $stmt->execute();

edited Dec 18 '14 at 20:08

answered Dec 18 '14 at 19:39

Mike Purcell

19,847
10
52
89

`$this->numberOfResources = 5; $prepared = $DB->prepare($sql); $prepared->bindParam(':limit', $this->numberOfResources, PDO::PARAM_INT); $items = $prepared->execute();` still doesn't work – WOUNDEDStevenJones Dec 18 '14 at 19:44
Updated, try that... was giving me a pass by reference error when I hardcoded 5 as the 2nd param to `bindParam`. – Mike Purcell Dec 18 '14 at 20:09
Same, so I used `$this->numberOfResources` instead. I'm tinkering around with the `:` and `?` formats now. I'll let you know if this fixes things or not. – WOUNDEDStevenJones Dec 18 '14 at 20:17
Code as posted should work, I tested. Obviously with not the same schema, but the language constructs. Try just doing a `SELECT * FROM articles LIMIT :limit` to remove any other distractors. – Mike Purcell Dec 18 '14 at 20:22
@Anthony's comment was the cause, and this was the solution. Actually, I ended up using `$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);` and left my parameters as `?`, but it essentially gave me the same functionality. Thanks! – WOUNDEDStevenJones Dec 18 '14 at 21:05

PDO call failing in PHP but successfully executes in MySQL Workbench

1 Answers1