Echoing every mysql result, not just the one...

[wright67uk]

I'm having a few troubles. 

 

When I run the first query below in phpmyadmin, i receive six results.

 

However when I run the code below $info_msg is only echoed once, whereas I was expecting it to be echoed once for every result. (six times)

 

Where am I going wrong and what should I change?

<?php
include 'connect.php';

$date = date('Ymd'); 
$query= "SELECT races.date, races.name, races.odds, races.race, actions.action, actions.user_id 
         FROM races RIGHT JOIN actions ON races.name = actions.name WHERE races.date = $date" ;
		 
                if(!$result = $mysqli->query($query)){
                die('There was an error running the query [' . $mysqli->error . ']');
				                                     }
													 
                 while($row = $result->fetch_assoc())
                        {
                        $horse_name = $row['name']; 
						$user_id = $row['user_id']; 
						$race = $row['race'];
                        $query = "SELECT user_email FROM phpbb_users WHERE user_id = $user_id" ;
                        } 
						 
				if(!$result = $mysqli->query($query))
					{
                    die('There was an error running the query [' . $mysqli->error . ']');
					}
					
                    while($row = $result->fetch_assoc()){
		
         $user_email = $row['user_email'];
	echo $info_msg = "just to let you know a horse you are following is racing soon! 
        <br><b> " . $horse_name . " </b> will be racing in the " . $race;
 													$to      = $user_email;
 	$subject = 'A reminder from TNT';
 	$message = $info_msg;
 	$headers = 'From: reminder@reminder.com' . "\r\n" .
   		   'Reply-To: reminder@reminder.com' . "\r\n" .
  		   'X-Mailer: PHP/' . phpversion();
		   mail($to, $subject, $message, $headers);         }
                    

?>

[davidannis]

Put the echo $info_msg inside the while loop.

Take this line out of the while loop

                        $query = "SELECT user_email FROM phpbb_users WHERE user_id = $user_id" ;

You only need to get the e-mail address once, not once per horse.

Edited May 6, 2013 by davidannis

[wright67uk]

Thankyou for the reply.

 

$info_msg is already in a while loop.

 

User_id changes in each row, wouldn't this have to be a while loop to work?

[jcbones]

You change your result resource when you ask for the email address.  Then that runs to its end in the while loop, there is nothing else from the resource ID.  This is because you bound the second resource id to the same variable name as the first resource id, overwriting your $result variable.  This is one of the roadblocks of running queries inside of query loops.  You should join the phpbb table to your other query, so everything is pulled at the same time.

[davidannis]

 

 

 

Thankyou for the reply.

 

$info_msg is already in a while loop.

 

User_id changes in each row, wouldn't this have to be a while loop to work?

If you want $info_msg to echo for each horse, it needs to be in the loop that reads the horse records

It is below that loop in a while loop that should only execute once as you've written the code.

I have fixed that problem in the code below.

<?php
include 'connect.php';

$date = date('Ymd');
$query = "SELECT races.date, races.name, races.odds, races.race, actions.action, actions.user_id
    FROM races RIGHT JOIN actions ON races.name = actions.name WHERE races.date = $date";
if (!$result = $mysqli->query($query)) {
    die('There was an error running the query [' . $mysqli->error . ']');
}
while ($row = $result->fetch_assoc()) {
    $horse_name = $row['name'];
    $user_id = $row['user_id'];
    $race = $row['race'];
        echo $info_msg = "just to let you know a horse you are following is racing soon!
    <br><b> " . $horse_name . " </b> will be racing in the " . $race;
        $full_message.=$info_msg; // build the whole list by adding each row to it
} // end of the while loop that echo $info_msg belongs in 
    $query = "SELECT user_email FROM phpbb_users WHERE user_id = $user_id";
if (!$result = $mysqli->query($query)) {
    die('There was an error running the query [' . $mysqli->error . ']');
}
while ($row = $result->fetch_assoc()) { // This while will only execute once since any given user_id will only have one record.
    $user_email = $row['user_email'];

    $to = $user_email;
    $subject = 'A reminder from TNT';
    $message = $full_message;
    $headers = 'From: reminder@reminder.com' . "\r\n" .
            'Reply-To: reminder@reminder.com' . "\r\n" .
            'X-Mailer: PHP/' . phpversion();
    mail($to, $subject, $message, $headers);
}
?>

But, the way you have it written the routine will only mail to the last $user_id email.

 

You need to loop through the users and for each user build a list of horses that user is following then e-mail the list and go on to the next user. I leave that fix to you for now. If you have trouble come back again.