How do I display search results by date?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rach73
Forum Newbie
Posts: 5
Joined: Thu Apr 15, 2004 7:37 am

How do I display search results by date?

Post by rach73 »

Hello,

I've taken over a website from someone else and I have very little knowledge of PHP. The site includes a search page where you can search a database of work placements. Currently the search displays all work placements including ones which are past their closing date. I want to be able to change the code so that it only displays placements that are current (i.e. the ones with a date ahead of the current date). The current code which searches the databases is here:

Code: Select all

$query = "select tblPlacementData.placementid as id, tblPlacementData.*, tblCompanies.* from tblPlacementData, tblCompanies 
where tblPlacementData.companyid = tblCompanies.companyid AND 
companyname like '%".$compname."%' AND
jobtype like '%".$jobtype."%' AND
location like '%".$location."%' AND
information like '%".$information."%'
";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
I have tried to add the piece of code below to make it show only current placements but it comes up with an error saying 'Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /export/home/www/data/placements/search_results_test.php on line 64'

Code: Select all

$query = "select placementid, companyid, jobtype, location, closingdate from tblPlacementData 
where UNIX_TIMESTAMP(dateadded) <= $lastlog 
AND to_days(closingdate) - to_days(now()) >= 0
order by closingdate asc";
Could anyone help me to sort this out - I'm sure its something very simple but I'm very new to PHP so don't know what to do next!

Thanks
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post by swdev »

If you want to know all those jobs whose closing data is sometime in the future, and don't care when their starting date is (ie all those jobs that are either currently open or that will start at some point in the future), then use code like

Code: Select all

$query = 'SELECT ' .
        ' tblPlacementData.placementid as id, tblPlacementData.*, ' .
        ' tblCompanies.* ' . 
        ' FROM ' .
        ' tblPlacementData, ' . 
        ' tblCompanies ' .
        ' WHERE (' .
        ' (tblPlacementData.companyid = tblCompanies.companyid) ' .
        ' AND ' .
        ' (companyname like ''%' . mysql_escape_string($compname) .'%'')' .
        ' AND ' .
        ' (jobtype like ''%' . mysql_escape_string($jobtype) .'%'')' .
        ' AND ' .
        ' (location like ''%' . mysql_escape_string($location) .'%'')' .
        ' AND ' .
        ' (information like ''%' . mysql_escape_string($information) . '%'')' .
        ' AND ' .
        ' (UNXITIMESTAMP(closingdate) > UNIXTIMESTAMP(NOW()) )' .
        ')';
I lay out my SQL statements like this so that it is easy to see where the variables are and to see where I have missed quotes. It also makes it easy to add / remove parts of the statement. Unless you really need all the columns from the tblPlacementData and tblCompanies tables, I would replace those 2 parts of the query with just the columns you need.

When debugging SQL, it is alwasy a good idea to echo the SQL statement before itis execute, so you can see that it looks correct.

You should always check the returns from mysql_* functions for errors like

Code: Select all

$result = mysql_query($sql);
if (!$result)
{
  echo 'Problem running ' . $query . ' due to ' . mysql_error();
}
This will print out the query and the MySQL error message if the query fails.

Hope this helps
rach73
Forum Newbie
Posts: 5
Joined: Thu Apr 15, 2004 7:37 am

Post by rach73 »

Thanks ever so much for your help. I've tried that but it is still coming up with the same error. The next lines of code after that are:

Code: Select all

$result = mysql_query($query);
$num_results = mysql_num_rows($result);
The error says 'mysql_num_rows(): supplied argument is not a valid MySQL result resource'. Do I need to change that piece of code to something else?

Thanks.
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

the query failed, try trapping the mysql error
and go from there

Code: Select all

$result=mysql_query($query) or die(mysql_error());
Post Reply