While loop within if statement that is within a foreach loop... not working well

Question

I hope I can explain this well... I have an array called $departments_ids = array (1, 2, 3).

for each department within the array I need to display a list of comments stored in the database. I came up with something like this:

foreach ($departments_ids as $department_id) {

$query = "SELECT page_posts.post_id, page_posts.post, page_posts.post_date_time, 
users.profile_type_id, users.first_name, users.last_name, users.picture ".
"FROM page_posts ".
"INNER JOIN users ".
"USING (user_id) ".
"WHERE dep_id = $department_id ".
"ORDER BY post_date_time ASC";

$data = mysqli_query($dbc, $query);

     if(mysqli_num_rows($data) != 0) {
         while ($row = mysqli_fetch_array($data)){
         Here goes the code to print each comment 
         }
     }
     else {
        <p> Sorry, there are no comments yet. Don\'t  
        be shy, be the first one to post!</p>
        }
}

(Note: I ommitted part of the code to simplify the explanation)

The code works well, the problem is that it IS ONLY PRINTING THE FIRST COMMENT FOR EACH DEPARTMENT instead of all the comments stored in the database. It's like the code is read only once for each department and then it moves to the next department. If I understand this well I believe that once it hits the if statement and sees that there is more than one comment it should continue reading the while statement, and the while statement should take care of all the comments, but for some reason it is not working. I read about people with similar problems, but still can't figure out why it isn't working.

You used a join. Where are the command for defining how to join those table? — Marco Mura
– Marco Mura, Commented Jan 15, 2015 at 16:55
Here goes the code to print each comment - can't you show this code as well? — raina77ow
– raina77ow, Commented Jan 15, 2015 at 16:55
echo mysqli_num_rows($data) for each iteration to make sure you're getting what you expect. You also need to add some error checking. — Jay Blanchard
– Jay Blanchard, Commented Jan 15, 2015 at 16:55
you should try moving that query out of the loop. queries within loops are bad. Avoid them when possible — Get Off My Lawn
– Get Off My Lawn, Commented Jan 15, 2015 at 17:00

Alex · Accepted Answer · 2015-01-16 16:41:09Z

1

You could try this :

$query = "SELECT page_posts.post_id, page_posts.post, page_posts.post_date_time, users.profile_type_id, users.first_name, users.last_name, users.picture ";
$query .= "FROM page_posts INNER JOIN users USING (user_id) WHERE 1=1 AND";
$query .= "(";
foreach ($departments_ids as $department_id) {
    $query .= " dep_id=$department_id OR ";
}
$query .= ")";
$query .= " ORDER BY post_date_time ASC";

$data = mysqli_query($dbc, $query);
if(mysqli_num_rows($data) > 0) {
    while ($row = mysqli_fetch_array($data)){
        /*** Here goes the code to print each comment  */
    }
} else {
    echo "<p> Sorry, there are no comments yet. Don\'t be shy, be the first one to post!</p>";
}

Edit :

$query .= " dep_id=$department_id OR ";

Is either 1 or 2 or 3.

edited Jan 16, 2015 at 16:41

answered Jan 16, 2015 at 16:09

Alex

6267 silver badges16 bronze badges

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

Comments

Rookie · Accepted Answer · 2015-01-19 14:27:06Z

0

I finally figured out the problem... it was a rookie mistake:

I had 2 $query that were conflicting with each other... I had 1 query to look up comments and then another query to look up the responses to those queries... when I removed the second query just to test the code the code I provided in my question worked just fine; therefore,I changed the second query to $query2... problem solved.

Thanks for all your help!

answered Jan 19, 2015 at 14:27

Rookie

95910 silver badges29 bronze badges

Collectives™ on Stack Overflow

While loop within if statement that is within a foreach loop... not working well

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related