Why is this code not working

[l3rodey]

<?php

require '../../scripts/conn.php';

//open db here

	$date = ('Y-m-d 00:00:00');

	date_sub($date, date_interval_create_from_date_string('14 days'));


	$getJobs = "SELECT * FROM jobs WHERE sent='0' and dateofenquiry<='$date'";

	$result = mysql_query($getJobs); 

	while ($row = mysql_fetch_assoc($result)){

		//send email

		$updateJob = mysql_query("UPDATE jobs SET sent='0'", $conn);


		if ($updateJob = true){

			echo "Successful<br>";

		}else {

			echo "Nope<br>";

		}

	}

?>

 

That is my code not work? When I run this it will update every single row, not just ones which are 14 days after...

This is my table maybe that will help

 

id     |            dateofenquiry            |    sent

1      |       2014-01-08 13:53:29     |       0

2      |       2013-12-03 00:00:00     |       0

3      |       2013-12-03 00:00:00     |       1

 

It updates 1 and 2 and leave 3 as sent is 1. But it updates both 1 and 2... it should only update 2 as the timestamp is older then 14 days... and the first date stamp is 2 days? 

 

Help I'm crying to why this is not working.

[requinix]

You told it to

UPDATE jobs SET sent='0'

What did you expect? That it magically knew you didn't want every row to be changed? You need a WHERE clause in there to identify which particular row you want to update.

[l3rodey]

Shouldn't be because that should only run from the last clause I ran? I think... If 

$getJobs = "SELECT * FROM jobs WHERE sent='0' and dateofenquiry<='$date'";

 shouldn't that only run only where sent then I run a universal update? if not what about this?

$updateJob = mysql_query("UPDATE jobs SET sent='1' WHERE sent='0' and dateofenquiry<='$date'", $conn);

That still doesn't work that still updates every row... 

[Psycho]

$updateJob = mysql_query("UPDATE jobs SET sent='1' WHERE sent='0' and dateofenquiry<='$date'", $conn);

That still doesn't work that still updates every row... 

 

Well, it would update any row where the conditions sent='0' and dateofenquiry<='$date' are both true. Looking at your example data, rows with id 1 and 2 both meet that criteria and would be updated to have sent = 1. The row with ID = 3 does not meet that criteria - but the sent value for that row is already a 1.

 

The issue is this

 

    $date = ('Y-m-d 00:00:00');
    date_sub($date, date_interval_create_from_date_string('14 days'));

 

On the second line, the function date_sub() returns a datetime object. It is not modifying the value of $date. Plus, you can't use a datetime object in your query. Try

 

$date = date('Y-m-d 00:00:00', strtotime('-14 days'));

[Barand]

or you can do the date calculation within the query

$updateJob = mysql_query("UPDATE jobs SET sent='1' WHERE sent='0' and dateofenquiry <= CURDATE() - INTERVAL 14 DAY ", $conn);