only list jobs if current time > expiry time (php mysqli)

Question

hello :)

I am trying to list out all records from a database that have not expired.

i have a jobs listing site being developed, i have code to grab all the 'active' job details from the database and list out.

$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
$stmtJ = $mysql->prepare('SELECT id,jobtitle,vcref,jobtype,jobintro,closingdate FROM jobs WHERE active = 1');

$stmtJ->execute();
$stmtJ->store_result();
$stmtJ->bind_result($id,$jobtitle,$vcref,$jobtype,$jobintro,$closingdate);
$totalLiveJobs = $stmtJ->num_rows();

and is outputted like so:

<?php
while($row = $stmtJ->fetch()) :
echo("<p>job detail here</p>");
endwhile; 
?>

and i also have an 'if statement' snippet to see if the job has expired or not (i.e if expires {expires is a date yyyy-mm-dd} is greater than '$now' it should not show up.

$expires = date('Y-m-d-H-i-s',strtotime("$closingdate 12:00:00"));

$now = date('Y-m-d-H-i-s');


if ($expires < $now) {

echo ("<h3>Live Jobs Here</h3> $expires");

} else {

echo ("<h3>Job Expired</h3> $now");

}

However, i'm having problems merging the two together with the main goal being all jobs that have not yet expired will be shown on the page, any jobs that have expired should not be shown on the page.

I am pulling my hair out here, any help welcome, any requests for further details i'll respond as soon as i possibly can.

thx in advance

Answer 1

depending on your database structure, you could do

SELECT * FROM jobs WHERE active = 1 AND closingdate >= NOW()

Answer 2

You can merge two SQL query results together that have the same returned columns using UNION.

See more here: W3C Schools: SQL Union.

Answer 3

If all data stored within one table you can use this query:

SELECT id,jobtitle,vcref,jobtype,jobintro,closingdate 
FROM jobs 
WHERE CONCAT(closingdate, ' 12:00:00') > '{$mysql_timestamp}' AND active = 1

Note: mysql timestamp format is

date('Y-m-d H-i-s')

also you can use built in NOW() function, however depending on your hosting location there might be different timezone from your website audience.