how do i limit my search result to be echod

Hello Friends,

I am developing a website where vacancies can be posted. Enployers will have to fill a form which include number of I weeks the vacancies would be available. I have a table in my database where the entries will be kept. I have converted the number of weeks the vacancies will be available to days and have added it to the date which the vacancy is posted to get the date the vacancy will expire. On my home page where the visitors can view the avalable vacancies, I tried subtract today's (everyday) date from the expiry date and echo the result of vacancies which are still available. i used the code below but it didn't work

<?
$table_name=vacancies;
$column_name=end_date;
$date=date("d M y", time());
$getvac=mysql_query("SELECT * FROM $table_name WHERE $column_name - $date > 0 ORDER BY id DESC");
if(mysql_num_row($getvac)==0)
echo "NO VACANCY IS AVAILABLE.";
else
{$result=mysql_fetch_array($getvac);
...}
?>

PHP:

The end date is also in this format date("d M y"). But am receiving this error

Pls what can i do or what are the other ways out because i want to echo only those vacancies whose end_date is greater than zero.

regards
adsegzy

 

query your statement first on php myadmin, that helps

 

A little recommendation for the future: When mysql queries fail, try to put the following line after the query:

echo mysql_error();

It will usually tell you what is wrong; it would've helped in this particular case.



You cannot use dates like numbers. July 6th - November 3rd is crap. The "bad" solution would be to drop the WHERE clause from your query and put an if clause to check for the difference using a more complex PHP code.

The correct solution would be to use UNIX timestamps. The idea is to convert any date to a number. The UNIX timestamp is basically the amount of seconds that have passed from January 1st, 1970 00:00 GMT. That's a big number, but computers can handle them easily. Another advantage is that you can add a time span to a date, and get another date. This is probably what you would like to do.

There are PHP functions that convert dates to UNIX timestamps, and vice versa. They even have a SQL field type called "date" to help with that. To convert a time span to a UNIX timestamp value, simple find out how many seconds there are in that time span. For example, one week has 7 days, which have a total of 168 hours, or 10080 minutes, or 604800 seconds. The UNIX equivalent of July 3rd, 2010 plus 604800 will give you the UNIX equivalent of July 10th, 2010.

 

Great post ThomasTwen! Rep+

You haven't put quotes around your text strings.
You also don't need to use PHP to generate a date for the SQL query, you could have just something like:

    $getvac = mysql_query('SELECT * FROM '.$table_name.' WHERE DATE('.$column_name.') > DATE(NOW()) ORDER BY id DESC');

PHP:

 

set a limit on the query, for example: "SELECT * FROM table ORDER BY id DESC LIMIT 0,15" this would show the first 15 results of a query and also make your querys much faster.