Very strange behavior

[lush_rainforest]

So I'm trying to get my posts to show for a specific person using separate tables. Here is how my table structure looks like.

 

For posts_member it looks like

+----+--------+---------+
| id | userid | post_id |
+----+--------+---------+
| 1  |   1    |    1    |
+----+--------+---------+

For posts it looks like

+----+-------------+------------------------------+
| id | title       | description                  |
+----+-------------+------------------------------+
| 1  | First Post! | I just posted my first post! |
+----+-------------+------------------------------+

The very strange behavior starts when I try to retreive the data. I'll explain what I mean.

 

Here is my code first.

$sql = "SELECT post_id FROM post_members WHERE userid = :userid";
$prepare = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$parameters = array(':userid' => $userid);
$prepare->execute($parameters);

if($prepare->rowCount()) {

	while($row = $prepare->fetch(PDO::FETCH_ASSOC)) {

		$post_id = $row['post_id'];

		$sql = "SELECT id, title, description FROM posts WHERE id = :id";
		$stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
		$params = array(':id' => $post_id);
		$stmt->execute($params);

		if($stmt->rowCount()) {

			return $stmt->fetchAll();

		} else {

			return false;

		}

	}

} else {

	return false;

}

In this code, it looks ok. It looks like it'll return what I am expecting which is a bunch of records. However, that is not the case. It returns 1 row when I have 5 rows. But when I do this.

$sql = "SELECT post_id FROM post_members WHERE userid = :userid";
$prepare = $this->db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$parameters = array(':userid' => $userid);
$prepare->execute($parameters);

if($prepare->rowCount()) {

	while($row = $prepare->fetch(PDO::FETCH_ASSOC)) {

		$post_id = $row['post_id'];

		$sql = "SELECT id, title, description FROM posts WHERE id = :id";
		$stmt = $this->db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
		$params = array(':id' => $post_id);
		$stmt->execute($params);

		if($stmt->rowCount()) {

			print_r($stmt->fetchAll());

		} else {

			return false;

		}

	}

} else {

	return false;

}

It returns all 5 records. What the heck is going on? On the page I am retreiving the records on, I'm using foreach loop which should return what I am expecting, but it doesn't. Can anyone explain to me why this is happening? I suspect the FETCH_ASSOC part, but then again. When I use print_r on the $stmt->fetchAll();. it returned all data so I don't think that's the case.

[Barand]

Your second code doesn't "return" the rows it just prints the array containing the rows.

 

Is that code inside a function? If not you shouldn't be using return. If it is, how are you processing the results returned by the first code.

 

You should not be running queries inside a loop, you should use a JOIN to get all the results in a single query call, like this

SELECT p.id
, p.title
, p.description 
FROM posts as p
    JOIN post_members as pm ON p.id = pm.post_id
WHERE pm.userid = :userid

[lush_rainforest]

 

Your second code doesn't "return" the rows it just prints the array containing the rows.

Well, I was trying to debug it and see why it wasn't doing what I wanted it to.

 

Is that code inside a function? If not you shouldn't be using return. If it is, how are you processing the results returned by the first code.

Yes, these are all in functions so that's the reason why I am returning them so I can retreive them from a different page. I did it the same exact way I did it with the second code.

I called for the function using OOP and I assign that call with a variable, I check to make sure that result doesn't return false. If it doesn't, then I loop the data in a foreach loop. No breaks and no dies. So I don't understand why it only displayed 1 result while the second code displayed all 5 results when they are basically almost the same exact code, except I use print_r instead of returning it in the second code.

 

 

 

You should not be running queries inside a loop, you should use a JOIN to get all the results in a single query call, like this

SELECT p.id
, p.title
, p.description 
FROM posts as p
    JOIN post_members as pm ON p.id = pm.post_id
WHERE pm.userid = :userid

Thank you. Your suggestion worked like a charm. I appricate it.