HELP checkbox query nightmare

Problems with filter query and checkboxes

[jonathonhuntley]

I still actually have some major issues in this query. Basically, I have a database containing one table that has a multiple columns. My form brings back a multiple-select checkboxes for each column as their own individual set so they can be processed seperately for use in a search engine, i.e. Column 1 is [box] and Column 2 is [box1].

Possibly I have gone for something really quite difficult or maybe I'm overlooking the obvious but I want several of these boxes full of checkboxes for each column and distinct variable to act as the filter. I seem to be having trouble formulating a way to counter all different variables as they can tick whatever they like.

Does anyone have a way of doing this? I want it to be like ebays checkbox filter!

This is my code so far but it only works if they select something:

$connect = mysql_connect("localhost","root","") #14
or die ("couldn't connect to server");
mysql_select_db("test");

print_r($_POST);

$box=$_POST['box'];

IF(!isset($_POST['box'])){

$sql = " ";

echo $sql;

}
else
{
$sql = " `subject_one` =
";
$or="";
while (list ($key, $val) = each ($box) )
{
$sql = "$sql ${or}'${val}'";
$or="OR `subject_one` =";
}
$sql = " ( $sql ) ";

$box1=$_POST['box1'];

IF(!isset($_POST['box1'])){

$sql1 = " ";

echo $sql1;

}
else
$sql1 = " `subject_two` = ";

$or2="";
while (list ($key, $val) = each ($box1) )
{
$sql1 = "$sql1 ${or2}'${val}'";
$or2="OR `subject_two` = ";
}
$sql1 = " ( $sql1 ) ";

echo "</br>$sql";
}
echo "</br>$sql1";

$sql2 = " SELECT *
FROM `table`
WHERE ";
$sql3 = "$sql2 $sql AND $sql1";

echo "</br>$sql3";

$query = $sql3;
$result = mysql_query($query)
or die ("Couldn't execute query");

Thank you in advance for any suggestions

[panchilenito]

If your objective is to get data even when your users do not select any box the I will add an always true condition at the end...
p.e:
SELECT * FROM table
WHERE (HERE ALL YOUR CONDITIONS IF THEY SELECT SOMETHING)
AND 1 = 1;

[panchilenito]

... forget my previous example... doesn't work... was a brain "f a r t" ...

[rocknbil]

There's a little trouble in your approach. You don't know what your search parameters are going to be, and they can change at any time, and thinking it through top-down "as you need it" like this is going to be a nightmare, and a worse one when you come back and ask yourself what you were thinking. :-) Here is how I organize stuff like this:

- build a WHERE based on input.
- if the where exists, use it, otherwise what have you got? Select * from table.

The following approach is not for huge scripts, but similar methods could be applied for large scripts. It's for an example. Consider three arrays:

// These are your actual form field names:
$valid_inputs = Array (
'this',
'that',
'the_other',
'this_other',
'that_other'
);

// This maps the form field names to table fields. You should
// never use form fields the same as your table field names.
$tblfields = Array (
'this' => 'this_tablefield',
'that' => 'that_tablefield',
'the_other' => 'the_other_tablefield',
'this_other' => 'this_other_tablefield',
'that_other' => 'that_other_tablefield'
);

//This maps what kinds of comparisons I want for each field
// when I build my select.
$comp_values = Array (
'this' => '=',
'that' => ' like ',
'the_other' => ' < ',
'this_other' => '=',
'that_other' => '>'
);

These arrays pull all the hard coded values out of your selects and makes it expandable. Usually located at top of the script, or some configuration include, whatever. Doesn't matter **what** type of input is in the form, checkbox, select, text.

$where=$select=NULL; // To squelch any undefined errors

foreach ($valid_inputs as $v) { 
 if (isset($_POST[$v]) and $_POST[$v] != '')) { 
 $input = mysql_real_escape_string($_POST[$v]); 
 $input = (preg_match('/like/i',$comp_values[$v]))?"'%$input%'":"'$input'"; 
 // If where is defined, you need AND 
 if ($where) { $where .= ' and'; } 
 $where .= " $tblfields[$v] $comp_values[$v] $input"; 
 } 
} 

So I've built a where using the above arrays, which can have any of the following clauses:

this='some value'
that like '%some value%'
the_other < '256'
this_other = ' some value'
that_other > '125'

Now the select.

$select = "select from table";
if ($where) { $select .= " where $where"; }

Let's make it even better.

$sql = get_select();

function get_select () {
// globals used only for exercise
global $valid_inputs,$tblfields,$comp_values;
// Take all of the select-building code above and put it here
return $select;
}

So now your entire program is is this:

// arrays here

$sql = get_select();
$result=mysql_query($sql);
if (mysql_num_rows($result) > 0) {
// output results
}
else { echo "<p>No results to display</p>"; }

// get_select function here

Some things to note:

- What you do in your form has a large bearing on your coding style. For example, always have the first item in a select empty, always have a default radio button checked. etc. Be mindful if this type of stuff and your coding will always be reliable. How this applies to your checkboxes? Put a value in the element.

- Note the spaces in the concatenation:
if ($where) { $where .= ' and'; }

- I never paid attention to it until I got sick of echoing selects to see whereImissedAspace. Pick one, always put the space before or after concatenating.

- Posted as an exercise in logic, typed on the fly, this is not working code but the ideas here should help you sort it out.

-- Does not include order by or limit clauses, but look ahead - if you have thousands of results, you will need some form of pagination. It's my opinion that pagination and setting irder by/where go hand in hand. For sake of simplicity, I do

list($order,$limit) = my_limit_function();

$select = "select from table";
if ($where) { $select .= " where $where"; }
$select .= " $order $limit";

[jonathonhuntley]

Cheers rocknbil,

Much appreciated! As I am a newbie on this I was aware that my code was probably inefficient, patchy and cumbersome and thought there are probably simpler ways of expressing the end result. I realised also with the volumes of data I eventually want to put into this database, the search engine would have to be fairly efficient in order not to stall, crash or take an ridiculous amount of time loading.

I will attempt this over the weekend, hopefully to positive results but I'm sure I'll be asking again for this invaluable advice.

Many thanks,
Jon