Multimple searches in one

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

Multimple searches in one

Post by mohson »

Hi everyone,

I use this code to search for specific values in my database

Code: Select all

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

	$query = 	"SELECT 
		 
		salutation,name,surname,organisation,email,address,address1,
	telephonefax,mobile,mscints,mscactive,gradjobs,ugproj,pdev,bcsmem,bcspds,teach,acconsult,person_id
	
	FROM feedbackcontacts
	
	WHERE mscactive = '$formVars[mscactive]yes'";

	$result = mysql_query($query);
What I want to know is how do I adapt this to search two more colums.

I.e where BCSmembership and BCSPDS = 'yes'

In other words when a user selects submit, the query searches mscactive, bcsmem, bcspds for the value 'yes' as is currently done above.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

This checks that ALL of the columns are 'yes':

Code: Select all

SELECT salutation,name,surname,organisation,email,address,address1,
telephonefax,mobile,mscints,mscactive,gradjobs,ugproj,
pdev,bcsmem,bcspds,teach,acconsult,person_id 

FROM feedbackcontacts 
        
WHERE (mscactive = 'yes' 
AND BCSmembership = 'yes'
AND BCSPDS = 'yes')
This checks that at least one of the columns contains 'yes':

Code: Select all

SELECT salutation,name,surname,organisation,email,address,address1,
telephonefax,mobile,mscints,mscactive,gradjobs,ugproj,
pdev,bcsmem,bcspds,teach,acconsult,person_id 

FROM feedbackcontacts 
        
WHERE (mscactive = 'yes' 
OR BCSmembership = 'yes'
OR BCSPDS = 'yes')
I've included parenthesis only to make it easier to read (you may want to add further AND or OR statements).

Hope this helps.
Last edited by GM on Mon Jul 17, 2006 7:55 am, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multimple searches in one

Post by Benjamin »

Code: Select all

SELECT `salutation`, `name`, `surname`, `organisation`, `email`, `address`, `address1`, `telephonefax`, `mobile`, `mscints`, `mscactive`, `gradjobs`, `ugproj`, `pdev`, `bcsmem`, `bcspds`, `teach`, `acconsult`, `person_id` FROM `feedbackcontacts` WHERE `mscactive` = '$formVars[mscactive]yes' AND `BCSmembership`='yes' AND `BCSPDS`='yes'
Someone beat me :)
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Abit confusing one of you has kept the $formvars bit and the other hasnt?

Which one is correct Im in the process of trying both.

What do you think?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

The variable appears to be a part of the original code, and is probably something you do not want to remove. Hence I preserved it in the query.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

I took it out, because in the original code there was also "yes" written after it, so I assumed it was a mistake and removed it. Also, (despite the code) you said in your original post that you were searching for the value "yes".

If you are testing for the presence of "yes" in the column, then it doesn't make sense to use a variable. If, however, you are testing for the presence of whatever is in the variable, then obviously you would need to leave the variable there.

What are you testing for? If the value "yes", then my solution will work. If the value is not known until runtime, you need to use a variable.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Code: Select all

$query = "SELECT 
		 
		salutation,name,surname,organisation,email,address,address1,
	telephonefax,mobile,mscints,mscactive,gradjobs,ugproj,pdev,bcsmem,bcspds,teach,acconsult,person_id
	
	FROM feedbackcontacts
	
	WHERE mscactive = 'yes'

	OR mscints = 'yes'
	
	";
Thanks guys ive cleared that up. im only searching on 'Yes' problem is when I run this query it returns both results - those details with people who say 'yes' to mscact and mscint.

As you say this code checks if one of the colums say 'yes', thats fine but I want it to display only that colum which says yes. This query returns all colums which say yes.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

You'll need to some some manipulation of the result set with PHP, because SQL will return all the fields you ask it for (in the SELECT part of the query), from all the records that match the search criteria (the WHERE part of the query).

If one field is 'yes' and the other is 'no' SQL will return both columns, one with value 'yes' and one with value 'no'. It's then up to you to display them in the way you want.
Post Reply