Page 1 of 1

Search query problem

Posted: Mon Aug 07, 2006 11:11 am
by mohson
OK

The name Merrill Lynch is stored in two tables.

When I use this code to search for the value Merrill or similar I find the record:

Code is:

Code: Select all

// the query used to search the DB

foreach($HTTP_POST_VARS as $varname => $value)
        $formVars[$varname]=$value;

$query = "SELECT 
	o.org_id,o.web_url,
		p.person_id,p.org_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact, 
		p.datecontactagain,p.email,


		DATE_FORMAT(dateoflastcontact, '%M/%Y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%M/%Y') 
		AS datecontactagain 

		
		FROM people p LEFT JOIN organisations o
     		ON o.org_id = p.org_id

		WHERE organisation LIKE '$formVars[organisation]%'";


$result = mysql_query($query);
But when I use a variat of the smae code to search for Merill or similar in the other table It wont find the record

Code is:

Code: Select all

foreach($HTTP_POST_VARS as $varname => $value)
        $formVars[$varname]=$value;


 $query = 	"SELECT 
		 orgname, web_url,sector,org_id,person_id, 
		 notes FROM organisations 
		 

		WHERE orgname LIKE '$formVars[orgname]%' ORDER BY orgname ASC ";

$result = mysql_query($query);
Why is this happening

Posted: Mon Aug 07, 2006 11:15 am
by volka
http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html wrote: With LIKE you can use the following two wildcard characters in the pattern:
Character Description
% Matches any number of characters, even zero characters
Merrill%: matches if the string starts with Merrill maybe followed by some other stuff.
Merrill Lynch: starts with Merrill followed by some other stuff -> match

Lynch%: matches if the string starts with Lynch maybe followed by some other stuff.
Merrill Lynch: does not start with Lynch -> no match

Posted: Tue Aug 08, 2006 3:51 am
by mohson
Thanks Volka,

But this works perfect with the first example so why not with the second?

Posted: Tue Aug 08, 2006 5:10 am
by mohson
What im failing to understand is that the database value is "Merrill Lynch" in two different tables

Using the first bit of code above to search the first table, even if I put "merrill" in the text box it gives "Merrill Lynch" as the result which is exactly what I want!!

In the second example when I search the other table, the only way it will return "Merrill Lynch" is if I put in exactly "Merrill Lynch" in the text box.

This is so frustrating but how can both pieces of code not work exactly the same??

Posted: Tue Aug 08, 2006 5:14 am
by JayBird
Could depend on your table structure and how you have your fields setup

Posted: Tue Aug 08, 2006 5:30 am
by mohson
Looking at the individual fields, the only differences are: the table relating to the code that works has the following elements in the field 'organisations', varchar30, Null = Yes, Default = Null

the elements of the field 'orgname' in the other table where the search code doesnt work as effetcivley are: varchar100

Could this really affect the searches?

Posted: Tue Aug 08, 2006 6:04 am
by JayBird
Not sure...

Maybe you want to look into FULL TEXT searched

http://www.google.com/search?q=mysql+full+text+search full text search