Problem with Search Function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Problem with Search Function

Post by sree78 »

Hi people...

I have created a search site which searches for all employees working in a org. Everything works perfectly fine except, just realized about this new problem.. if a user accidently types in a space like for an example for typing Joe they typed joe* (* is actually a &nbsp)... the user won't be able to see the space and when he/she clicks submit the search results ends in 0 match.

I am using post-gres as my back-end and I kinda new to php. The ideal solution I am looking for is some function which will be able to replace the space with nothing. probably a preg_replace .. I have got no idea. I have tried trimming but it does not work. Any help is highly appreciated.

Thank You :?

**************************************************
This is how my code looks now

Code: Select all

<?php
<?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($_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('%$emp%')
		OR lower(fname) Like lower('%$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>"); 
?>
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

how did you try trimming??

this should work:

Code: Select all

searchTheDatabase(trim($_POST&#1111;'emp']));
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Post by sree78 »

Thanks a lot it worked just fine. Instead of having the trim as you suggested I had it before at:-

if(isset (trim($_POST['emp']))) but for some reason it did not work..

Thanks a lot
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Post by sree78 »

Hi,

I do have another question with regards to this search function using php. Now it eliminates the extra spaces it produces the result well except if I put type both first name and last name together e.g. Joe Smith then there is no results. Does trimming only eliminates spaces before and after a full text?

Any help is highly appreciated.

Thank You
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

trim only removes the whitespace before and after the string, but even so you are going to have to do a little more work than that.

$_POST['emp'] will be "Joe Smith" your SQL would then be

Code: Select all

$query = "Select  employee.empid,employee.fname,                    
                                      employee.lname, emp_info.phone, 
                                      department.name 
                        FROM employee, department, emp_info 
                        WHERE (lower(lname) Like lower('%Joe Smith%') 
                        OR lower(fname) Like lower('%Joe Smith%')) 
                        AND employee.empid = emp_info.empid 
                        AND department.deptid = emp_info.deptid 
                        ORDER by employee.fname";
see the problem??
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Post by sree78 »

I really appreciate all your replies and I understand your view but that is where I am stuck too. How do I go about to write a code which will allow me to use space in between for the first name and last name in the search function.

Any replies are highly appreciated.

Thanks You

:wink:
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

you can use spaces between the name, the problem is you are searching 2 different fields in the database, being first name and last name for a complete name, so either you need to split the variable using something like explode() and search on the first name and surname you get from that, or you will need to use a concat() function in your SQL and search that for the complete name.

there are always several solutions to a problem, I hope this gives you somewhere to start from.
Post Reply