Search Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Search Query

Post 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);
Last edited by mohson on Wed Aug 03, 2005 10:00 am, edited 1 time in total.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post 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??
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Re: Search Query

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

Post 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????
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Re: Search Query

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

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

Post by mohson »

any more tips on this now that BBcode is back it should be clearer to read?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Do you have error reporting turned on?
Post Reply