Creating a proper query from dynamically generated check boxes

[lightnb]

My script querys the database, and uses a while loop to generate a check box for each unique manufacturer.

when you select one of the boxes and submit the form, it displays all the items made by that manufacturer.

When you select more than one checkbox, it doesn't work.

the variable "$checkbox_name" only contains one value. What do i need to do to make more than one checkbox at a time work?

Live example: http://www.rahlentertainment.com/db_results.php

My script:

if ( $go_button == "go!" ) // check to see if the user has submited the filter form
{ // If so, figure out what the user wants, display the form again, and display the filtered results:


// crteate the query string based on user input

if(isset($make_checkbox))
{
$where_string = " WHERE make = '" .$make_checkbox. "'";
}

// show the filter form again:

echo "this form has been submited";

echo $make_array;

echo "<form action='" .$PHP_SELF. "' method='post'>";

// Begin the dynamically generated select box

$manu_sql = "SELECT distinct make FROM " .$lighting_fixtures_table_name;

$manu_result = mysql_query($manu_sql);

While ( $manu_row = mysql_fetch_array ( $manu_result ) )
{
echo $manu_row[make] . ': &nbsp; <input name="make_checkbox" type="checkbox" id="make_checkbox" value="' . $manu_row[make] . '"><br/>';
}

// END SELECT BOX

//Submit Button

echo "<input type='submit' name='go_button' value='go!'/>";

// END SUBMIT BUTTON

echo "</form>"; // END FORM



// display the filtered results

$sql = "SELECT distinct make as mnf
FROM $lighting_fixtures_table_name";

if(isset($where_string))
{
$sql .= $where_string;
}
else{
$sql .= " ORDER BY mnf";
}

//$result = mysql_query($sql); // use this line live, and the one below for debugging

$result = mysql_query($sql) or die("Query failed: $sql - " . mysql_error()); //use this line for debugging or the one above for live environment

while ( $row = mysql_fetch_array( $result ) )
{
echo "<fieldset><legend>$row[mnf]</legend><table align='center'><tr>";
$sub_sql = "SELECT make, model, type
FROM $lighting_fixtures_table_name
WHERE make = '".$row[mnf]."'
ORDER BY model";

//echo $sub_sql; //Show the query for debuggin purposes (othewise comment out this line)

$sub_result = mysql_query($sub_sql);

$line_counter = 1; //initialize the line counter

while ( $sub_row = mysql_fetch_array( $sub_result ) )
{

if ($line_counter == 5)
{
echo "</tr><tr>";
$line_counter = 1;
}else
{
$line_counter = $line_counter + 1;
}

// This code is the individual result box

echo "<td>";
echo '<table width="116" border="0"><tr><td width="110" height="114" background="http://www.rahlentertainment.com/lxc/graphics/database_icon.jpg"><div align="center">';
echo $sub_row[type];
echo "</div></td></tr>";
echo '<tr><td><div align="center"><b>' . $sub_row[make] . "</b></div></td></tr>";
echo '<tr><td><div align="center">' . $sub_row[model] . "</div></td></tr></table>";
echo "</td>";

// END RESULT BOX

}
echo "</tr></table></fieldset><br><br>";

}

[scragar]

replace:
$where_string = " WHERE make = '" .$make_checkbox. "'";
withif(is_array($make_checkbox)){
$where_string = " WHERE make = '".implode("' OR make = '", $make_checkbox)."'";
}else{
$where_string = " WHERE make = '" .$make_checkbox. "'";
};
and it should work as expected.

[bokeh]

if(is_array($make_checkbox)){
$where_string = " WHERE make = '".implode("' OR make = '", $make_checkbox)."'";Looks to me like you will have an empty make = '' at the start of the clause. Maybe:if(is_array($make_checkbox))
{
$where_string = ' WHERE';
$i = 0;
foreach($make_checkbox as $box)
{
$where_string .= (($i++) ? ' OR' : null ) . " make = '$box'";
}
}
else
{
$where_string = " WHERE make = '$make_checkbox'";
}

[aaronbdavis]

Easier than that: use the in clause in your query.

//Pseudo-code: needs adjusement to work
// but should give you the idea

$list = "'" . implode("','", $make_checkbox) . "'";
// if this is for numbers, change to this:
// $list = implode(",", $make_checkbox);

// then in the query, use this way:
$sql = "... WHERE make IN ($list)";

[scragar]

mine would still work perfectly, although aaronbdavis's code looks to be the better example since mySQL will need less resourses to run the query.

[lightnb]

Easier than that: use the in clause in your query.

//Pseudo-code: needs adjusement to work
// but should give you the idea

$list = "'" . implode("','", $make_checkbox) . "'";
// if this is for numbers, change to this:
// $list = implode(",", $make_checkbox);

// then in the query, use this way:
$sql = "... WHERE make IN ($list)";


I'm not framiliar with the implode function...

I tried: if ( $go_button == "go!" ) // check to see if the user has submited the filter form
{ // If so, figure out what the user wants, display the form again, and display the filtered results:


$list = "'" . implode("','", $make_checkbox) . "'";

// crteate the query string based on user input

if(isset($make_checkbox))
{
$where_string = " WHERE make IN ($list)";
}

and got:

Warning: implode() [function.implode]: Bad arguments. in /home2/rahl/public_html/db_results.php on line 33

[scragar]

if $make_checkbox is not an array(IE you fill in 0 or 1 boxes) then implode will throw out an error.

That's why my code used an if to select this instance.

[aaronbdavis]

You will want to check that $make_checkbox is set before trying to implode it. Also, as scragar said, if it is not an array it will throw an error. To fix this, check to make sure that it is an array, and if it is not, then either create a where equals clause, with the implode in the else block, or turn it into an array, don't use an else block, and do the implode outside of the if-statement.
i.e.
if (!is_array($make_checkbox))
{
// set up the Where-Equals clause
} else
{
// set up the Where-In clause using implode
}

// OR

if (!is_array(#make_checkbox))
{
$make_check = array($make_checkbox);
} else {}

// set up the Where-In clause using implode

[lightnb]

I'm now using this:

// create the query string based on user input

if(!is_array($make_checkbox))
{
$where_string = " WHERE make = '$make_checkbox' ";
}
else{
// set up the Where-In clause using implode
$list = "'" . implode("','", $make_checkbox) . "'";
$where_string = " WHERE make IN ($list)";
}


// display the filtered results

$sql = "SELECT distinct make as mnf
FROM $lighting_fixtures_table_name";

if(isset($where_string))
{
$sql .= $where_string;
}
else{
$sql .= " ORDER BY mnf";
}

And it's no longer giving me any errors, but it's still only selecting a single manufacturer regardless of how many checkboxes are selected.

[lightnb]

Do I have to do anything special with my form to make sure it turns the checkboxes into an array, or is that automatic?

[bokeh]

<input type="checkbox" name="myarray[]" value="1">
<input type="checkbox" name="myarray[]" value="2">
<input type="checkbox" name="myarray[]" value="3">
<input type="checkbox" name="myarray[]" value="4">
<input type="checkbox" name="myarray[]" value="5">Notice the square brackets at the end of the name attribute and that all have the same name.

[lightnb]

I added the square brackets but, i think somthing else is broken, since I get Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home2/rahl/public_html/lxc/database/db_results.php on line 44 .

I'm using $make_sql = "SELECT distinct make FROM " . $lighting_fixtures_table_name;

$make_result = mysql_query($make_sql);

While ( $make_row = mysql_fetch_array($make_result ) )
{
echo $make_row[make] . ': &nbsp; <input name="make_checkbox[]" type="checkbox" id="make_checkbox" value="' . $make_row[make] . '"><br/>';
}

[bokeh]

$make_result = mysql_query($make_sql);Change to: $make_result = mysql_query($make_sql) or die("<p>$make_sql</p>\n<p>".mysql_error()."</p>\n");

[lightnb]

I get SELECT distinct make FROM

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


It doesn't seem to be adding on the table variable correctly. I moved the block of code that displays the form into a function, since it's used multiple times. Wasn't there some thing you had to do to get variables to work inside functions?

$make_sql = "SELECT distinct make FROM " . $lighting_fixtures_table_name;

[bokeh]

That means $lighting_fixtures_table_name is empty.

[lightnb]

Look like you had to declare the variable as global inside the function.

global $lighting_fixtures_table_name;

Got it working now. Thank You :)