Search query problem

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
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Search query problem

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Thanks Volka,

But this works perfect with the first example so why not with the second?
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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??
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Could depend on your table structure and how you have your fields setup
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
Post Reply