Page 1 of 1

Problem Creating a "two word" search (PHP & Po

Posted: Mon Aug 04, 2003 10:00 am
by sree78
Hi there,

I am new to php and having difficulty in doing a two word search in my employee directory database I have created. I can either search with last name or first name, but if I put in both names then I am not getting any results. I am using Posgresql as my back-end. I believe Mysql has this function called CONCAT.. I tried to use this function.. but does not seem to work. Any ideas are highly appreciated.

Thank you in advance


:? My current code:-

Code: Select all

<?php

 <form action="search_result.php" method="post" name="lib">
 <input type="text" name="emp">
 <input type="submit" name="search" class="submit_button"value="GO">
search_result.php

Code: Select all

<?php 

   if(isset ($_POST['emp']))
	
  { 
    print("<table width="700" align="center">
    <tr>
   <td>
   <table width="600">

  <font face="Arial, Helvetica, sans-serif" size="">
  <strong><u>Search Results</u></strong></font></td>
  </tr>

  <tr>
  <td>&nbsp;</td>
  </tr>"); 
  
 searchTheDatabase(trim($_POST['emp']));  
	
   } 

 function searchTheDatabase($emp) 
	
  { 
      $query = "Select 
      employee.empid, employee.fname, employee.lname,   
      emp_info.phone, department.name
      FROM employee, department, emp_info
      WHERE (lower(lname) Like lower('%".$_POST['emp']."%')
      OR lower(fname) Like lower('%".$_POST['emp']."%'))
      AND employee.empid = emp_info.empid
      AND department.deptid = emp_info.deptid
      ORDER by employee.fname";
		
    $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:
				    
 <strong><i>$emp</i></strong></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: 
				
   <strong><i>$emp</i></strong></font></tr>
						
   <tr><td colspan="3">&nbsp;</td></tr>"); 
				
   print("<tr><td width="220">
             <font face="Arial, Helvetica, sans-serif" size="2">
            <b>Name</b></font></td>
					                
            <td><font face="Arial, Helvetica, sans-serif" size="2">
            <b>Department</b></font></td>
            <td><font face="Arial, Helvetica, sans-serif" size="2">
             <b>Phone</b></font></td>
						</tr>"); 
?>

Posted: Mon Aug 04, 2003 10:05 am
by evilmonkey
Why don't you try treating each word as a separate search object? Your problem is that you compare 1 string to two strings. I'll give you an example. You have an employee named Bob Smith. In the database it's stored as First name: Bob, Last Name: Smith. You punch "Bob Smith" into your search box and try to get the result. It compares "Bob Smith" to "Bob" and it compares "Bob Smith" to "Smith". Therefore, gets no result. Try separating the string.

This is just an idea, correct me if I'm wrong.

Cheers!

Posted: Mon Aug 04, 2003 10:31 am
by sree78
Really appreciate your fast reply, I understand your point. It was one of the option I had by having each word to be treated as a separate search object. I am just curious how we can integrate both the two colum in the database using the search function. Is there any way we could do it? Thanks again.

Posted: Mon Aug 04, 2003 4:50 pm
by evilmonkey
You're saying how you can do partial matches? For example, if you write "Bob Smith", you want it to mathc "Bob" and "Smith"? (I didn't really understand what you were saying), but if that is what you want to do, than once again, you wil have to separate the string.

Posted: Tue Aug 05, 2003 9:16 am
by sree78
(Separate the strings) Did you mean that I will have to create a two input box and have the user to then enter the information? So the search function will treat each string differently when it searches the DB?

Code: Select all

<?php
<form action="search_result.php" method="post" name="lib"> 
First Name<input type="text" name="fname"> 
Last Name<input type="text" name="lname">
<input type="submit" name="search" class="submit_button"value="GO"> 
?>
Thank You