...

View Full Version : searching the db with optional values



simstar
03-12-2007, 08:01 PM
I need to create a program that will allow the user to search the mysql database using around 15 values. The values are optional.

It is a gardening site, so I want users to be able to search for only 'green' and skill level '1' plants for example.

There are about 15 optional values that they can choose from.

I know how to search the db for multiple values using SQL queries but Im stuck when it comes to searching the database if the user for varying numbers of values depending on whether they entered a value in the HTML search form.

So I only want the SQL to search for values which have been inputted by the user in the HTML form.

Does anyone have any helpful tips ?
Thanks

devinemke
03-12-2007, 08:55 PM
when constructing your SQL string simply leave out the fields that the user left empty in the form

simstar
03-12-2007, 09:02 PM
when constructing your SQL string simply leave out the fields that the user left empty in the form
How do I do that?

I only know how to write simple SQL statements...I dont know how to create ones using php, depending on what the user has entered.

There has got to be a more simple way than writing 100 -150 SQL statements for each possible combination

Fumigator
03-12-2007, 09:16 PM
The ideal thing to do is to use "fulltext" indices on the fields that you deem searchable. Depending on how your database is designed, however, this could lead to some overhead that may impact performance.

Anyways... you can get acquainted with fulltext indexing in the MySQL manual here (http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html).

devinemke
03-12-2007, 10:15 PM
<?php
if (!isset($_POST['submit']))
{
echo '
<form action="" method="POST">
first name: <input type="text" name="fields[first_name]" value=""><br>
last name: <input type="text" name="fields[last_name]" value=""><br>
email: <input type="text" name="fields[email]" value=""><br>
<input type="submit" name="submit" value="submit">
</form>
';
}
else
{
$sql_array = array();
foreach ($_POST['fields'] as $key => $value)
{
$value = trim($value);
if ($value) {$sql_array[] = $key . " = '" . $value . "'";}
}

if ($sql_array)
{
$sql = 'SELECT * FROM table WHERE ' . implode(', ', $sql_array);
echo $sql;
}
else
{
echo 'all fields blank';
}
}
?>

simstar
03-13-2007, 05:01 PM
Thanks for the code, it works, but I am only used to using


while($row = mysql_fetch_array( $result )) {

to display the data.. is there another method to display the sql results?

cheers for the help :thumbsup:

Fumigator
03-13-2007, 10:33 PM
That method should still work...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum