I have searched and read carefully, and for the life of me, I can't find a solution to what seems like a simple problem: I have a search form, and I want to be able to search a certain field in a mySql DB by more than one possible match. For example, assume I have a field called "city" where the name of one city can be stored, and I build the following form:

<input type="checkbox" name="city" value="New York">New York<br>
<input type="checkbox" name="city" value="Los Angeles">Los Angeles<br>
<input type="checkbox" name="city" value="Boston">Boston<br>

I want the user to, in effect, say "Find me someone who lives in either New York or Los Angeles". I should be noted that this form contains more than just a search for "city" - that is for several other variables at the same time.

My hearty thanks to anyone that can help.

  • Grayson

    change "city" to "city[]". that should return an array to you.

    Alternatively you could use a SELECT field with a SIZE greater than 1 and the MULTIPLE option. That will have the same effect.

      Okay, that much I'd read from other postings, but it doesn't work. How do I get the array into the actual sql statement?

      Where I determine the SQL query in my code, I used:
      $sql="SELECT * FROM data WHERE city LIKE '$city' AND housing LIKE '$housing'";

      I get no results, though I should. When I echo $sql, to see what it's sending out, I get:

      SELECT * FROM data WHERE city LIKE 'Array' AND housing LIKE 'apartment'

      So obviously I'm missing some intermediate step - the variable 'Array' should not appear as such in the SQL query. Something needs to be done to process tha contents of the array into a form that is compatible with an SQL query.

      But what exactly needs to be done?

        well you will have to either make multiple queries or, if you want to search for all of those at the same time, construct your query string:

        $sql = 'SELECT * FROM data WHERE housing LIKE "apartment" AND (';

        for($i = 0; $i < sizeof($city); $i++) {
        if($i > 0) {
        $sql .= ' OR ';
        }
        $sql .= "(city LIKE $city[$i])";
        }
        $sql .= ');';

          Thank you very much. The trick worked perfectly. In the end, I modified it a bit, so that I could keep my sql query in one piece. Before the sql query, I wrote this bit of code:

          $city1 .= "(";
          for($i = 0; $i < sizeof($city); $i++) {
          if($i > 0) {
          $city1 .= " OR ";
          }
          $city1 .= "(city LIKE '$city[$i]')";
          }
          $city1 .= ")";

          Then I wrote the sql query like so:

          $sql = "SELECT * FROM data WHERE $city1";

            7 months later

            Hi,

            I agree this is a brilliant piece of code. I'd tried to solve a similar problem for 3 days (and nights) until I spotted your snippet. Once again, greate many thanks.

            However, since I'm pretty new to PHP I don't get everything correctly.

            This is the code I wrote based on your suggestion. If you could comment on every line - just for my better understanding:-)))

            $parts = explode(" ",$keyword);
            $sql = "SELECT DISTINCT(e_mail) FROM test WHERE section LIKE '%$keyword%' OR (";
            for($i = 0; $i < sizeof($keyword); $i++) {
            if($i > 0) {
            $sql .= " OR ";
            }
            $sql .= "(section LIKE '$keyword[$i]')";
            }
            $sql .= ");";

            Regards,

            Witold

              Sure. First you need to understand what the goal is. We have a string of keywords, separated by spaces, and we want to find all of the (e-mails, in your case) which contain any of those words.

              So, the first thing we need to do is separate our long string of keywords into a more manageable array:

              $parts = explode(" ",$keyword);

              Now, we need to begin the query. One of two problems with your own code is that it assumes there is at least one search term. Instead of your line 2 I would write:

              $sql = "SELECT DISTINCT(e_mail) FROM test WHERE (";

              Now we need to add each keyword to the query as a search parameter. so we construct a for() loop that will iterate across the keyword array:

              for($i = 0; $i < sizeof($keyword); $i++) {

              Now, if this is not the first element, we need the word "OR" to separate the elements:

              if($i > 0) {
              $sql .= " OR ";
              }

              The other mistake you made in your code was leaving out the wildcard characters (%) in the second part of your query. We need to add each parameter in a LIKE clause so that we can use those characters and search for the keyword WITHIN the body of the e-mail:

              $sql .= "(section LIKE '%$keyword[$i]%')";
              }

              Now all that's left is closing the query string. Semicolons at the end of queries are only required when you are actually running SQL from the command-line.

              $sql .= ")";

              Hope that helps!

                2 months later

                Thanks for yhe examples.
                I'm working on a comparing page (www.webselect.nl) and now developping a page where people can check and compare products. It really helped me out!!
                Thanks,

                Jasper

                  a month later

                  Let us add another layer of complexity though. What if the original webpage has a javascript to select ALL the checkboxes. How would you write the JS to work? document.form[0].city[].checked = true will not work because the brackets are reserved for arrays in JS also.

                  Greg
                  (hi burbage!)

                    Write a Reply...