Building a query from multiple dropdowns

[86Stang]

I'm trying to build a query from a form that contains a keyword field as well as three dropdowns that are populated via three arrays.  Each dropdown has a default of "All".  I have the following code so far but that only includes one dropdown and it seems to me the amount of IFs to cover all three dropdowns using this method would be silly.

 

if ($city == "all")
{
	$qry = "SELECT * FROM table WHERE description = '$keywords'";						
}
else
{
	$qry = "SELECT * FROM table WHERE description = '$keywords' AND city = '$city'";
}

 

Any brilliant ideas on how to tackle this?

[vbnullchar]

can you post your form?

[86Stang]

<form name="search" action="search.php" method="post">
          <tr> 
            <td class="main"><b>Search:</b> 
              <input type="text" name="keywords" value="<? echo stripslashes($keywords); ?>" size="25">
                Paper: 
              <select name="paper">
              <?
		echo "<option value=\"all\">All Newspapers</option>\n";
		for ($i=0;$i<count($newspapers);$i++)
			{
				if ($newspapers[$i] == $paper)
					{
						echo "<option value=\"$newspapers[$i]\" selected>$newspapers[$i]</option>\n";
					}
				else
					{
						echo "<option value=\"$newspapers[$i]\">$newspapers[$i]</option>\n";
					}
			}
      ?> 
              </select>
                City: 
              <select name="cities">
              <?
	  	echo "<option value=\"all\">All Cities</option>\n";
		for ($i=0;$i<count($cities);$i++)
			{
				if ($cities[$i] == $city)
					{
						echo "<option value=\"$cities[$i]\" selected>$cities[$i]</option>\n";
					}
				else
					{
						echo "<option value=\"$cities[$i]\">$cities[$i]</option>\n";
					}
			}
      ?> 
              </select>
                
              <input type="submit" name="Submit" value="Search">
            </td>
          </tr>          
        </form>

[GingerRobot]

Okay. Well, the first thing i notice is that you're using keywords to search. Are you aware of how the mysql search works?

 

If a user were to put in say 'keyword1 keyword2' as their keywords, then , your current query would only return rows where the description was exactly 'keyword1 keyword2'. So, if a row had the descrition 'some words keyword1 keyword2', then it would not be returned.

 

For this kind of thing, you want to be at least using the LIKE operator, with wildcards. So, for example, your query would be:

$qry = "SELECT * FROM table WHERE description LIKE '%$keywords%'";

A % sign is the wildcard for any character, any number of times

An _ can be used to signify any character, but just once

 

Using the above example, this would return all rows where 'keyword1 keyword2' are contained anywhere within the description. However, it would still not return something like: 'keyword1 somewords keyword2'.

 

If that is an issue, you have a few options. Firstly, you could place the % sign between the keywords. However, this would still only help you if all the words were contained in the description. Or, you could search word by word, using an OR clause. Finally, the best results would be returned by using a full text search, which is more complex - i suggest you google. So, you'll need to decide what quality of search results you're happy with.

 

Back to the question:

 

The first thing to do is rename your select fields to use the name of the field in the database they relate to. For instance, cities should be renamed to city. You could then use this code:

 

<?php
$options = array('paper','city');//define an array of all the fields that select boxes exist for
$qry = "SELECT * FROM table WHERE description LIKE '%$keywords%'";
foreach($options as $v){
if($_POST[$v] != 'all'){
	$qry.= ' AND '.mysql_real_escape_string($v).' = '.mysql_real_escape_string($_POST[$v]);
}
}
echo $qry;
?>

 

Hope that helps.

[86Stang]

Thanks GingerRobot!!  I'm not using a database to populate the select boxes, if that's what you're meaning.  I put them each into their own array that is being included from another page.  Does that effect any of what you gave me?

[GingerRobot]

That doesn't affect anything i said. The first part of my response with with regard to how you are searching for keywords, which you may wish to reconsider. The second part gave you the code, and asked you to rename your select fields so they have the same name as the field in the database that they relate to e.g. your field is called city, not cities, so change:

 

<select name="cities">

To:

 

<select name="city">

 

 

 

 

[86Stang]

Ok, great.  I planned on using LIKE but after I got this whole select box thing figured out.

 

So I'm squared away on this (and sorry for being such a simpleton on this) - I am using two different arrays to populate two different select boxes.  Let's assume they are populated as follows:

 

$city = array('town1','town2','town3')

$paper = array('paper1','paper2','paper3')

 

In the code that you so graciously gave me, which of the following would be correct for the array assigned to the $options variable?

 

$options = array('city','paper')

or

$options = array('town1','town2','town3','paper1','paper2','paper3')

 

Yours Truly,

Coding with a Clue

[GingerRobot]

The code i gave you should work as is for the select boxes city, and paper (assuming you rename cities to city). I said you'll need to add to that, because you said in your original post that you had three dropdowns. In which case, you need to add an element which is the name of the select box(which should be same as the field name)

 

So, in short, the first $options was correct.

 

I think the best thing is just to try it - see what happens. you'll learn best that way.

 

 

[86Stang]

Tried it and I'm getting:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND city = ORDER BY date DESC' at line 1

 

[GingerRobot]

Can i see the updated code, including the form code?

[Psycho]

Just build your query incrementally instread of trying to do it all at once. Here is some mock code:

 

<?php

//Create the base query
$qry = "SELECT * FROM table";

//Create the WHERE strings
if ($_POST['keywords'] != '') {
   $where[] = "description LIKE '%{$_POST['keywords']}%'";
}
if ($_POST['city'] != '') {
   $where[] = " city = '{$_POST['city']}'";
}
if ($_POST['office'] != '') {
   $where[] = "office = '{$_POST['office']}'";
}
if ($_POST['department'] != '') {
   $where[] = "department = '{$_POST['department']}'";    
}

//Add the where clauses to the query
if (isset($where)) {
   $qry .= " WHERE " . implode(' AND ', $where);
}



?>