My pdo query seems to fail and I don't see why

Question

Possible Duplicate:
PHP PDO bindValue in LIMIT

Okay so here is my query:

$db->sqlquery("
    SELECT a.*, c.`category_name`
    FROM `articles` a LEFT JOIN `articles_categorys` c
    ON c.`category_id` = a.`category_id`
    WHERE a.`active` = 1
    AND a.`category_id` IN (?)
    ORDER BY a.`date`
    DESC LIMIT ?", array($config['article_rss_categorys'], $limit)
);

I check and the $config['article_rss_categorys'] is set and its 0,1,2,4,6,7, also $limit is set and it's 15.

Here is my query code

    try
    {
        $this->STH = $this->database->prepare($sql);

        foreach($objects as $k=>$p)
        {
            // +1 is needed as arrays start at 0 where as ? placeholders start at 1 in PDO
            if(is_numeric($p))
            {
                $this->STH->bindValue($k+1, $p, PDO::PARAM_INT);
            }
            else
            {
                $this->STH->bindValue($k+1, $p, PDO::PARAM_STR);
            }
        }

        return $this->STH->execute();

        $this->counter++;
    }

    catch (PDOException $e)
    {
        $core->message($e->getMessage());
    }

I don't see why it is failing, am I missing something stupid?

I tested the query in phpmyadmin replacing ? with the correct stuff and it does work so the database is fine.

I then try to fetch and put out the results like so;

while ($line = $db->fetch())
{
    // make date human readable
    $date = $core->format_date($line['date']);

    $output .= "
        <item>
            <title>{$line['category_name']} > {$line['title']}</title>
            <link>http://www.prxa.info/index.php?module=articles_comments&amp;aid={$line['article_id']}</link>
            <pubDate>{$date}</pubDate>
            <guid>http://www.prxa.info/index.php?module=articles_comments&amp;aid={$line['article_id']}</guid>
        </item>";
}

This is my fetch code:

public function fetch()
{
    $this->STH->setFetchMode(PDO::FETCH_ASSOC); 
    return $this->STH->fetch();
}

How do you mean failing? Does it not get the expected results, or does it throw an exception? If so, what exception? :) — Terry Seidler, Oct 12 '12 at 21:54
Sorry by failing i mean when i try to fetch the results, it's always blank. Also marabutt yes in my post i state i tried it in phpmyadmin. — NaughtySquid, Oct 12 '12 at 21:58
Have you tried a `var_dump($line)` in your while loop instead of trying to add it to a string right away? var_dump may give more information regarding `$line`. — Terry Seidler, Oct 12 '12 at 22:02
(though if it doesn't get in the while loop because the query does not return results then my comment is pretty much useless) — Terry Seidler, Oct 12 '12 at 22:06
the var dumps literally gives nothing at all. So i am thinking the query is the thing that is borked, any ideas? — NaughtySquid, Oct 12 '12 at 22:08
Shouldn't the order by line have a closing quote after date? — SteB, Oct 12 '12 at 22:13
Odd it does in the script but not the example i corrected it. — NaughtySquid, Oct 12 '12 at 22:15
I managed to find one issue $limit was a string not an int for some reason, but now i only get one row? — NaughtySquid, Oct 12 '12 at 22:20
Try to change `$this->STH->bindValue($k+1, $p, PDO::PARAM_INT);` to `$this->STH->bindValue($k+1, (int)$p, PDO::PARAM_INT);` (cast it as an int - should make a difference - [ESPECIALLY when using `LIMIT`](http://stackoverflow.com/a/2269931/247893)) — h2ooooooo, Oct 12 '12 at 22:30
Yes making it an int works, but now my query only returns one row. — NaughtySquid, Oct 12 '12 at 22:35

FtDRbwLXw6 · Answer 1 · 2012-10-13T03:23:46.710

As far as I know, a bind parameter can only represent a single value. You're trying to do this:

... WHERE foo IN (?) ...

And then bind an array of values (I assume), expecting the resulting query to look similar to:

... WHERE foo IN (1, 2, 3, ...) ...

That's not possible. You'll need a parameter for each value in the set of values that you're trying to use within the IN clause:

... WHERE foo IN (?, ?, ?, ...) ...

Edit:

To explain a little more on why you'd only be getting a single record back - you're binding non-numeric values as strings, so PHP will convert an array to a string with the value string(5) "Array". This string will then be passed to the database, and could be being cast to an integer (since that's what the database is expecting, and default settings on most databases will type coerce). It's possible/probable that this string is being cast to the integer 0, thus producing a query like:

... WHERE foo IN (0) ...

...and obviously leading to a difficult to track down bug.

+1 Correct, a parameter placeholder takes the place of a *single* scalar value. You can't bind a string containing list of values, that'll work like a single string that happens to contain commas. — Bill Karwin, Nov 02 '12 at 21:26

My pdo query seems to fail and I don't see why

1 Answers1