how to code a search engine for date??

[bb nicole]

Emm.. I have done search engine for a job portal, if i want to a list menu for date posted as below, what code sql query should i needed??

List menu
Date Posted:
1 day ago
1 week ago
2 week ago
All

And what code or sql query for 'All'???

Thanks..:)

[Motoma]

Emm.. I have done search engine for a job portal, if i want to a list menu for date posted as below, what code sql query should i needed??

List menu
Date Posted:
1 day ago
1 week ago
2 week ago
All

And what code or sql query for 'All'???

Thanks..:)

Why SQL flavor are you using?

[bb nicole]

Why SQL flavor are you using?

Emm... Sorry.. What do u means?? I have complete the code as below, but i have no idea how to add a search function for date the job posted by employer, because the date posted inserted in database is a dd mm yyyy, not like below...
1 day ago
1 week ago
2 week ago


[PHP]<?php
mysql_connect("localhost","root","")
or die("Cannot connect to the server");

mysql_select_db("ums e-job portal")
or die("Cannot connect to the database");

if (isset($_POST['keyword'])) {
$keyword = mysql_real_escape_string($_POST["keyword"]);
$jobCategory=$_POST["jobCategory"];
$jobLocation=$_POST["jobLocation"];

$sql="SELECT * FROM job WHERE jobCategory LIKE '$jobCategory' AND jobLocation LIKE '$jobLocation' AND jobTitle like '%$keyword%' AND jobType LIKE '%$keyword%'OR jobPosition LIKE'%$keyword%'OR companyName LIKE '%$keyword%' ";
?>[/PHP]

[Motoma]

I have moved this thread to the MySQL forum, the folks there should be much more helpful.

[ronverdonk]

I have the distinct feeling, from all your previous threads in both the PHP and the MySQL forums, that you could do with some MySQL tutorials and I'd suggest that you do that before you continue.

Fort this question, it can all be found in the standard MySQL documentation. I'll give 1 example. To calculate the date for 1 day, 1 week and 2 weeks ago from a given date in format 'dd-mm-yyyy'you issue the following SQL statements:

// since 1 day
SELECT DATE_SUB(STR_TO_DATE(column_name, '%d-%m-%Y'), INTERVAL 1 DAY) as dayminus1;
 
// since 7 days
SELECT DATE_SUB(STR_TO_DATE(column_name, '%d-%m-%Y'), INTERVAL 1 WEEK) as weekminus1;
 
// since 2 weeks
SELECT DATE_SUB(STR_TO_DATE(column_name, '%d-%m-%Y'), INTERVAL 2 WEEK) as weekminus2;

Ronald :cool:

[lachu]

I have done search engine for a job portal, if i want to a list menu for date posted as below, what code sql query should i needed??

List menu
Date Posted:
1 day ago
1 week ago
2 week ago
All

And what code or sql query for 'All'???

Thanks..:)[/quote]