Query &quot;All&quot; Question

Query "All" Question

janey

Hi,

Can anyone give me a little info on or point me to a good tutorial/article regarding queries (PHP/MySQL) on a website. Here is what I am having trouble with:

I have a website that lists abandoned dogs and cats needing adoption. When a user comes to the site, I allow them to query the database to search for an animal to adopt. Let’s say my fields are: size, breed, age, sex and temperament. All of these fields are queried with dropdown lists, such as size = large, medium, small.

What I cannot figure out is how to add an “ALL” to the dropdowns so the user only has to select the criteria that matters to them. For example, if they select a specific breed, age, sex and temperament, but they don’t care about size, I want them to be able to select “all” or “any” and see all sizes that meet the other parameters.

Can someone point me in the right direction? Thanks!!

shu

Your dropdown:
<select name="size">
  <option value="">ALL</option>
  <option value="small">Small</option>
  <option value="medium">Medium</option>
  <option value="large">Large</option>
</select>,
etc.

//Your Query
$query = "SELECT * FROM animals WHERE";
if ($size != "") {
  $query .= " size='$size' AND";
}
//Do the same for all other dropdown lists
$query = eregi_replace(" AND$", '', $query);
$query = eregi_replace(" WHERE$", '', $query);

janey

Thanks so much that worked perfectly!

I have another question about dropdowns.

I am inserting data into a mysql db via an html form and using php. I would like to use a multiple select listbox for one portion, but I cannot figure out how to get it to work. Any ideas?

Thanks!!!

cgraz

If you're using one of those selects where you hold ctrl down to select more, did you use [] in the name? that will convert the values into an array so you get all the results, not just the last one. Here's an example

<select name="form[]" multiple>

<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>

</select>

If all those were selected, you could access the values using

$POST['form']['0']; // value is 1
$POST['form']['1']; // value is 2
$_POST['form']['2']; // value is 3

returns an array $form (use $_POST if register globals is off), and to get the values of form which is an array, use 0, 1, 2, etc. If they select 1 and 3, you still access it using

$POST['form']['0']; // value is 1
$POST['form']['1']; // value is 3

The 0, 1, and 2 don't necessarily match up.

$_POST['form']['0'] could have a value of 3 if they only selected 3. Just something to keep in mind.

Cgraz

janey

Thanks, Cgraz.

I think I see where you are going, but I'm still having a little trouble. I haven't worked much with arrays, yet, so please bear with me.

When I make the select box an array, how do I write the insert so that the actual value is inserted into the table rather than the word "array"?

Thanks again!!

cgraz

foreach($_POST['form'] as $value) {
   $query = mysql_query("INSERT INTO your_table (field_name) VALUES('" . $value . "')");
}

Cgraz

janey

Thanks again! I am still having a little trouble. When I use the code below, I get the following error:

Error in query: 1. You have an error in your SQL syntax near '1' at line 1

Here is the code:

 <? 
include("conf.php3");
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
foreach($_POST['popselect'] as $value) {
 $query = mysql_query("INSERT INTO popserved (served) VALUES('" . $value . "')");
}
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
echo "<p><font size=2 color=#FF0000><br><br>The information was added sucessfully.</font>";
mysql_close($connection);	
}
?>

cgraz

You don't need this line

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

The query gets executed inside of the foreach loop.

$query = mysql_query. . .

Cgraz

janey

Does anyone know how the query "all" above (posted by Shu) work if you had a more complex query.

The original query was:

$query = "SELECT * FROM animals WHERE";
if ($size != "") {
  $query .= " size='$size' AND";
}
//Do the same for all other dropdown lists
$query = eregi_replace(" AND$", '', $query);
$query = eregi_replace(" WHERE$", '', $query);

But what if there were actually more that one fields to be searched, such as size and color?

Ordinarily, the query might be something like:

SELECT * FROM animals WHERE (size= '$size' and color= '$color')

So, how what would the syntax of the query be if you wanted "size" to return "all" if left blank??
Thanks!

cgraz

You'd check if size had a value before using it in your query, or else, it would always return all the records where size="" if size has no value.

Cgraz

janey

Cgraz, I understand the concept of what you are saying, but I've tried it a few different ways, and I always get zero results returned even though I know there are items in the db that would fit the query.

Could you possibly show me an example of how you would do it, or do you know of a reference you could point me to?

As always, thanks so much for your help!

cgraz

I'm guessing the person types there search terms in a form, meaning you'd have access to an array called $_POST that contained all the values. So here's an example using only one script. You can divide this into two if you'd like.

<?

if (isset($_POST['submit'])) { // if the form was submitted

$query = "SELECT * FROM your_table";

foreach($_POST as $key => $value) {
   if (($value == "") || ($value == "submit")) {
     // do nothing
   } else {
     if ($query == "SELECT * FROM your_table") {
        $query .= " WHERE $key = '$value'";
     } else {
        $query .= " AND $key = '$value'";
     }
   }
}

echo $query;

} else { // form was not submitted. Display form to visitor

?>

<form method="post" action="<?= $PHP_SELF ?>">

<select name="field_1">
<option value="">Select</option>
<option value="value1">value1</option>
<option value="value2">value2</option>
<option value="value3">value3</option>
</select><br><br>

<select name="field_2">
<option value="">Select</option>
<option value="value1">value1</option>
<option value="value2">value2</option>
<option value="value3">value3</option>
</select><br><br>

<select name="field_3">
<option value="">Select</option>
<option value="value1">value1</option>
<option value="value2">value2</option>
<option value="value3">value3</option>
</select><br><Br>

<input type="submit" name="submit" value="submit">

</form>

<?

}

?>

the name of each select would need to be the name of the fields in your mysql database (such as size), and the values would need to be the values that each of those fields has. Try this code out and you'll see how it works. When you get it working and printing the right query, then change echo $query to

$result = mysql_query($query) or die(mysql_error());

Cgraz

janey

Cgraz,

Thanks again!

I was way off in my code, and you make it look so easy.

cgraz

Well you had the right idea; took me a while to figure that out the first time. Loops are great tools. If you haven't used them much, play around with them. They come in VERY handy.

Cgraz