advanced search page queries

[only1perky]

Hi guys,

 

I currently have an advanced search page containing 50+ checkboxes. This posts to the results page and queries the database using:

mysql_select_db($database_connuser, $connuser) or die;
$find = implode("=1 AND ",$_POST['criteria'])."=1";
$query = "select * from drinking where $find order by venue asc";

 

This is working perfectly and returns the results according.

 

I would now like to add a few more fields to the search (3 text boxes).

 

How would i create the query(s) to incorporate these new fields. The text fields will be as follows:

Venue

Street

Area

 

All these fields contain a value in the database so are not null in any cases.

 

I know I could create a separate query for each possible combination of events, e.g. venue is set, street is set, area is not set and criteria[] is set.

But this will soon require an extremely large number of queries if new fields are added.

 

So my question is what is the easiest way to complete this bearing in mind that I may add new fields in the future.

 

Hope you can help me out.

 

Thanks.   

[haku]

Need more code.

[only1perky]

What do you need?

 

Here is what my form will look like. (simplified for here)

 

<form name="advancedsearch" action="search.php" method="post" >

    Venue Name: 	<input name="venue" id="venue" type="text" />
    Street: 	<input name="street" id="street" type="text" />
    Area: 	<input name="area" id="area" type="text" />
    
Accomodation: 	<input name="criteria[]" type="checkbox" id="accomodation" value="accomodation" />
Smoking Area:	<input name="criteria[]" type="checkbox" id="designated_smoking" value="designated_smoking" />
Near Station:	<input name="criteria[]" type="checkbox" id="near_station" value="near_station" />
Near Bus: 		<input name="criteria[]" type="checkbox" id="near_bus" value="near_bus" />
    etc
    etc
    etc
    etc
    etc

 

Is there any other code you will need?

 

[uniflare]

ok i would use arrays, like youve done on the checkboxes.

 

eg:

 

in the form you use:

 

<input type="text" name="form[username]" value="somevalue">

 

this way you can do this:

 

mysql_select_db($database_connuser, $connuser) or die;
$find2 = array(); // rid us of those e_notice's.
foreach($_POST['form'] As $Key=>$Value){
   $find2[] = " AND `".$Key."`='".$Value."'";
}
$find = implode("=1 AND ",$_POST['criteria'])."=1";
$query = "select * from drinking where $find.$find2 order by venue asc";

[only1perky]

Ok so my form now looks like this:

 

<form name="advancedsearch" action="search.php" method="post" >
   
    Venue Name:    <input name="form[venue]" id="venue" type="text" />
    Street:    <input name="form[street]" id="street" type="text" />
    Area:    <input name="form[area]" id="area" type="text" />
   
   Accomodation:    <input name="criteria[]" type="checkbox" id="accomodation" value="accomodation" />
   Smoking Area:   <input name="criteria[]" type="checkbox" id="designated_smoking" value="designated_smoking" />
   Near Station:   <input name="criteria[]" type="checkbox" id="near_station" value="near_station" />
   Near Bus:       <input name="criteria[]" type="checkbox" id="near_bus" value="near_bus" />
    etc
    etc
    etc
    etc
    etc

 

And my query is now:

 

mysql_select_db($database_connuser, $connuser) or die;
$find2 = array(); // rid us of those e_notice's.
foreach($_POST['form'] As $Key=>$Value){
   $find2[] = " AND `".$Key."`='".$Value."'";
}
$find = implode("=1 AND ",$_POST['criteria'])."=1";
$query = "select * from drinking where $find.$find2 order by venue asc";


$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

 

Have I got this correct because I now get the errors:

 

Warning: implode() [function.implode]: Invalid arguments passed in /pages/drinking/search.php on line 137

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /pages/drinking/search.php on line 142

[uniflare]

hmm, the implode shud of been unchanged.

 

try changing the name of the form array to something like data_arr, eg:

 

form[venue]

to

data_arr[venue]

etc

[only1perky]

No Change!

 

Form is now:

<form name="advancedsearch" action="search.php" method="post" >
   
    Venue Name:    <input name="data_arr[venue]" id="venue" type="text" />
    Street:    <input name="data_arr[street]" id="street" type="text" />
    Area:    <input name="data_arr[area]" id="area" type="text" />
   
   Accomodation:    <input name="criteria[]" type="checkbox" id="accomodation" value="accomodation" />
   Smoking Area:   <input name="criteria[]" type="checkbox" id="designated_smoking" value="designated_smoking" />
   Near Station:   <input name="criteria[]" type="checkbox" id="near_station" value="near_station" />
   Near Bus:       <input name="criteria[]" type="checkbox" id="near_bus" value="near_bus" />
    etc
    etc
    etc
    etc
    etc

 

And query:

 

mysql_select_db($database_connuser, $connuser) or die;
$find2 = array(); // rid us of those e_notice's.
foreach($_POST['data_arr'] As $Key=>$Value){
   $find2[] = " AND `".$Key."`='".$Value."'";
}
$find = implode("=1 AND ",$_POST['criteria'])."=1";
$query = "select * from drinking where $find.$find2 order by venue asc";


$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

 

Any ideas?

 

UPDATE:

 

I have just noticed that I get the implode error only if I don't tick a check box.

If i enter values in the text boxes and then tick a check box I just get the:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in bla bla bla file.

[uniflare]

This will fail if you dont select at least 1 checkbox. (implode failure).

 

to fix this:

$and_ornull = (count($_POST['criteria']) >= 1)? " AND" : "";
$find2[] = $and_ornull." `".$Key."`='".$Value."'";

 

Basically you only need the AND if there is at least 1 other search or 'where' term, so if any checkboxes are selected, then add the AND.

----------

 

The implode function will still fail like this.

 

You should put all the search elements into a single array (Add criteria to find2), then implode them all at once to fix this.

[only1perky]

So just to clarify my form should now look like:

 

Venue Name:    <input name="criteria[]" id="venue" type="text" />
    Street:    <input name="criteria[]" id="street" type="text" />
    Area:    <input name="criteria[]" id="area" type="text" />
   
   Accomodation:    <input name="criteria[]" type="checkbox" id="accomodation" value="accomodation" />
   Smoking Area:   <input name="criteria[]" type="checkbox" id="designated_smoking" value="designated_smoking" />
   Near Station:   <input name="criteria[]" type="checkbox" id="near_station" value="near_station" />
   Near Bus:       <input name="criteria[]" type="checkbox" id="near_bus" value="near_bus" />
    etc
    etc
    etc
    etc
    etc

 

Is that correct?

 

And I'm afraid you have lost me on what to do with the query. This is only my second week working with php so if you could baby proof things it would help a lot.

 

[uniflare]

No.

 

You need to get a better understanding, ill give you some tips to work on your own (im tired);

 

put this at the top: (it will show you what is being posted via the form).

 

echo("<pre>"); print_r($_POST); echo("</pre>");

 

Echo the query on the page,

 

$query = "select * from drinking where $find.$find2 order by venue asc";
echo($query);

 

this way you will see how the query forms up to the post data.

===

 

SQL and PHP are different languages (ofc), learning both at the same time can be difficult, but keep at it!

 

u dont need to change the form at all - or shudnt need to.