PHP - Use mysql_fetch_assoc() multiple times in the loop.

Question

I'm working on a really simple email notification system for newly added comments. It works like this:

loop through project users
for each user, loop through all comments written in the last hour
if there are any comments that are not written by the user, push them to an array
if comment array is not empty, send an email to the user

Here's some of the code:

<?php
$users_query = mysql_query("SELECT * FROM users");

$comments_query = mysql_query("SELECT * FROM comments c, users u WHERE c.date > DATE_SUB(NOW(), INTERVAL 1 HOUR) and c.user_id = u.id");

while ($user = mysql_fetch_assoc($users_query)) {
   $commentsArr = array();  

   while ($comment = mysql_fetch_assoc($comments_query)) {
      if ($comment['email'] != $user['email']) {
         array_push($commentsArr, $comment['comment']);
      }
   }

   // send $commentsArr by email to $user['email']
}
?>

The problem is that the same mysql_fetch_assoc cannot be used multiple times on a single page. I tried reseting the pointer using mysql_data_seek($comments_query, 0); but that just threw the following error:

Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 0 is invalid for MySQL result index 4 (or the query data is unbuffered)

Are there any other ways of handling this? All other people with similar questions were advised to join their tables instead of splitting them up. I don't think I can do that since I have to go through all users first, so if I join them, I'll have more rows than users.

Anyway, is there a way to loop through the comments while inside a loop?

you shouldn't use mysql_ as it is depreciated, use mysqli_ instead or PDO — Enjoyted
– Enjoyted, Commented May 15, 2014 at 11:42

Avikarsha Saha · Accepted Answer · 2014-05-15 15:14:37Z

2

<?php
$users_query = mysql_query("SELECT * FROM users");
$users = mysql_fetch_array($users_query);

$comments_query = mysql_query("SELECT * FROM comments c, users u WHERE c.date >    DATE_SUB(NOW(), INTERVAL 1 HOUR) and c.user_id = u.id");

$comments = mysql_fetch_array($comments_query)

foreach($user as $users) {
  $commentsArr = array();  

  foreach($comment as $comments) {
    if ($comment['email'] != $user['email']) {
     array_push($commentsArr, $comment['comment']);
    }
  }

  // send $commentsArr by email to $user['email']
 }
?>

edited May 15, 2014 at 15:14

answered May 15, 2014 at 13:50

Avikarsha Saha

1331 silver badge7 bronze badges

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

Comments

Ted B. · Accepted Answer · 2014-05-15 13:12:37Z

1

I switched from mysql to mysqli and now mysqli_data_seek($comments_query, 0); works as expected.

answered May 15, 2014 at 13:12

Ted B.

659 bronze badges

Comments

Ramki · Accepted Answer · 2014-05-15 12:00:22Z

0

Use $commentsArr array in outside of the while loop:

 <?php
    $users_query = mysql_query("SELECT * FROM users");

    $comments_query = mysql_query("SELECT * FROM comments c, users u WHERE c.date > DATE_SUB(NOW(), INTERVAL 1 HOUR) and c.user_id = u.id");
    $commentsArr = array();  
    while ($user = mysql_fetch_assoc($users_query)) {


       while ($comment = mysql_fetch_assoc($comments_query)) {
          if ($comment['email'] != $user['email']) {
             array_push($commentsArr, $comment['comment']);
          }
       }

       // send $commentsArr by email to $user['email']
    }
    ?>

answered May 15, 2014 at 12:00

Ramki

5192 silver badges9 bronze badges

2 Comments

Ted B. Over a year ago

That won't make a difference, as the problem is with mysql_fetch_assoc being called multiple times.

Ramki Over a year ago

if you calling inside the while loop means it shows an empty array.

Collectives™ on Stack Overflow

PHP - Use mysql_fetch_assoc() multiple times in the loop.

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related