PHP - how do i limit my search result to be echod

Asked By Olusegun A. on 23-Jul-10 11:33 AM
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);
...}
?> 


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

Warning: mysql_num_rows(): supplied argument is not a valid Mysql result resource


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
Gayathri S replied to Olusegun A. on 24-Jul-10 08:08 AM
Hi,

The problem with your query is that, you are using a php date object inside the query and hence the query will always fail and that is the reason why you get the warning message.

You can simply use the NOW() function of mysql to accomplish this, instead of constructing a date object in php and passing it to mysql. So your query will look like:

SELECT  * FROM $table_name WHERE $column_name - NOW() > 0 ORDER BY id DESC

Also FYI, to debug such errors, it is always a good practice to include an error message printing when a query files. You can do that using the following code whenever you do a mysql call:

$result = mysql_query("your_query")
     or die(mysql_error());

Using this will tell you exactly where the error in your query is instead of just printing a warning message.