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?