在mysql字段中发送值为1的电子邮件用户

Question

I have a php file that is executed every night at midnight. It queries mysql for all user in a table if they have a value of "1" in the alerts field and it returns their email addresses. I am trying to get this file to only email those users. currently i have it working by emailing every single email address in the database $to = $row['email']; but I cant get it to only send emails to the select users who have signed up for alerts.

<?

$con=mysqli_connect("localhost","root","","DB1");
// Check connection
if (mysqli_connect_errno()) {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM users");



##email users their account balances


$sql = mysqli_query("SELECT * FROM users WHERE alerts = '1'");
$recipients = array();
while($row = mysqli_fetch_array($result)){ 

    $recipient[]=$row['email'];

    $to = $row['email'];
    $subject = "Your account has received a deposit.";
    $body = "Hello,

We have added 1 DOGE and .0000001 BTC to your account. Go to www.bitcoinchew.com/Profile to withdrawl. $
    if (mail($to, $subject, $body)) {
        echo("<p>Email successfully sent!</p>");
    } else {
        echo("<p>Email delivery failed…</p>");
    }
  }
?>

Answer 1

Ok, here. Firstly, you're calling the wrong variable $result for your query and using mysqli_query() twice.

$result = mysqli_query($con,"SELECT * FROM users");

and

while($row = mysqli_fetch_array($result))

Get rid of $result = mysqli_query($con,"SELECT * FROM users");

and use $sql = mysqli_query($con,"SELECT * FROM users WHERE alerts = '1'");

then using while($row = mysqli_fetch_array($sql)){ instead.

You're also missing a From: in your mail() headers, which would most likely end up in Spam or be "marked" as Spam. The From would show up as your server, rather than an Email address.

$from = "[e-mail]";

then add $headers, in if (mail($to, $subject, $body)) being

if (mail($to, $subject, $body, $headers))

You also had a missing quote and semi-colon at the end of to withdrawl. $ which would have resulted in a parse error.

to withdrawl. $";

---

Here's a working rewrite:

<?php

$con = mysqli_connect("localhost","root","","DB1");

    if (mysqli_connect_errno()) {
     echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

$sql = mysqli_query($con,"SELECT * FROM users WHERE alerts = '1'");

$recipients = array();

while($row = mysqli_fetch_array($sql)){ 

    $recipient[]=$row['email'];

    $from = "[e-mail]"; // <= change it to your own

    $to = $row['email'];
    $subject = "Your account has received a deposit.";

    $body = "Hello,

We have added 1 DOGE and .0000001 BTC to your account. Go to www.bitcoinchew.com/Profile to withdrawl. $";

    $headers = "From: $from";


    if (mail($to, $subject, $body, $headers)) {
        echo("<p>Email successfully sent!</p>");
    } else {
        echo("<p>Email delivery failed…</p>");
    }
}

---

Footnotes:

$recipients = array(); is not required. You can just need to remove the brackets $recipient=$row['email'];

Both methods will work either way, it's just more keystrokes for nothing.

---

Personalization:

If you have a row for people's names, you can personalize it with:

$name=$row['name']; add it above $recipient=$row['email'];

then doing $body = "Hello $name, We have added...