[jctgonzalez]

I have a form with about 20 fields that users will need to search. I'm having trouble using the Select statement to correctly produce results. If I use AND, I don't get anything and if I use OR I get all results. The code I'm using is below:

PHP Code:

$query = "SELECT * FROM source_list WHERE ";

if ($topic == "Please Select") {
    $query .= "topic='' AND (";
    } else {
    $query .= "topic='$topic' AND (";
    }
    
if ($first !== '') {
    $query .= "first LIKE '%.$first.%' AND ";
    }
    
if ($last !== '') {
    $query .= "last LIKE '%.$last.%' AND ";
    }
    
if ($area !== '') {
    $query .= "area LIKE '%.$area.%' AND ";
    }

if ($phone !== '') {
    $query .= "phone LIKE '%.$phone.%' AND ";
    }

if ($homeoffice !== '') {
    $query .= "homeoffice LIKE '%.$homeoffice.%' AND ";
    }
    
if ($area2 !== '') {
    $query .= "area2 LIKE '%.$area2.%' AND ";
    }

if ($phone2 !== '') {
    $query .= "phone2 LIKE '%.$phone2.%' AND ";
    }

if ($address !== '') {
    $query .= "address LIKE '%.$address.%' AND ";
    }

if ($city !== '') {
    $query .= "city LIKE '%.$city.%' AND ";
    }
    
if ($state !== '') {
    $query .= "state LIKE '%.$state.%' AND ";
    }    
    
if ($zip !== '') {
    $query .= "zip LIKE '%.$zip.%' AND ";
    }

if ($business !== '') {
    $query .= "business LIKE '%.$business.%' AND ";
    }

if ($title !== '') {
    $query .= "title LIKE '%.$title.%' AND ";
    }
    
if ($email !== '') {
    $query .= "email LIKE '%.$email.%' AND ";
    }
    
if ($age !== '') {
    $query .= "age LIKE '%.$age.%' AND ";
    }
    
if ($race !== '') {
    $query .= "race LIKE '%.$race.%' AND ";
    }
    
if ($category !== '') {
    $query .= "category LIKE '%.$category.%' AND ";
    }
    
if ($faith !== '') {
    $query .= "faith LIKE '%.$faith.%' AND ";
    }
    
    if ($comments !== '') {
    $query .= "comments LIKE '%.$comments.%'";
    }
    
$query .= " 1)";

$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error()); 


I'm not sure what I'm doing wrong. Any help would be greatly appreciated.

[dibap]

Hi,

I admit, that haven't really understand, what Your Query shall do, but there are 2 Ideas I had:

1. If there is an empty Topic, you compare with an empty value. I guess You should use a Wildcard or leave this check away.

2. If the field comment is empty Your Query will End with the >AND< Operator of the last expression. I suggest to change all the Strings from Ending AND to Leading AND of the Expression before.

I hope this will help a little.

dibap

[hmaldonado]

Assuming what you need, I believe that you should add an AND string in the last If statement, i which you check if the field comment has something. In the case that comment has some value, then you select statement will end like this

... AND comment LIKE '%string%' 1)

As you can see the number 1 is added at the end of the code, I mean before you send the query to the server. So I you need to add the ADD word then the statement will end like this

... AND comment LIKE '%string%' AND 1)

With this you do not need to worry about the AND at the beginning. Due to the 1 that you are adding corrects the case when your statement ends with an AND.

Good luck..