user3473873 user3473873 - 2 months ago 5
MySQL Question

PHP foreach issue when separating values

I am basically trying to create where the end of the month form date = todays date but in this case 22/09/2016 where opmanager is stored in users get that email and foreach opmanager that has the end of the month date as 22/09/2016 send email. The email script i've done is ready to be implemented into the echo

However where the db fetchs two different opmanagers only one manager is getting the email and isn't been sent to foreach opmanager.

The code below:

<?php

include ("../dbconnect.php");

$sql='SELECT * FROM `clients` WHERE endofmonthform="22/09/2016"'; //TODAYS DATE BACK HERE!

$result=mysql_query($sql);

while($row=mysql_fetch_array($result)){

$enddate = $row['endofmonthform']; // End

$startdate = $row['startofmonthform']; // Start

$email = $row['email']; //Email to send email to

$id = $row['id'];

$formlevel = $row['formlevel']; //To update and check formlevel

$sitegroupname = $row['mastersite'];

$manager = $row['opmanager'];

}

$query="SELECT userEmail FROM `users` WHERE userName='".$manager."'";
$resultSet = mysql_fetch_all($query);
foreach ($query as $row) {
echo $row['userEmail']; //Where send email function will be
}
?>


I need it to send an email to each manager

Answer

Apart from the coding mistakes, the reason it's only sending the email to a single manager is because the manager bit only gets executed once. Your 2nd query:

SELECT userEmail FROM `users` WHERE userName='".$manager."'

will return a set of users where the username is equal to the LAST $manager from the previous loop because this bit of code is NOT inside the first loop.

Simply moving this bit of code inside the loop will fix your initial issue. The rest of the fixes can be found below:

include ("../dbconnect.php");

$sql='SELECT * FROM `clients` WHERE endofmonthform="22/09/2016"'; //TODAYS DATE BACK HERE!

$result=mysql_query($sql); 

 while($row=mysql_fetch_array($result)){  

    $enddate = $row['endofmonthform']; // End

    $startdate = $row['startofmonthform']; // Start

    $email = $row['email']; //Email to send email to

    $id = $row['id'];

    $formlevel = $row['formlevel']; //To update and check formlevel

    $sitegroupname = $row['mastersite'];

    $manager = $row['opmanager'];


    $query="SELECT userEmail FROM `users` WHERE userName='".$manager."'";
    $result=mysql_query($query); 

    while($row=mysql_fetch_array($result)){  
        echo $row['userEmail']; //Where send email function will be
    }

}

That said, this is still bad code.... It uses a deprecated API (mysql_) and it's vulnerable to SQL injection. Take a look at mysqli or pdo for a better API and prepared statements for the SQL injection issue.

If you want to easily use a database with php you could have a look my pdoWrapper: https://github.com/Mastermindzh/pdoWrapper

Edit:

As pointed out in the comments one could, really should, use a join to get this data. This might be a little much for the TS/OP to understand at this point but I will add it anyways for the sake of completeness:

include ("../dbconnect.php");

$sql='SELECT c.endofmonthform, c.startofmonthform, c.email, c.id, c.formlevel, c.mastersite, c.opmanager, u.userEmail FROM `clients` as c LEFT JOIN `users` as u on c.opmanager = u.userName WHERE endofmonthform="22/09/2016"'; //TODAYS DATE BACK HERE!

$result=mysql_query($sql); 

 while($row=mysql_fetch_array($result)){  

    $enddate = $row['endofmonthform']; // End

    $startdate = $row['startofmonthform']; // Start

    $email = $row['email']; //Email to send email to

    $id = $row['id'];

    $formlevel = $row['formlevel']; //To update and check formlevel

    $sitegroupname = $row['mastersite'];

    $manager = $row['opmanager'];

    echo $row['userEmail']; //Where send email function will be

}
Comments