simple php and checkbox help.

Creamy Goodness

i have a form that has numerous checkboxes on it, i would like to be able to create a simple sql statement based on which checkboxes are ticked.

so say someone ticks only the project and keyword box. i want the query to be

SELECT project, keyword FROM table;

or if the project, keyword, origination was ticked

SELECT project, keyword, origination FROM table;

here's my form

[php]
//form submits to itself.
echo '<form name="excelexport" method="get" action='.$_SERVER.'>
<table class="stats">
<tr>
<td class="hed" colspan="2">Export to Excel</td>
</tr>
<tr>
<td>Project</td>
<td><input type="checkbox" name="project" value="project" /></td>
</tr>

<tr>
<td>Product</td>
<td><input type="checkbox" name="product" value="product" /></td>
</tr>
<tr>
<td>Product No.</td>
<td><input type="checkbox" name="productno" value="productno" /></td>
</tr>
<tr>
<td>Keyword</td>
<td><input type="checkbox" name="keyword" value="keyword" /></td>
</tr>

<tr>
<td>Origination</td>
<td><input type="checkbox" name="origin" value="origin" /></td>
</tr>
<tr>
<td>Submitted Date</td>
<td><input type="checkbox" name="submitdate" value="submitdate" /></td>
</tr>
<tr>
<td>Status</td>
<td><input type="checkbox" name="status" value="status" /></td>
</tr>

<tr>
<td colspan="2"><input type="button" class="subbut" value="Select All" onclick="checkAll(document.excelexport);"/>  <input type="button" class="subbut" value="De-select All" onclick="uncheckAll(document.excelexport);"/>  <input type="submit" class="subbut" value="Export"/></td>
</tr>';
[/php]now above this i check to see if the $_GET array has been set.

[php]
if(isset($_GET))
{
$sql = "SELECT ";
foreach ($_GET as $key => $value)
{
$sql.= $value . ",";
}

//remove the last comma
$sql = substr($sql,0,-1);

$sql .= " FROM table";


$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
echo $row . "<br/>" . $row; //etc. etc.
}
}
[/php]if i echo out $sql the query i get a query which i expect, but i get a mysql warning and everything stops

here's the warning: WARNING: mysql_fetch_array(): supplied argument is not a valid MYSQL result resource in <FILENAME.PHP> on line 24

line 24 is the $result = mysql_query($sql);

seamus

When you execute the query directly against the server, does it work?

On an aside, remember that checkboxes don't work the same way as other input. If a checkbox is not selected, it simply won't appear in the $_GET array. So if no boxes are selected, your script will still attempt to execute the query "SELECT FROM table"

if recommend you change your if condition to
[php]if(isset($_GET) && !empty($_GET))[/php]
In the interests of security, I would also recommend that you write the lines after that with injection in mind:
[php] foreach ($_GET as $key => $value)
{
$sql.= "`".mysql_escape_string($value) . "`,";
}[/php]

Creamy Goodness

seamus wrote: »

When you execute the query directly against the server, does it work?

On an aside, remember that checkboxes don't work the same way as other input. If a checkbox is not selected, it simply won't appear in the $_GET array. So if no boxes are selected, your script will still attempt to execute the query "SELECT FROM table"

if recommend you change your if condition to
[php]if(isset($_GET) && !empty($_GET))[/php]In the interests of security, I would also recommend that you write the lines after that with injection in mind:
[php] foreach ($_GET as $key => $value)
{
$sql.= "`".mysql_escape_string($value) . "`,";
}[/php]

thank you Seamus, is was the !empty($_GET) that solved it.

i usually do write my queries with sql injection in mind i just left it out of the code example above as with other parts of my code as i don't like a lot of my code online especially when it's not necessary

Creamy Goodness

ok i've move on a little from this idea and now have three values that are selected from three separate drop-down boxes that are posted to a php script.

i have an base sql query that i'd like to run which is

SELECT project, subarea, product, date, etc FROM table;

that query will execute if nothing is selected from the drop down box, but i want to be able to filter the results based on what is in the drop-down boxes.

so for example, the user selects a project and a product the query would look like this:

SELECT project, subarea, product, date, etc FROM table WHERE project='$userselectedproject' AND product='$userselectedproduct';

problem i'm having a hard time thinking of all the permutations and how to structure the query?

*EDIT*,

i'm working on an if and elseif block at the moment, but i can see that being big and rather cumbersome to anyone maintaining the code.