Searching within multiple fields with checkboxes

[xKillswitchx]

Ran into a horrible problem I just can't find a work around to. I am new to searching mysql with PHP.

Basically, I have a script that allows the user to search based on the manufacturer or a car, the model of a car, price, vin, year, etc in a database. I have a checkbox for each of these.

I had it setup basically like so...

if( isset( $_POST['manufacturer'] ) ) {
$query .= "\n`manufacturer` LIKE '%$search%'";
}

I f the post was set I just appended to the query string. didnt really work out because of the ¦¦ used in the rest of them...

Can anyone help out in giving me some guideance on searching multiple fields like this? I don't need a straight answer, just wanna know where to look, maybe tutorial or guide.

[coopster]

Many default MySQL set ups will not handle that form of concatenation. You need to either set the query mode to ANSI or change the way you concatenate using their CONCAT operator.

At least, that is my first guess as to the nature of the failed attempt ;)

[rocknbil]

More info is probably needed, as in exactly what is going wrong. A couple observations:

$query .= "\n`manufacturer` LIKE '%$search%'";

The newline is not needed. It's pretty common to build the select on the fly, for example:

if isset($_POST['value1'])) {
$where .= " (field1='$_POST['value1']' or field2='$_POST['value1']')";
}

if isset($_POST['value2'])) {
if (isset($where)) { $where .= " or"; }
$where .= " (field1='$_POST['value2']' or field2='$_POST['value2'])'";
}

$select = "select * from table";
if (isset($where)) { $select .= " where $where"; }
$select .= " order by field1,field2";

Note the parentheses, which are not important in an AND but become very important in an OR:

didnt really work out because of the ¦¦ used in the rest of them...

I'm guessing this could be part of the problem. If you do

... or field1='$value1' or field2='$value2'

it may incorrectly match on one or the other, or both, or previous or conditions, when you want either or for the match. you need

... or (field1='$value1' or field2='$value2')

Last,

`manufacturer` LIKE '%$search%'";

If you have the ability, these should be numeric queries. It makes the search a lot faster and eliminates a lot of possibility for error, as well as injection:

<input type="checkbox" name="mfg_1" value="1"> Ford

if ((isset ($_POST['mfg_1'])) && preg_match('/^\d+$/',$_POST['mfg_1'])) {
$mfg = $_POST['mfg_1'];
if ($where) { $where .= " or"; }
$where .= " manfacturer = '$mfg'";
}

Example only, as you'd probably dynamically loop through the checkbox values and cleanse any input data before providing it to your database.