pdo mysql with php loop only returning 1 row

Question

UPDATE 2 - I managed to do it by using a subquery, rather than having a $config var with each number i added a row to my articles_category table to have 1 or 0, if it has 1 include it like so:

    $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 (
(

SELECT `category_id`
FROM `articles_categorys`
WHERE `show_in_rss` =1
)
)
ORDER BY a.`date` DESC
LIMIT ?", array($limit));

UPDATE1 - I have a partial solution i need help with the second bit (i don't even know if this is going the right way about it but it's driving me nuts).

If i do this;

    $category_array = explode(',', $config['article_rss_categorys']);

$in_sql = '';
$counter = 0;
// count how many there are
foreach($category_array as $cat)
{


    if ($counter == 0)
    {
        $in_sql .= '?';
    }

    else
    {
        $in_sql .= ',?';
    }

    $counter++;
}

I can put "$in_sql" inside the IN () to give me the individual ? and it works. Now i need to find a way to get each number from inside $config['article_rss_categorys'] into the second part of the query?

ORIGINAL;

$db is my database class/connection (all the functions are in this class).

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 (inside the mysql class called by $db);

    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, (int)$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 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 (inside the mysql class called by $db);

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

It only returns one row, the last one. It is supposed to be looping through them all up to a max of 15.

I don't get why it's only getting one?

Did you try running the query manually - just to make sure it returns more than 1 result ? — Nir Alfasi, Oct 12 '12 at 23:26
I'm not sure that you can bind a placeholder to an array like that. See this question: http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — Barmar, Oct 12 '12 at 23:31
It's not an array though, it's just numbers seperated by commas. — NaughtySquid, Oct 12 '12 at 23:34
but you're binding multiple values, maybe only the last one sticks, I'm not sure. — vascowhite, Oct 12 '12 at 23:37
Hmm why does it get counted as multiple values? Because of the commas? — NaughtySquid, Oct 12 '12 at 23:38
actually ignore that I'm most of the way through a bottle of wine and not thinking straight. I'll come back to this tomorrow if you don't have a solution. :) — vascowhite, Oct 12 '12 at 23:39
Okay thanks i appreciate all the help i have this page bookmarked. — NaughtySquid, Oct 12 '12 at 23:41
`array($config['article_rss_categorys'])` is an array. Maybe you just want `$config['article_rss_categorys']` — Barmar, Oct 12 '12 at 23:45
No it needs to be an array as the query code sorts through it. The $config var isn't an array itself though. — NaughtySquid, Oct 12 '12 at 23:50
Please see my update at the top, partial soluton, just need the second bit. — NaughtySquid, Oct 12 '12 at 23:57

score 1 · Answer 1 · answered Oct 12 '12 at 23:05

1

PDOStatement::fetch() only returns one row, you need PDOStatement::fetchAll().

See http://www.php.net/pdo

answered Oct 12 '12 at 23:05

vascowhite

18,120
9
61
77

The problem is it works elsewhere in my script, since it's in a loop shouldn't it keep going? – NaughtySquid Oct 12 '12 at 23:06
You don't show where you call fetch(), but its the usual mistake. Could you show that code? – vascowhite Oct 12 '12 at 23:11
Why does everything except fetch() in the while loop refer to $this->STH? – vascowhite Oct 12 '12 at 23:20
Also you are returning the last row with `return $this->STH->fetch();` in the last snippet. That's the one you describe as your 'fetch code'. – vascowhite Oct 12 '12 at 23:21
STH is because i followed this to learn PDO http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/, it's the statement. The while loop and query are outside of the class those functions are in. So i'm calling them in. The code snippets are not in order it being run, the fetch function is called in the while loop it should give me all the rows but it gives me the last one. I also tried it with fetch all, same result, the last one. So i think the query is the problem. – NaughtySquid Oct 12 '12 at 23:23
1

In your fetch() function do a var_dump($this->STH->fetchAll()) and put the result into your question please. It could be that [@barmar](http://stackoverflow.com/questions/12868229/pdo-mysql-with-php-loop-only-returning-1-row/12868274#comment17419660_12868229) is right. If so, I will delete this answer and he can post his own. – vascowhite Oct 12 '12 at 23:31
Please see my update at the top, partial soluton, just need the second bit. – NaughtySquid Oct 12 '12 at 23:57

pdo mysql with php loop only returning 1 row

1 Answers1