Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search criteria: if a field is any[eg beds] THIS IS CORRECT ?

    how to implement code [mysql/html] so if someone choose "any" for bedroom COLUMN[DROP DOWN MENU] results to show independence to bedroom column[according ONLY OTHER CRITERIA] ??

    This query is correct for this ?
    $query = "SELECT * FROM realestatetable WHERE (type=$type OR type='any') AND (area=$area OR area='any') AND (bedrooms=$beds OR bedrooms='any') AND price>$min AND price<$max ORDER BY price desc";

  • #2
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    No, that will not work as the query will look for the word "any" in the bedrooms field, just like it will in type and area.

    With a dynamic query like yours, you must dynamically create your query based on the selections the user makes, like so:
    PHP Code:
    // Base query
    $query "SELECT * FROM realestatetable WHERE price>$min AND price<$max";

    // If the user didn't select "any" for type, search for the type
    if (strcasecmp($type'any') !== 0)
      
    $query .= " AND type = '$type'";
    // If the user didn't select "any" for area, search for the area 
    if (strcasecmp($area'any') !== 0)
      
    $query .= " AND area = '$area'";
    // If the user didn't select "any" for bedrooms, search for the number of bedrooms 
    // (This assumes a number, which is why no quotes around $bedrooms)
    if (strcasecmp($bedrooms'any') !== 0
      
    $query .= " AND bedrooms = $bedrooms";

    // Now add the ORDER BY clause
    $query .= " ORDER BY price DESC"
    -Shane

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please note that html form has choice for
    bedrooms
    any
    1
    2
    3
    4
    +5
    well , my code will work ?

  • #4
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Quote Originally Posted by lse123 View Post
    Please note that html form has choice for
    bedrooms
    any
    1
    2
    3
    4
    +5
    And how is the info the DB stored? In the bedrooms field, does it just store the number? If so, with your selection choice, you'll have to take my code and modify it a bit so that it can properly search (see below). To be honest, you really need to do some tutorials on SQL queries.
    PHP Code:
    if (strcasecmp($bedrooms'1') === 0
      
    $query .= " AND bedrooms = 1";
    else if (
    strcasecmp($bedrooms'2') === 0
      
    $query .= " AND bedrooms = 2"
    else if (
    strcasecmp($bedrooms'3') === 0
      
    $query .= " AND bedrooms = 3"
    else if (
    strcasecmp($bedrooms'4') === 0
      
    $query .= " AND bedrooms = 4"
    else if (
    strcasecmp($bedrooms'+5') === 0
      
    $query .= " AND bedrooms > 4"
    -Shane

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    bedrooms=$beds OR bedrooms='any'
    this syntax for string field is ok ?

  • #6
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Quote Originally Posted by lse123 View Post
    Code:
    bedrooms=$beds OR bedrooms='any'
    this syntax for string field is ok ?
    Quote Originally Posted by TheShaner View Post
    No, that will not work as the query will look for the word "any" in the bedrooms field, just like it will in type and area.
    Are you reading my posts at all? I said from the beginning that it isn't and that you need set up your query like I'm doing in my examples. Your bedrooms field will not have the word "any" in it, so you can't search on that term. If someone selects "any", what you do is NOT search the bedrooms field.

    -Shane

  • #7
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I MUST USE
    if (strcasecmp($bedrooms, 'any') !== 0)
    $query .= " AND bedrooms = $bedrooms";

    OR

    if (strcasecmp($bedrooms, '1') === 0)
    $query .= " AND bedrooms = 1";
    else if (strcasecmp($bedrooms, '2') === 0)
    $query .= " AND bedrooms = 2";
    else if (strcasecmp($bedrooms, '3') === 0)
    $query .= " AND bedrooms = 3";
    else if (strcasecmp($bedrooms, '4') === 0)
    $query .= " AND bedrooms = 4";
    else if (strcasecmp($bedrooms, '+5') === 0)
    $query .= " AND bedrooms > 4";
    Last edited by lse123; 11-27-2007 at 09:00 AM.

  • #8
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What if prics vars : $min and $max can be 'any' too ?

  • #9
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Whenever you have a field that can be ANY value, when you create your query, DO NOT search for it in your WHERE clause. Just leave it out.

    If your user specifies an actual number for price, that's when you search the price field.

    -Shane

  • #10
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the case of PRICE field I can setup html form so when a user chose 'any' this corresponse to min value for MIN FIELD and this corresponse to max value for MAX FIELD ? I mean inside the html form(values that give form to php/mysql script) ....


  •  

    Posting Permissions

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