Page 1 of 1

Search Query

Posted: Mon Aug 01, 2005 4:53 am
by mohson
Hi guys this is my code for a search query I want to search for a particular field which contains the value 'yes'

What do you think of my search quesry - im getting a blank screen can you see what im missing??

Code: Select all

// the query used to search the DB

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

PRINT $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.notes,p.email,p.datecontactagain,p.email,p.consultation_panel_member,p.primary_contact,primarycontactemail,  


		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 consultation_panel_member = '$formVars[consultation_panel_member]yes'";


$result = mysql_query($query);

Posted: Mon Aug 01, 2005 8:46 am
by mohson
Can anyone one provide any assistance whicth this - I have even tried altering the where clause so that it reads

WHERE consultation_panel_member ='yes'";

but still no joy - any advice would be appreciated??

Re: Search Query

Posted: Mon Aug 01, 2005 9:12 am
by nielsene
Try:

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.notes,p.email,p.datecontactagain,p.email,p.consultation_panel_member,p.primary_contact,primarycontactemail,  


		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 consultation_panel_member = '$formVars[consultation_panel_member]yes'";

echo $query;
$result = mysql_query($query);

Posted: Mon Aug 01, 2005 9:28 am
by mohson
still no result just a blank screen - the thing is this code works fine for all my other searches so it has to be something to do with the accuracy of the where clause code.

what does anyone think????

Re: Search Query

Posted: Mon Aug 01, 2005 9:33 am
by nielsene
So you have tried:

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.notes,p.email,p.datecontactagain,p.email,p.consultation_panel_member,p.primary_contact,primarycontactemail,

	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 consultation_panel_member = 'yes'";

echo $query;
$result = mysql_query($query);
And that doesn't return any rows?

Have your tried completely getting rid of the where clause, just to make sure the JOIN is returning something?

Posted: Mon Aug 01, 2005 9:41 am
by mohson
yep nothing is returned also got rid of the where clause but still nothing but a white screen.

as I said before this code works fine for my other searches heres the code for when I search first names and it works perfect:

/ 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.notes,p.email,
p.consultation_panel_member,p.primary_contact,primarycontactemail,



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 firstname LIKE '$formVars[firstname]%'";


$result = mysql_query($query);

Posted: Wed Aug 03, 2005 10:01 am
by mohson
any more tips on this now that BBcode is back it should be clearer to read?

Posted: Wed Aug 03, 2005 10:07 am
by nielsene
Do you have error reporting turned on?