Page 1 of 1

Multimple searches in one

Posted: Mon Jul 17, 2006 7:40 am
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.

Posted: Mon Jul 17, 2006 7:53 am
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.

Re: Multimple searches in one

Posted: Mon Jul 17, 2006 7:54 am
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 :)

Posted: Mon Jul 17, 2006 9:07 am
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?

Posted: Mon Jul 17, 2006 9:10 am
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.

Posted: Mon Jul 17, 2006 9:24 am
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.

Posted: Mon Jul 17, 2006 9:41 am
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.

Posted: Mon Jul 17, 2006 10:00 am
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.