selecting with multiple possibilities

[Manfred1976]

Hi all, im completely new to this forum and a n00b when it comes to php and mysql, but im atempting to make an internal site that will locate potential leads from an outsourced company. they provide us with a single excel table stating everything we need to know about individual customers, but i am trying to write a search facility for our marketing side.

I have managed to create an edit page where i can edit details sent by them, and i have managed to create a page to display, the only thing im lacking is the search page

Here is where the problem lies, i want to be able to search through every available field, so i might want to type in the first name and bring up everyone with that name, or the last name, or the gender, etc etc. or a mixture of several of these. the way i have atempted to code this is by a select statement with multiuple IF clauses regarding if the form is empty: - see below

$query = "SELECT * FROM leads WHERE ";
if $date !=""
$query .= "date='$date' AND ";
if $sex !="";
$query .= "firstname='$firstname' AND ";
if $lastname !="";
$query .= "lastname='$lastname' AND ";
if $sex !="";
$query .= "sex='$sex' AND ";
if $address1 !="";
$query .= "address1='$address1' AND ";
if $address2 !=""
$query .= "address2='$address2' AND ";
if $address3 !=""
$query .= "address3='$address3' AND ";
if $address4 !=""
$query .= "address4='$address4' AND ";
if $postcode !=""
$query .= "postcode='$postcode' AND ";
if $email !=""
$query .= "email='$email' AND ";
if $telephone !=""
$query .= "telephone='$telephone' AND ";
if $enquiry !=""
$query .= "enquiry='$enquiry' AND ";
if $notes !=""
$query .= "notes='$notes' AND ";
if $followup !=""
$query .= "followup='$followup' AND ";
if $datesent !=""
$query .= "datesent='$datesent' AND ";
if $information !=""
$query .= "information='$information' AND ";

$query2 = substr($query,0,-4)

all this does is create a parse error, now i know im doing something simple wrong i just cant spot it for the left of me, and after searching the internet for the past couple of days i havent found the error.

[sid]

it would be easier, if you provided us with the actual error message, but starting from the top, i came across this first, which seems to repeat for each variable:

f.ex.

if $date !=""
$query .= "date='$date' AND "; 

should be

if ($date !="")
$query .= "date='$date' AND "; 

or, how i would prefer it

if ($date) {    //this implies that $date != ""
    $query .= "date='$date' AND ";
}

i just like it better to
a.) always use the round brackets, since it makes expanding the code easier and
b.) use indentation within the brackets, to make the code more readable.

also, check [man]if[/man]

at the end:

$query2 = substr($query,0,-4);

the trailing semicolon was missing.

[rgermain]

Not that it matters, but you could also do the following.

if(!empty($date))
{
       $query .= "date='$date' AND "; 
}

[Will_Poulson]

Also, you might have some trouble with dangling AND statements.

When I run through these sorts of things, I stack them up into arrays,

$sql_stack[]="something = $something"

and then do a foreach that loops through the array afterwards adding the AND

This is also really handy when you start doing multi box selects that have to be in their own ()

Where
(
(something = something
or
soemthing = somethingelse
)
or
(something > something
and
someotherthing = 1)
)

So you can make a couple different stacks

Really helps when you need to switch from narrow AND to broad OR

Good luck

Plus Narrow versus broad returns you

[Manfred1976]

cheers for the assistance, i finally went for

$query = "SELECT * FROM leads WHERE ";
if (!empty($date)) {
    $query .= "date='$date' AND ";
}

and i get no errors. Unfortunatly i get every result but what the heck, its better than nothing *grin*