Page 1 of 1

Need Help!! with Search function using 4 different elements

Posted: Tue Aug 05, 2003 11:34 am
by sree78
Hi,

I want to do a search function for my employee directory which can be searched by either:-

First Name
Last name
Department
Phone

My form looks something like this:-

Code: Select all

<form ACTION="search_result2.php" method="get" name="lib">
 <input type="hidden" name="dept" value="<? echo $rowїdeptid];?>">
  
First Name <input NAME="fname" size="20"><br>
Last Name <input NAME="lname" size="20">

Code: Select all

Department:
<select name="dept">
<option value="" selected>Select a Department (optional)</option>

   <?
$sql="select * from department
order by name";
$result=pg_query($sql);
for($i=0;$i<pg_numrows($result);$i++)
{
$arr=pg_fetch_array($result,$i);
echo "<OPTION value=".$arr["deptid"].">".$arr["name"];
}
?>
</select>

Code: Select all

Work Phone&lt;input NAME="phone" size="20&gt;&lt;br&gt;

&lt;input TYPE="submit" name="submit" value="Search"&gt;
&lt;input TYPE="Reset" name="Reset"&gt;

&lt;/form&gt;
##############################################

Right now I am very familiar how to search using only function but I am not sure how to construct a sql which can use either one of the information to do the search.

My current results page.. which only searches employees by department is as below:-

Code: Select all

<?php
if(isset($_GET['dept'])) 
	{ 
   		print("<table width="700" align="center">
						<tr>
 						<td>
  							<table width="600">
							<font face="Arial, Helvetica, sans-serif" size="3"><strong><u>Search Results</u></strong></font></td>
							</tr><tr><td>&nbsp;</td></tr>"); 
   searchTheDatabase(trim($_GET['dept'])); 
	} 


	function searchTheDatabase($dept) 
	{ 
   
   
		$query = "Select Distinct
		employee.empid, employee.fname, employee.lname, emp_info.phone, department.name
		FROM employee, department, emp_info
		WHERE department.deptid = '$dept'
		AND employee.empid = emp_info.empid
		AND department.deptid = emp_info.deptid
	 ORDER by employee.fname";
		
		print ("$query");
		$dbResult = pg_query($query); 
   		if(!pg_numrows($dbResult)) 
		
		{ 
      		print("<tr><td><font face="Arial, Helvetica, sans-serif" size="2">Sorry Nothing was found matched your query:
				   </font></td></tr>"); 
        } 
   
   			else 
				{ 
				
				print("<tr><td colspan="3"><font face="Arial, Helvetica, sans-serif" size="2">
						The following matches were found to your query: 
						</font></tr>
						<tr><td colspan="3">&nbsp;</td></tr>"); 
?>
Would really appreciate if someone who either had experience doing this before or know how to do it could assist me. Thank You very much :roll:

Posted: Wed Aug 06, 2003 4:38 am
by pootergeist
compose the where clause as a concatenated string, then compile into the query

Code: Select all

$where_clause = 'WHERE ';
$and_clause = '';
if(isset($_GET['fname']) && $_GET['fname'] !== "")
	{
	$where_clause .= 'employee.fname=''%' .$_GET['fname']. '%'' ';
	}
if(isset($_GET['lname']) && $_GET['lname'] !== "")
	{
	$where_clause .= (strlen($where_clause) > 7) ? 'AND ' : '';
	$where_clause .= 'employee.lname=''%' .$_GET['lname']. '%'' ';
	}
if(isset($_GET['dept']) && $_GET['dept'] !== "")
	{
	$where_clause .= (strlen($where_clause) > 7) ? 'AND ' : '';
	$where_clause .= 'department.deptid=' .$_GET['dept']. ' ';
	$and_clause .= ' AND department.deptid = emp_info.deptid';
	}
if(isset($_GET['phone']) && $_GET['phone'] !== "")
	{
	$where_clause .= (strlen($where_clause) > 7) ? 'AND ' : '';
	$where_clause .= 'emp_info.phone=' .$_GET['phone']. ' ';
	}
$where_clause .= ((strlen($where_clause) > 7) ? 'AND ' : ''). 'employee.empid = emp_info.empid ';

      $query = "SELECT DISTINCT
      employee.empid, employee.fname, employee.lname, emp_info.phone, department.name
      FROM employee, department, emp_info
      " .$where_clause.$and_clause. "
    ORDER by employee.fname";
     
echo $query;

Posted: Fri Aug 08, 2003 11:53 am
by sree78
Hi pootergist,

Your code worked really well. I just had to tweak a little and it was awesome. I am still poor coding regular expression but your code have given me a great example of how to do such searches in the future. Thanks a lot..

Posted: Fri Aug 08, 2003 7:44 pm
by McGruff
sree78 wrote:I am still poor coding regular expression
Me too. This is a handy tool to try out expressions:

http://www.weitz.de/regex-coach/#install

Posted: Fri Aug 08, 2003 7:51 pm
by macewan
thanks for that link to regex-coach