building a mysql query with checkboxes

[wizardofchips]

is there a way to use a form and check boxes to control which fields this line from a script im working on chooses

$result = $pafiledb_sql->query($db, "SELECT * FROM $db[prefix]_files WHERE file_name LIKE '%$string%' OR file_desc LIKE '%$string%' OR file_creator LIKE '%$string%' OR file_longdesc LIKE '%$string%' OR file_version LIKE '%$string%'", 0);

in this query there are 5 fields being queried.
can a form and check boxes be used to build the query so that one, some or all of these fields are queried?

dont know enough about php so any help would be great...

thanks

[Birdman]

Sure, it can be done! There are a few ways, but I think making the checkboxes an array would be the best.

<input type="checkbox" name="fields[]" value="file_name" />
<input type="checkbox" name="fields[]" value="file_desc" />
and so on...

Then in the script, you can build the query with a loop:

$num_fields = count ($_POST['fields']);

for ($i = 0; $i < $num_fields; $i++){
if ($i < ($num_fields-1)) $where = $fields[$i] . " LIKE '%" . $string% . "' OR ";
else $where = $fields[$i] . " LIKE '%" . $string% . "'";
}

Now you have the WHERE part of the query in a variable. Just stick it in the main query now:

$result = $pafiledb_sql->query($db, "SELECT * FROM $db[prefix]_files WHERE $where", 0);

And there you have it! By the way, welcome to Webmaster World!

Birdman

[wizardofchips]

great....! thanks so much! Ive been looking forever to find a script that would do what you have shown me! Finally i found one that I understood enough to know what neeeded to be modified, now I have the modifications thanks to you! Cant wait to try it.. I let you know how it worked out.

[wizardofchips]

Nothing is ever that easy... especially since Ive had only minimum experience with php... Im not sure where to put some of the stuff you showed me... giving me parse errors... maybe i should give you the script in its entirety... I do know where the query goes and ive inserted it into script... Lost on where the array should go and where the loop goes

if ($search == "do") {
$string = strip_tags($string, '<a><b><i><u>');
$locbar = "<a href=\"pafiledb.php\" class=\"small\">$config[1]</a> :: <a href=\"pafiledb.php?action=search\" class=\"small\">$str[search]</a> :: $str[results] $string</a>";
if ($logged == 1) {
adlocbar($locbar, $user, $str);
adminlinks($str);
} else {
locbar($locbar);
}
$result = $pafiledb_sql->query($db, "SELECT * FROM $db[prefix]_files WHERE $where", 0);
$numhits = mysql_num_rows($result);
?>
<table width="100%" border="1" cellpadding="2" cellspacing="0" class="headertable" bordercolor="#000000">
<tr><td width="100%" colspan="2" class="headercell"><center><b><?php echo $str[search];?></b></center></td></tr>
<?php
if ($numhits == 0) {
?>
<tr>
<td width="5%" align="center" valign="center" class="datacell"><img src="styles/<?php echo $config[11];?>/images/error.gif" border="0"></td><td width="95%" class="datacell"><?php echo $str[nomatches];?> <b><?php echo $string;?></b></td></tr>
<?php
} else {
?>
<tr><td width="100%" class="datacell" align="center" colspan="2"><?php echo "$numhits $str[matches] <b> $string";?></b></td></tr>
<?php
while ($r = mysql_fetch_object($result)) {
if ($r->file_posticon == "none" or $r->file_posticon == "none.gif" or empty($r->file_posticon)) {
$posticon = "&nbsp;";
} else {
$posticon = "<img src=\"images/posticons/$r->file_posticon\">";
}
echo "<tr><td width=\"%5\" align=\"center\" class=\"datacell\">$posticon</td><td width=\"95%\" class=\"datacell\" align=\"left\"><a href=\"pafiledb.php?action=file&id=$r->file_id\">$r->file_name</a></td></tr>";
}
}
echo "</table>";
}
if (empty ($search)) {
$locbar = "<a href=\"pafiledb.php\" class=\"small\">$config[1]</a> :: $str[search]</a>";
if ($logged == 1) {
adlocbar($locbar, $user, $str);
adminlinks($str);
} else {
locbar($locbar);
}
?>

Thanks

[wizardofchips]

no matter how or where i put the array i keep getting Parse error: parse error, unexpected '.'
and it points at this line...
if ($i < ($num_fields-1)) $where = $fields[$i] . " LIKE '%" . $string% . "' OR ";

[wizardofchips]

i meant loop not array

[charlier]

I think you need to move the final % into the " string.

if ($i < ($num_fields-1)) $where = $fields[$i] . " LIKE '%" . $string . "%' OR ";

[Birdman]

Yup, I had the % in the wrong place. Sorry for that.

for ($i = 0; $i < $num_fields; $i++){
if ($i < ($num_fields-1)) $where = $fields[$i] . " LIKE '%" . $string . "%' OR ";
else $where = $fields[$i] . " LIKE '%" . $string . "%'";
}

You may as well get used to parse errors anyhow :)

Regards,
Birdman

[wizardofchips]

thanks you two... think i have it figured out