PHP foreach issue when separating values

Question

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

You're trying to loop a string, not the result-set. Besides, you need to fetch it. See how you do it in the first query (`while ($row = mysql_fetch_array($resultSet)) {`) — Qirel, Sep 23 '16 at 11:49
`mysql_*` functions are deprecated since PHP 5.5 (and **removed entirely** in PHP 7) and you should [stop using them](http://stackoverflow.com/q/12859942) if you can. You should choose another API that allows you to use [prepared statements](http://stackoverflow.com/q/60174/) (which you *really should* when dealing with user-input), like `mysqli_*` or PDO - see [choosing an API](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Qirel, Sep 23 '16 at 11:50
yeah, do $fetch= $resultSet ->fetch_assoc(); and than foreach ($fetch as $row) { echo $row['userEmail']; } — Ognj3n, Sep 23 '16 at 11:50
My mistake i did have it mysql_fetch_all ill edit that back now — user3473873, Sep 23 '16 at 11:50
@Ognj3n That's OOP `mysqli_`, it won't work with `mysql_` functions. — Qirel, Sep 23 '16 at 11:51
@user3473873 As per your edit, you now don't even execute the query. Just look at how it's done in the first part of your code, and replicate that. :-) — Qirel, Sep 23 '16 at 11:53
@Qirel this only fetches one result now. I need it to send out to each email — user3473873, Sep 23 '16 at 12:00
That's because your other query is outside the first while-loop, `$manager` will only have the last value in your second query. You probably can make this a lot easier with a sub-query in your first query, and do everything in one loop. — Qirel, Sep 23 '16 at 12:08
@Qirel So basically foreach manager in clients whos endofmonthform is that date needs an email sent to each managers email which is stored in users. There is more than one manager whos endofmonthform is that date and they have different emails which both need to be sent to — user3473873, Sep 23 '16 at 12:09
@Qirel how would i go about your last comment? Move the bracket to end of the second query? — user3473873, Sep 23 '16 at 12:10

Rick van Lieshout · Accepted Answer · 2016-09-23T13:15:01.910

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

}

Looping through an sql resultset, and then looping through the matching records from another table, is not the way to go. Have you ever heard of `join` in sql? — Shadow, Sep 23 '16 at 12:16
Give an example @shadow ? Ive uploaded the full script i hope to run. Ive used an smtp script which works and the code Rick van Lieshout has used does pull each of the emails. However doesnt solve my issue.... Please see http://pastebin.com/7PD2MyuC — user3473873, Sep 23 '16 at 12:46
@Shadow of course I have, I did not feel however that is was necessary to include it because there are quite a few mistakes already. — Rick van Lieshout, Sep 23 '16 at 13:16
@user3473873, What is not working? The original problem you proposed has been answered by my answer. — Rick van Lieshout, Sep 23 '16 at 13:19
@RickvanLieshout Yes correct and thank you for that. However if you see my pastebin im having trouble implicating it into the sendmail script i have. Im still pretty sure i need a foreach $opemail or $manager send email is needed pastebin.com/7PD2MyuC — user3473873, Sep 23 '16 at 13:28
You should accept an answer for this question and ask another question for the e-mail problem. The email problem has nothing to do with this question :) — Rick van Lieshout, Sep 23 '16 at 13:47
@RickvanLieshout http://stackoverflow.com/questions/39662791/php-send-email-foreach-user and ive sent you an email — user3473873, Sep 23 '16 at 14:06

PHP foreach issue when separating values

1 Answers1