#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    4
    Rep Power
    0
    I need some help with a PHP/mysql_query
    Here's the line:

    $result = mysql_query ("SELECT * FROM data1 WHERE location LIKE '$location%' AND rental LIKE '$rental%' AND purchase LIKE '$purchase%'",$db);

    if ($row = mysql_fetch_array($result)) {

    It mostly works, but if the user chooses a location and both rental AND purchase, it shows no records.

    If the user chooses either rental OR purchase it pulls up the appropriate records correctly.

    I've tried:
    "AND rental LIKE '$rental%' OR purchase LIKE '$purchase%"
    but it still doesn't work.

    I need the search to work for users that want to choose both rental AND purchase from a location.
    What am I doing wrong?
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    $result = mysql_query ("SELECT * FROM data1 WHERE location LIKE '$location%' AND ( rental LIKE '$rental%' OR purchase LIKE '$purchase%' )",$db);

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    4
    Rep Power
    0
    Nearly...
    If a user chooses a location
    And rental
    And purchase
    It pulls up the correct entries.

    BUT
    If a user chooses a location
    And rental or purchase
    It pulls up all fields with both rental and purchase.... which is incorrect, it should only pull up location and either rental or purchase, not both.

    A simple thing maybe, but it's driving me mad!

  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Okay..you have to construct some logic to create your query. If they didn't enter a rental, then you don't want it in your query. Something like this...
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $query = "SELECT * FROM data1 WHERE location LIKE '%$location%' ";
    if ($rental != "")
    {
    $query .= " AND rental LIKE '%$rental' ";
    }
    if ($purchase != "")
    {
    $query .= " AND purchase LIKE '%$purchase%' ";
    }

    $result = mysql_query($query,$db);
    [/code]
    The problem was if the user didn't enter rental, then $rental eithe wasn't set or equaled an empty string. So when you search for something like

    rental LIKE '%$rental%'

    You're actually searching for something like

    rental LIKE '%%'

    which will return all of the records.

    ---John Holmes...

    [This message has been edited by SepodatiCreations (edited October 14, 2000).]
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    4
    Rep Power
    0
    But if a user chooses rental AND purchase.

    There's no records found, because there are no records that are both... they are either one or the other.

    What I need is that when the user chooses both, they get records that have the chosen location AND are both rental and purchase.

    I hope this makes sense, I appreciate your help!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    euless, texas, usa
    Posts
    3
    Rep Power
    0
    Same concept, just merging the two posted answers above. I haven't tested this at all, so caveat emptor.

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>$query = "SELECT * FROM data1 WHERE location LIKE '$location%'";

    if ($rental != "" && $purchase != "") {
    $query .= " AND (rental LIKE '$rental%' OR purchase LIKE '$purchase%')";
    } elseif ($rental != "" && $purchase == "") {
    $query .= " AND rental LIKE '$rental%'";
    } elseif ($rental == "" && purchase != "") {
    $query .= " AND purchase LIKE '$purchase%'";
    }

    $result = mysql_query($query,$db);[/code]
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    4
    Rep Power
    0
    Thanks John, that's exactly what I was looking for.

    It works as I wanted.

Similar Threads

  1. Linux C/C++ winsock & thread question
    By beholder242 in forum C Programming
    Replies: 3
    Last Post: January 28th, 2004, 07:31 AM
  2. PHP data retrieval question with MySQL
    By gursharn in forum PHP Development
    Replies: 7
    Last Post: December 31st, 2003, 10:43 AM
  3. Javascript question mark question?
    By tajmiester in forum JavaScript Development
    Replies: 3
    Last Post: November 30th, 2003, 06:36 PM
  4. Radio Buttons
    By Ulysses Freeman in forum HTML Programming
    Replies: 4
    Last Post: November 15th, 2003, 10:57 PM
  5. newbie dns question
    By ragemaster in forum DNS
    Replies: 3
    Last Post: November 3rd, 2003, 10:11 AM

IMN logo majestic logo threadwatch logo seochat tools logo