MYSQL update not working with php variable id

Question

I'm stuck on a simple MYSQL UPDATE statement; selecting an ID from the database, and then updating a record WHERE ID = $ID. Code:

$searchDates = ('SELECT * FROM booking_event WHERE subject != "Closed" AND is_reminded IS NULL AND DATE_ADD(NOW(), INTERVAL 2 DAY) >= `starting_date_time` AND NOW() <= `starting_date_time`'); 

$result = mysql_query($searchDates) or die (mysql_error());

while ($row = mysql_fetch_array($result)) {
  $id = $row['event_id'];

mysql_query("UPDATE booking_event SET is_reminded = 1 WHERE event_id = '$id'") or die (mysql_error()); 

}

For some reason it won't update with the ID selected from the database - I've tried using a testID variable and it works fine.

Any help would be appreciated Thanks!

Mahmoud Gamal · Accepted Answer · 2012-12-06 11:45:20Z

2

You can do this in one query like so:

UPDATE booking_event b1
INNER JOIN
(
    SELECT * 
    FROM booking_event 
    WHERE subject != "Closed" 
      AND is_reminded IS NULL 
      AND DATE_ADD(NOW(), INTERVAL 2 DAY) >= starting_date_time 
      AND NOW() <= starting_date_time
) b2
SET t1.is_reminded = 1 WHERE b1.event_id = b2.event_id

answered Dec 6, 2012 at 11:45

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hugo Delsing · Accepted Answer · 2012-12-06 11:47:40Z

1

PHP variables are case sensitive. Most likely your column name in MYSQL will be something like Event_Id or any other casing. Using SELECT * will return the actual name, so to fetch it you would need $id = $row['Event_Id'];

Best thing to be sure you dont have a casing problem, use:

SELECT event_id FROM ....

and then $id = $row['event_id']; will work.

answered Dec 6, 2012 at 11:47

Hugo Delsing

14.3k5 gold badges50 silver badges76 bronze badges

Collectives™ on Stack Overflow

MYSQL update not working with php variable id

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related