Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    1

    Unanswered: mySQL select statement problem

    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.

  2. #2
    Join Date
    Jul 2003
    Location
    Stuhr/germany
    Posts
    4
    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

  3. #3
    Join Date
    Jun 2003
    Location
    México
    Posts
    5

    Re: mySQL select statement problem

    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •