Building a select statement from checkboxes

[cyprus]

I have been struggling for ages on this. I have a page with 12 checkboxes, which get posted to a page with a dropdown list on. 6 checkboxes choose which table columns the user wants to see in the list. 6 checkboxes select the products he wants to see in the list, products are categorised in 6 groups, so he might choose 1,3,5.

How do I get from the checkboxes to building a Select from database line of code?

eg Select a,b,c from database where x=1 or x=2 etc.

Would be very appreciated if someone could help, my knowledge is small.Thanks

[HuggieBear]

You could use if statements...

[code=php:0]
$sql = "SELECT col1, col2, col3 FROM table WHERE id";
if (isset($_GET['checkbox1']))(
  $sql .= "AND value1 = 'YES'";
}
if (isset($_GET['checkbox2']))(
  $sql .= "AND value2 = 'YES'";
}
$result = mysql_query($sql);
[/code]

Have you tried something like that?

Regards
Rich

[cyprus]

Thanks, no I did not go down that road. I am posting the values through and using session variables. I got as far as:

$query = "SELECT Orderdate, " .$op1. " FROM Orders";

There is an $op1,$op2,$op3,$op4,$op5,$op6 which carry the names of six columns to get displayed.

There is an $T1,$T2,$T3,$T4,$T5,$T6 which each carry a value of 1 if checked being for the WHERE statement:

WHERE  $T1=1 or $T2=1 etc.

I have been tying myself in knots, especially how to include the "OR" when there maybe only one box selected or its the last of the selections. Its been a real minefield. Thanks

[HuggieBear]

SO all the op's are additional columns you can select, and all of the T's are the values for use in the WHERE clause?

If so, then just do as suggested, build the query as you go with concatenation.

Regards
Rich

[Destruction]

I wrote a generic function for dynamically creating a WHERE clause from checkboxes or list selections where multiple are allowed.

[code]
<?php
function dynSelect($field, $options = array())
{
$list = array();
foreach($options as $option) //Loop through the checkbox selected values
{
if(!is_numeric($option)) //If not numeric, encase in single quotes for query
{
$option = "'$option'";
}
$list[] = "`$field` = $option"; //Add to array
}
$list = implode(" OR ", $list); //Join any seperate array sections with OR, if only one value this function will not add the OR.
return $list; //Return string list
}
?>
[/code]

In the checkbox code you'd put similar to below:
[code]
<?php
echo '<input type="checkbox" name="options[]" value="$id" />';
?>
[/code]

Then using the function would be such as:

[code]
<?php
$where = dynSelect("id", $_POST['options']);
$sql = mysql_query("SELECT * FROM `table` WHERE $where");
?>
[/code]

Hope this helps,

Dest

[Barand]

is this what you are after

[code]<?php
if (isset($_POST['submit'])) {
    $fields = 'op1'; // default
    $where = '';
    if (count($_POST['showfield']))
        $fields = join(',', $_POST['showfield']);
   
 
    if (count($_POST['product'])) {
        $prodlist = join("','", $_POST['product']);
        $where = "WHERE product IN ('$prodlist')";
    }
    $sql = "SELECT $fields FROM mytablename $where";
   
    echo "<p>$sql</p>";
}

?>
<FORM method='post'>
Show fields<br>
<input type="checkbox" name="showfield[]" value="op1">Op1<br>
<input type="checkbox" name="showfield[]" value="op2">Op2<br>
<input type="checkbox" name="showfield[]" value="op3">Op3<br>
<input type="checkbox" name="showfield[]" value="op4">Op4<br>
<input type="checkbox" name="showfield[]" value="op5">Op5<br>
<input type="checkbox" name="showfield[]" value="op6">Op6<br>
<br>
Select products<br>
<input type="checkbox" name="product[]" value="product1">product1<br>
<input type="checkbox" name="product[]" value="product2">product2<br>
<input type="checkbox" name="product[]" value="product3">product3<br>
<input type="checkbox" name="product[]" value="product4">product4<br>
<input type="checkbox" name="product[]" value="product5">product5<br>
<input type="checkbox" name="product[]" value="product6">product6<br>
<input type="submit" name="submit" value="Submit">
</FORM>[/code]

[cyprus]

Many Many thanks for all the help folks, I'm astounded by the effort you all make in this forum. I will try all the options out and hopefully get there.

[cyprus]

I have been playing around with Barand's code, not that I have ignored the others, in fact Destructions code covers my question, but have not had the chance to try it. However I am trying to modify the WHERE statement below.

if (count($_POST['product'])) {
        $prodlist = join("','", $_POST['product']);
        $where = "WHERE GGPost = ('$prodlist')";

    }
    $sql = "SELECT $fields FROM ORDERS $where";


I am trying to get it to build - Where GGPost=1 or GGPost=2 etc.
Any idea how to modify it. Many thanks again to al

[Barand]

Why?

GGPost IN ('1', '2', '3')

is exactly the same as

(GGPost = '1') OR (GGPost = '2') OR (GGPost = '3')

only it's a lot simpler.

[cyprus]

Many thanks, you are correct. I put it back and it works. Is there a way to hold checkbox values that are selected after a sumbission without using a table? Also is there a points award on this site for showing gratitude of help. Many thanks again

[Barand]

[quote author=cyprus link=topic=107961.msg434421#msg434421 date=1158264900]
Is there a way to hold checkbox values that are selected after a sumbission without using a table?
[/quote]
Not sure what you mean. Can you explain?

[cyprus]

Thanks. I have a page with checkboxes on, also a dropdown list. The list fills itself from a database query based on the selections of the checkboxes. In order for the list to get filled/updated, the form has to be submitted and returned. However after submission the checkboxes are cleared. Hope that explains it better.

[Barand]

You need to check if the value of each c/box was in those values that were posted. If so, add 'checked' inside the option tag. The easiest way to check them all is in a loop

[code]
Select products<br>

<?php
    $products = array (
        1 => 'product1',
        2 => 'product2',
        3 => 'product3',
        4 => 'product4',
        5 => 'product5',
        6 => 'product6'
    );
    foreach ($products as $id =>$prod) {
        if ($_POST['product']) {
            // was value of id in those posted?
            $chk = in_array($id, $_POST['product']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>";
       
    }
?>
[/code]

[cyprus]

Many thanks, I tried to put it in my existing code, but had no luck. My current code is:

<?
session_start();
?>

<?
    if (isset($_POST['submit'])) {
    $fields = 'op1'; // default
    $where = '';
    if (count($_POST['showfield']))
    $fields = join(',', $_POST['showfield']);
   
    if (count($_POST['product'])) {
    $prodlist = join("','", $_POST['product']);
    $where = "WHERE GGPost IN ('$prodlist')";

    }
    $sql = "SELECT $fields FROM ORDERS $where";
    echo "<p>$sql</p>";
}

?>

<FORM method='post'>
Included Data<br>
<input type="checkbox" name="showfield[]" value="OrderNumber">Order Number<br>
<input type="checkbox" name="showfield[]" value="Orderdate">Order Date<br>

<br>
Select Product<br>
<input type="checkbox" name="Product[]" value="1">Digital Betacam<br>
<input type="checkbox" name="product[]" value="2">Betacam SP<br>
<input type="checkbox" name="product[]" value="3">DVCPro<br>
<input type="checkbox" name="product[]" value="4">HDCAM<br>
<input type="checkbox" name="product[]" value="5">Mini DV<br>
<input type="submit" name="submit" value="Submit">
</FORM>

Would be very appreciative if you could advise where to put the code to retain checkbox positions after submit. Many thanks

[Barand]

replace
[code]<input type="checkbox" name="product[]" value="1">Digital Betacam

<input type="checkbox" name="product[]" value="2">Betacam SP

<input type="checkbox" name="product[]" value="3">DVCPro

<input type="checkbox" name="product[]" value="4">HDCAM

<input type="checkbox" name="product[]" value="5">Mini DV[/code]

with
[code]<?php
    $products = array (
        1 => 'Digital Betacam',
        2 => 'Betacam SP',
        3 => 'DVCPro',
        4 => 'HDCAM',
        5 => 'Mini DV'
    );
    foreach ($products as $id =>$prod) {
        if ($_POST['product']) {
            // was value of id in those posted?
            $chk = in_array($id, $_POST['product']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>";
       
    }
?>[/code]

[cyprus]

Thanks a million, absolutely perfect. I will modify the other checkboxes in the same way. So glad I moved over to this site with people like you willing to help. The last forum was filled with "Look in the manual", very greatful for all your help. Thanks

[cyprus]

Absolutely perfect. I have just modified the other checkboxes the same way and all is fine. So pleased I joined this forum, with people around like you out there. Thanks again

[Barand]

Glad to have helped