Trying to alter a table value once its corresponding date has expired

[jaimec]

Hello,

 

I'm in need of some help.

 

I've inherited a site that runs a simple jobs board script and am completely stuck.

 

When the $job_date_end field has passed then the job as expected, ceases to be live on the site. In the back-end admin section there's also an option to suspend a job. What I want to do is add a line of code that reads whether the $job_end_date has passed, and if so, suspends it $job_status=1

 

I thought this might work

 

 

if ( $job_date_end >= $time ) {
$job_status=0;
} else {
$job_status=1;
}

 

but I'm getting no joy (I'm a flash designer new to php but trying to learn).

 

Any advice would be greatly appreciated.

 

Here's the php code from the script that reads whether a job is still in date and therefore good to be published in the list.

 

 

$time = time();

switch ($_GET["sub"]) {

	case "details":

		$job = $this->db->QFetchArray("SELECT * FROM {$this->tables[job_list]} WHERE job_id = '{$_GET[id]}' AND job_status=0 AND job_date_start <= '{$time}' and job_date_end >= '{$time}'");

		//check if is a valid job
		if (is_array($job)) {

			//add the extra data
			$job["job_category_title"] = $cache["categories"][$job["job_category"]];
			$job["job_department_title"] = $cache["departments"][$job["job_department"]];
			$job["job_location_title"] = $cache["locations"][$job["job_location"]];
			$job["job_name_title"] = $cache["names"][$job["job_title"]];

			$job["referer"] = $_SERVER["HTTP_REFERER"] ? $_SERVER["HTTP_REFERER"] : "list.php";
			$job["job_salary"] = number_format($job["job_salary"] , 2 );
			$job["details_link"] = $job["job_link"] ? $this->templates["details"]->blocks["Link"]->Replace($job) : "";

			return $this->templates["details"]->blocks["Main"]->Replace($job);

		} else {
			//redirect to error.php
			header("Location: error.php");
			exit;
		}	

[Guardian-Mage]

Please post how you fill the $time variable. This is just a guess, but I think you are setting $time wrong, or not as the same format as the $job_date_end. For example:

$time = date("Y-m-d")//Valid format for defining $time

$job_date_end = 2006-06-05 //Then code works
//However
$job_date_end = 2006-06-05 21:23:32 //This doesn't work
//or
$job_date_end = 2006:06:05//This wouldn't work either. 

 

Please post the values of $time and $job_date_end

 

-Brandon

[Barand]

run an update query on the table

 

UPDATE jobtable SET job_status = 1 WHERE job_date_end < CURDATE()

[jaimec]

This is really strange but in the actual MySql database, the latest job entry has a value of

 

1179900061

 

for the job_end_date - when listed on the latest jobs page its comes out as 6 June 2007 which is correct!? I'm guessing there's a converison in the script somewhere.

 

 

As for the update query - as jobs are added and expiring on a regular basis, could I add a line of code into the script - eg

 

mysql_query("UPDATE TABLE  `job_list` SET  job_status=1 WHERE job_date_end < CURDATE());

$site->Run();

 

would that work or have I mangled the syntax?

 

[Barand]

Your date is stored as a unix time value

 

UPDATE TABLE  `job_list` SET  job_status=1 WHERE FROM_UNIXTIME(job_date_end) < CURDATE()

[jaimec]

Thanks for that - I'll give it a try

[jaimec]

Is the correct syntax

 

 

mysql_query("UPDATE TABLE  `job_list` SET  job_status=1 WHERE FROM_UNIXTIME(job_date_end) < CURDATE()");

$site->Run();

[Barand]

looks ok

[jaimec]

Hasn't worked I'm afraid - jobs that were set to expire still have a setting of

 

$job_status=0

 

rather than the suspended

 

$job_status=1

 

is there anything else I can try?

[jaimec]

Tried the query in MySQLAdmin and got the following error

 

MySQL said:

 

#1064 - You have an error in your SQL syntax;

 

 

 

The hosting is

 

PHP version 4.4.6

MySQL version 5.0.27-standard

 

 

 

[redarrow]

set the job status database table to a int ok.

[jaimec]

Just checked and it does appear to be:

 

Field            Type    Length/Values    Default

job_status  int      1                        0

[jaimec]

Got it working

 

UPDATE job_list SET job_status=1 where FROM_UNIXTIME(job_date_end) < CURDATE()

 

cheers for all your help