Jump to content

Building a select statement from checkboxes


cyprus

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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


Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.