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
– Nir Alfasi, Commented Oct 12, 2012 at 23:26
I'm not sure that you can bind a placeholder to an array like that. See this question: stackoverflow.com/questions/920353/… — Barmar
– Barmar, Commented Oct 12, 2012 at 23:31
It's not an array though, it's just numbers seperated by commas. — NaughtySquid
– NaughtySquid, Commented Oct 12, 2012 at 23:34
but you're binding multiple values, maybe only the last one sticks, I'm not sure. — vascowhite
– vascowhite, Commented Oct 12, 2012 at 23:37

vascowhite · Accepted Answer · 2012-10-12 23:05:44Z

1

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

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

answered Oct 12, 2012 at 23:05

vascowhite

18.5k9 gold badges64 silver badges79 bronze badges

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

7 Comments

NaughtySquid Over a year ago

The problem is it works elsewhere in my script, since it's in a loop shouldn't it keep going?

vascowhite Over a year ago

You don't show where you call fetch(), but its the usual mistake. Could you show that code?

vascowhite Over a year ago

Why does everything except fetch() in the while loop refer to $this->STH?

vascowhite Over a year ago

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 Over a year ago

In your fetch() function do a var_dump($this->STH->fetchAll()) and put the result into your question please. It could be that @barmar is right. If so, I will delete this answer and he can post his own.

|

Collectives™ on Stack Overflow

pdo mysql with php loop only returning 1 row

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related