CODEWITHSUNDEEP
phpmysqlsql
cantaffordavan
cantaffordavan

Reputation: 1447

FOREACH and UPDATE mysql not working together

I can't seem to get this code to work. Can you run another query using the foreach rows? This is a cron script that will be sending out emails and I need to update the status from '0' to '1' when the cron runs so I know the emails were sent - the code UPDATE query below does not work.

// Connect to the database
$conn = new PDO("mysql:host=$DB_HOST;dbname=$DB_DATABASE",$DB_USER,$DB_PASSWORD);

// Get the emails to send and ensure the status shows they have not been sent already
$stmt = $conn->prepare("SELECT id, userid, title, message FROM reminders WHERE time=CURDATE() AND status='0'");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($results AS $row){
echo $row['title'] . ' ' . $row['message'] . ' ' . $row['id'] . '<br />';
$update = $conn->prepare("UPDATE results SET status='1' WHERE id=:id");
$update->bindParam(':id', $row['id']);
$update->execute();
};

EDIT: The table name was wrong. Solved

Upvotes: 1

Views: 137

Answers (3)

Kai Mattern
Kai Mattern

Reputation: 3085

Just to have the correct solution in the question - answer format:

You are using the result variable resuls and then try to update the table results. Is the table you want to update really named "results" or has this information creeped in from the name of the variable?

From the query it look like you are working on the table reminders.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

how about specifying the datatype of the value (I think [not sure] the default data type is string and the server automatically parses it)? PDO::PARAM_INT

$update->bindValue(':id', $row['id'], PDO::PARAM_INT);

PDO Predefine Constants

Upvotes: 4

Mihai Iorga
Mihai Iorga

Reputation: 39704

Maybe you need to bindValue and specify the datatype:

$update = $conn->prepare("UPDATE results SET status=1 WHERE id=:id");
$update->bindValue(':id', $row['id'], PDO::PARAM_INT);
$update->execute();

Upvotes: 2

Related Questions