SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Checkbox Hell

  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Swansea, Illinois, USA
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Checkbox Hell

    OK, gang, so here's where I'm at: Trying to display items from MySQL database sorted by type of business (biz[]) and location (loc[]). Got some help here that got me this much:

    // connection to db assumed

    $bizConditional = "";
    $arraySize = count($biz) - 1;
    for ($i = 0; $i <= $arraySize; $i++) {
    if ($i == $arraySize) {
    $bizConditional .= "biz_type LIKE '%$biz[$i]%'";
    } else {
    $bizConditional .= "biz_type LIKE '%biz[$i]%' OR";

    }

    }

    $result = @mysql_query ("SELECT * FROM table_name WHERE $bizConditional AND city LIKE '%$loc%' ORDER BY link ASC");

    if ($result) // this prints out the query results in a table


    So, the first problem is that it only works with one kind of business checked. It will display 'restaurants' for example, but not 'restaurants' AND 'tires.' Also, there is no effect with the location checkboxes, perhaps because I haven't defined the loc[] as an array and given it a definition?

    Also, what is the code to display ALL BUSINESSES? Would the checkbox name still be loc[]?

    It's got me stumped, so I figure I'd jangle you genuises who eat and breathe this stuff. Thanks mucho.

  2. #2
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    Can you do us a favour and just wrap your code snippets with PHP tags? That is
    PHP Code:
     //code here 
    You'll see what I mean if you click the quote button at the bottom of this post.

    yours, Marcus.
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  3. #3
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $bizConditional '';
    $sep '';
    foreach (
    $biz as $oneBiz) {
        
    $bizConditional .= "{$sep}biz_type LIKE '%{$oneBiz}%'";
        
    $sep ' OR ';
    }
    ... 
    To get it over all locations you can remove the "LIKE %$loc%" of the query or put $loc empty (LIKE '%%' is LIKE everything)
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  4. #4
    SitePoint Enthusiast konky2000's Avatar
    Join Date
    Mar 2003
    Location
    Oakland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have found it necessary to use parentheses when building these kinds of conditional queries.

    In other words you want something like this:

    Code:
      SELECT *
        FROM phonebook
        WHERE ( (phonebook.biz = 'restaurant' ) || (biz.phonebook = 'car repair' ) || (biz.phonebook = 'florist') )
        AND phonebook.city = 'London';
    Also, if you have less than 32 different business types, there is a very elegant solution using BINARY numbers, but I would guess you might have more than 32 different types.
    Konky 2000 Collections - Japanese stickers and floaty pens

  5. #5
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mothman52
    So, the first problem is that it only works with one kind of business checked. It will display 'restaurants' for example, but not 'restaurants' AND 'tires.' Also, there is no effect with the location checkboxes, perhaps because I haven't defined the loc[] as an array and given it a definition?

    Also, what is the code to display ALL BUSINESSES? Would the checkbox name still be loc[]?
    Sounds like your database is fine, but your query is broke.

    Have your program print out the SQL query for "restaurants" and "tires", or whatever, and show us what you generate.

    And "all businesses" would eliminate the entire filter:

    Code:
     SELECT * FROM table WHERE city LIKE '%$loc%' ORDER BY link ASC
    =Austin

  6. #6
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Swansea, Illinois, USA
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks all....

    Re: Konky 2000

    So you're saying that if I have let's say 50 different buisinesses, I have to list 50 different instances of (biz[] = "tires") || (biz[] ="restaurants'), etc in my SELECT statement?
    Is that it? I'm surprised there's no more elegant way of doing that. If I define biz[] as an array, can't I implode the values into a string and present them to the database that way?

    Re: Austin Hastings

    I understand the SELECT statement you gave me as getting everything from the database, but how do you use your SELECT statement along with the other one which will return only the items selected? Do I stick it in an ELSE statement? Am I on the right track?
    Last edited by mothman52; Jan 29, 2005 at 10:51. Reason: Needed to ad to it

  7. #7
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mothman52
    Thanks all....

    Re: Konky 2000

    So you're saying that if I have let's say 50 different buisinesses, I have to list 50 different instances of (biz[] = "tires") || (biz[] ="restaurants'), etc in my SELECT statement?
    Is that it? I'm surprised there's no more elegant way of doing that. If I define biz[] as an array, can't I implode the values into a string and present them to the database that way?
    Mothman52,

    It depends on whether you need "LIKE" or if you can live with "=".

    You could use "WHERE table.field IN ('a', 'b', 'c')" to build a much smaller expression.

    Re: Austin Hastings

    I understand the SELECT statement you gave me as getting everything from the database, but how do you use your SELECT statement along with the other one which will return only the items selected? Do I stick it in an ELSE statement? Am I on the right track?

    I'd recommend building a subexpression that might contain the empty string:

    PHP Code:
    $city_constraint "table.city LIKE '%$loc%'";
    $type_constraint "";

    if (
    count($biz) != 0)
    {
      
    /* Note: there are single and double quotes mixed together here. */
      
    $type_constraint "table.field IN ('"
           
    join("', '"$biz)
           . 
    "') AND ";
    }

    $sql "SELECT * FROM table WHERE " $type_constraint
              
    $city_constraint $rest_of_statement
    If you have no "biz type" constraints in $biz[], then you get something like:
    Code:
    SELECT * FROM table WHERE table.city LIKE '%$loc%' ...
    but if biz has "restaurant" and "cars" in it, you get:
    Code:
    SELECT * FROM table WHERE table.field IN ('restaurant', 'cars') AND table.city LIKE '%$loc%' ...
    Works for me.

    =Austin

  8. #8
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Swansea, Illinois, USA
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Austin, I'll give it a try.

    Although will this coding work with something like 50 different businesses and maybe 20 different locations?


Bookmarks

Posting Permissions

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