Searching Question

[Accurax]

Hi guys, another question im afraid,

I have a form that allows people to search my database.

The form has 20 fields that could potentially be selected, this information is then POSTed to a search results page which assigns the information to php variables and then queries the database.

Now, my querie uses AND statements because id like the results to only return what the user selects and not OR's.

My problem is that if the user does not fully complete the search form, and therefore passes empty strings to the results page, the query has a hissy fit and returns no results.

While the code is in effect correct, as there are no entries in my database with empty fields, I would like to figure out a way to ignore empty strings in my query.

so, assume $_POST['name']; is empty and $_POST['pet']; is equal to polar bear

[code]
$name = $_POST['name'];
$pet = $_POST['pet'];

$user_query = "SELECT * FROM mytable WHERE name='$name' && pet='$pet' ";

$result = mysql_query($user_query)
or die ("no can do");
[/code]

The above query would search the database for rows where the name was equal to "" and the pet was equal to "polar bear"... and it would therefore return no results.

Is there anyway of catering for this without writing thousands of if statements & specific queries for every eventuality ??

[taith]

mysql does have a wildcard operator... %
[code]
$name = $_POST['name'];
$pet = $_POST['pet'];

$user_query = "SELECT * FROM mytable WHERE name='$name' && pet='%$pet%' ";

$result = mysql_query($user_query) or die ("no can do");
[/code]

that much being said... if $pet="bear" it'll find "polar bear", "bear", "grizzley bear", etc...
but if you $pet="polar bear" it'll find "polar bear", and not bear, or grizzley bear

[Accurax]

But what if pet was empty ? ... wouldnt it just return everything?

[taith]

yes it would :-)but if you add a simple if(!empty($_POST[pet])){ rest of code in here }

[redbullmarky]

might be worth pointing out that, as far as I know, && is not suitable for SQL queries. You need to use AND within your query.

[taith]

no... &&/|| works just as well as AND/OR

[redbullmarky]

[quote author=taith link=topic=122137.msg503333#msg503333 date=1168626510]
no... &&/|| works just as well as AND/OR
[/quote]
i stand corrected ;)

[Accurax]

[quote author=taith link=topic=122137.msg503330#msg503330 date=1168626361]
yes it would :-)but if you add a simple if(!empty($_POST[pet])){ rest of code in here }
[/quote]

where would that go ?... inside the query?

[Eddyon]

Before the query, and do an else { query goes here }

[taith]

[code]
if(!empty($_POST['pet'])){
$name = $_POST['name'];
$pet = $_POST['pet'];
$user_query = "SELECT * FROM mytable WHERE name='$name' && pet='%$pet%' ";
$result = mysql_query($user_query) or die ("no can do");
}else echo 'You must enter a specific search.';
[/code]

[Eddyon]

Ah taith is right, i overlooked the ! which is a "not".

[Accurax]

[quote author=taith link=topic=122137.msg503342#msg503342 date=1168627150]
[code]
if(!empty($_POST['pet'])){
$name = $_POST['name'];
$pet = $_POST['pet'];
$user_query = "SELECT * FROM mytable WHERE name='$name' && pet='%$pet%' ";
$result = mysql_query($user_query) or die ("no can do");
}else echo 'You must enter a specific search.';
[/code]
[/quote]

Thats kind of what i was thinking before.... but the real situatiion has 20+ fields in the search form .... wouldnt this way result in 1000's of different connitations of the if else statement needing to be written ?

[taith]

yup... thousands of if()elesif()'s... or 1 foreach()... ;-)

[code]
$name = $_POST['name'];
$pet = $_POST['pet'];
foreach($_POST as $k=>$v) if(!empty($_POST[$k]))$query.=" && `$k`='%$v%'";
if(empty($query)) die("You must enter some fields");

$user_query = "SELECT * FROM mytable WHERE name='$name' $query";
$result = mysql_query($user_query) or die ("no can do");
[/code]
or something to that effect :-)

[Accurax]

your joking? ..... I can do all of that with 1 foreach ?? ... i think i love you taith

[taith]

i jest not... $_POST holds all the fields from your previous form... so... why not use it as an array and build your query that way?

[taith]

however, i do suggest that you unset($_POST[]) the vars that you dont want in the query, before the foreach()