Jump to content


Photo

Building a select statement from checkboxes


  • Please log in to reply
17 replies to this topic

#1 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 13 September 2006 - 10:47 PM

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

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 September 2006 - 10:54 PM

You could use if statements...

$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);

Have you tried something like that?

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 13 September 2006 - 11:08 PM

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

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 September 2006 - 11:14 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 Destruction

Destruction
  • Members
  • PipPipPip
  • Advanced Member
  • 108 posts

Posted 13 September 2006 - 11:17 PM

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

<?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
}
?>

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

Then using the function would be such as:

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

Hope this helps,

Dest

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 13 September 2006 - 11:48 PM

is this what you are after

<?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>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 06:42 AM

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.

#8 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 07:28 PM

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




#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 September 2006 - 07:38 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 08:15 PM

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

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 September 2006 - 08:19 PM

Is there a way to hold checkbox values that are selected after a sumbission without using a table?

Not sure what you mean. Can you explain?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 08:59 PM

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.

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 September 2006 - 09:37 PM

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

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>";
        
    }
?> 

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 10:17 PM

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


#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 September 2006 - 10:23 PM

replace
<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

with
<?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>";
        
    }
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 10:31 PM

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

#17 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 14 September 2006 - 11:14 PM

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

#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 September 2006 - 11:23 PM

Glad to have helped
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users