I've created a PHP script that is triggered by a weekly CronJob. If the date of last activity is longer than 2 weeks ago, a field will go red and there will be sent an email to one specific person once the CronJob triggers.
I can get the data that needs to be mailed and it already does. Now I only need to update the field mailed to 1.
I tried the SQL query in phpMyAdmin:
UPDATE klanten_potentioneel SET mailed = 1 WHERE naamklant = '$naam'
Where $naam is obviously changed into an existing name. This query works.
<?php
$conn = new mysqli("localhost", "xxx", "xxx", "xxx");
$sql = "SELECT * FROM table WHERE mailed IS NULL GROUP BY naamklant ORDER BY id DESC ;";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
if (strtotime($row["datumlastvisit"]) < strtotime('-14 days')) {
$mailNeeded = '1 ';
//hier de code van mailen
$to = '[email protected]';
$subject = 'geen actie potentieel';
$message = "Er is nog geen actie ondernomen bij $row[naamklant] sinds $row[datumlastvisit] door $row[naam].";
$headers = 'From: [email protected]\r\n';
// Sending email
if(mail($to, $subject, $message,$headers)){
}
$naam = $row["naamklant"];
$sql = "UPDATE klanten_potentioneel SET mailed = 1 WHERE naamklant = '$naam'";
}
else if (strtotime($row["datumlastvisit"]) < strtotime('-7 days')) {
$mailNeeded = '0';
} else {
$mailNeeded = '0';
}
//to test if the code is working
echo $row["naamklant"];
echo " - ";
echo $mailNeeded;
echo "<br>";
}
}
else {
echo "There is nothing to be mailed.";
}
?>
The record in the database should be changing, but it does not.
Once you've completed query building you can execute it and get result of it.
In this case you didn't do that after the UPDATE query.
Add
$conn->query($sql);
after
$sql = "UPDATE klanten_potentioneel SET mailed = 1 WHERE naamklant = '$naam'";