Build select SQL string from boxes checked 1

[clanm]

I'm trying to build a query string, and the where clause will pull data corresponding to the five check boxes checked. The values for these five are either 0 or 1, 1 for yes and 0 for no.

For instance, let's say I have a user form, and the user clicks which ice cream they like:
Chocolate
Strawberry
Vanilla
Bubble Gum
Rocky Road

Now, we have an administration page which has all five of these same check boxes, and if that person from the admin group wants to see all the users that checked Chocolate and Vanilla, they'd click the first and third check box and hit "Go" to pull back the records for that query.

I know this is simple, and I could use something like a "If chbox1 ischecked then varSQL = " where chkbox1 = 1"
...etc. But I'm not sure how to search through all checkboxes and make sure if anything has been checked or not. I'm guessing I need to count up the values in a summation or loop through each of the check boxes and see if the admin user has at least checked something...or maybe they check nothing and want to see that data. That's a possibility.

I know there's a lot of knowledge out there in this forum, so I was just looking for some feedback by those who've done these scenarios.

Any suggestions are welcome!

Thanks!

 

[jpadie]

first thing to realise is that browsers only return checkboxes that have been checked. so you will never get a checkbox value submitted if the box has not been checked. if this is functionality you want you need to use javascript to populate a hidden text box or similar.

here's some code i put together quickly that will give you a rough idea of how things work. the db structure that is presupposed is a table with ice cream names and a unique id and a non-normalised table of name->icecreamid this is, in essence, a join table. in the real world you would have a person table with names etc and a unique id and the people_who_like_ice_cream table would have two foreign keys: the personid and the icecreamid and therefore the sql would have a join in it. hopefully this is enough to exemplify the coding method you might want to adopt, though.

<?

if (isset($_POST['submit'])):
	$sql = "";
	process_form();
endif;

function process_form() {
	$in = "";
	if(is_array($_POST['icecreams'])): //belt and braces test
		foreach ($_POST['icecreams'] as $icecream):
			$in .= "'". mysql_escape_string($icecream) . "',";
		endforeach;
		$in = rtrim($in, ",");
	endif;
	global $sql;
	$sql = "Select * from people_who_like_icecream where icecream_id IN ($in)";
}

if (!empty($sql)):
?>
<div >
<fieldset style="background-color:#F0F0F0; color:#CC99CC; width:60%; margin:0 auto; font-family:'Courier New', Courier, monospace; border: 1px dashed #666666">
<legend>SQL Code from Form</legend>
<?=$sql?>
</fieldset>
</div>
<? endif; ?>
<br/> <br/>
<form method="post" action="<?=$_SERVER['PHP_SELF']?>" style="background-color:#D9FBFF; width:60%; font-family:Verdana, Arial, Helvetica, sans-serif; border:1px solid">
<div style="clear:both; padding-bottom:5px;"> &nbsp;</div>
<?
$sql = "Select icecream_id, icecream_name from icecreams";
$result = mysql_query ($sql);
while ($row=mysql_fetch_assoc($result)):
	$checked = "";
	if (isset($_POST['icecreams'])):
		if (in_array($row['icecream_id'],$_POST['icecreams'])):
			$checked = "checked";
		endif;
	endif;
?>
<input 	type="checkbox" 
		name="icecreams[]" 
		value="<?=$row['icecream_id']?>" 
		<?=$checked?> />  
		<?=$row['icecream_name']?>
		<br/>
<?
endwhile;
?>
<div style="float:right";>
<input type="reset" name="reset" value="Reset" />&nbsp;<input type="submit" name="submit" value="Submit" />
</div>
<div style="clear:both; padding-bottom:5px;"> &nbsp;</div>
</form>

 

[jpadie]

lazy of me. here is a bit of revised code with some data embedded in the comment block at the top. create a db called icecream, use the sql in the commentblock to create the tables and populate some sample data, and change the mysql_connect line to reflect your logon parameters.

hope that makes more sense:

<?
/* SQL DATA 
DROP TABLE IF EXISTS `icecreams`;
CREATE TABLE IF NOT EXISTS `icecreams` (
  `icecream_id` int(100) NOT NULL auto_increment,
  `icecream_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`icecream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `icecreams`
-- 

INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (1, 'Rocky Road');
INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (2, 'Raspberry Ripple');
INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (3, 'Chocolate Swirl');
INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (4, 'Vanilla');
INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (5, 'Caramel');
INSERT INTO `icecreams` (`icecream_id`, `icecream_name`) VALUES (6, 'Banana');

-- --------------------------------------------------------

-- 
-- Table structure for table `people_who_like_icecream`
-- 

DROP TABLE IF EXISTS `people_who_like_icecream`;
CREATE TABLE IF NOT EXISTS `people_who_like_icecream` (
  `person_id` int(100) NOT NULL,
  `icecream_id` int(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `people_who_like_icecream`
-- 

INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (1, 1);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (1, 4);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (2, 1);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (2, 5);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (2, 6);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (3, 2);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (4, 1);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (4, 2);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (4, 3);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (4, 4);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (5, 5);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (5, 6);
INSERT INTO `people_who_like_icecream` (`person_id`, `icecream_id`) VALUES (6, 3);

-- --------------------------------------------------------

-- 
-- Table structure for table `persons`
-- 

DROP TABLE IF EXISTS `persons`;
CREATE TABLE IF NOT EXISTS `persons` (
  `person_id` int(100) NOT NULL auto_increment,
  `person_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `persons`
-- 

INSERT INTO `persons` (`person_id`, `person_name`) VALUES (1, 'Albert');
INSERT INTO `persons` (`person_id`, `person_name`) VALUES (2, 'Bertie');
INSERT INTO `persons` (`person_id`, `person_name`) VALUES (3, 'Charles');
INSERT INTO `persons` (`person_id`, `person_name`) VALUES (4, 'Daniella');
INSERT INTO `persons` (`person_id`, `person_name`) VALUES (5, 'Edward');
INSERT INTO `persons` (`person_id`, `person_name`) VALUES (6, 'Francis');

*/
$sql="";
$people="";
mysql_connect("SERVERNAME", "USERNAME", "PASSWORD") or die(mysql_error());
mysql_select_db("icecream") or die(mysql_error());
if (isset($_POST['submit'])):
	process_form();
endif;


function process_form() {
	$in = "";
	if(is_array($_POST['icecreams'])): //belt and braces test
		foreach ($_POST['icecreams'] as $icecream):
			$in .= "'". mysql_escape_string($icecream) . "',";
		endforeach;
		$in = rtrim($in, ",");
	endif;
	global $sql;
	global $people;
	$sql = "
		SELECT DISTINCT 
			persons.person_name as person
		FROM 
			(icecreams 
				INNER JOIN 
					people_who_like_icecream 
					ON 
					icecreams.icecream_id = people_who_like_icecream.icecream_id) 
			INNER JOIN 
				persons 
				ON people_who_like_icecream.person_id = persons.person_id
		WHERE 
			(((icecreams.icecream_id) In ($in)))";
	$result = mysql_query($sql) or die(mysql_error());
	
	while ($row = mysql_fetch_assoc($result)):
		$people .= $row['person'] . "<br/>";
	endwhile;
	
}

if (!empty($sql)):
?>
<div >
<fieldset style="background-color:#F0F0F0; color:#CC99CC; width:60%; margin:0 auto; font-family:'Courier New', Courier, monospace; border: 1px dashed #666666">
<legend>SQL Code from Form</legend>
<?=$sql?>
</fieldset>
</div>
<div>
<div style="margin-top:10px; font-family:Verdana, Arial, Helvetica, sans-serif" >
The following people like the icecreams you queried:<br/>
<?=$people?> </div>
<? endif; ?>
<br/> <br/>
<form method="post" action="<?=$_SERVER['PHP_SELF']?>" style="background-color:#D9FBFF; width:60%; font-family:Verdana, Arial, Helvetica, sans-serif; border:1px solid">
<div style="clear:both; padding-bottom:5px;"> &nbsp;</div>
<?
$sql = "Select icecream_id, icecream_name from icecreams";
$result = mysql_query ($sql);
while ($row=mysql_fetch_assoc($result)):
	$checked = "";
	if (isset($_POST['icecreams'])):
		if (in_array($row['icecream_id'],$_POST['icecreams'])):
			$checked = "checked";
		endif;
	endif;
?>
<input 	type="checkbox" 
		name="icecreams[]" 
		value="<?=$row['icecream_id']?>" 
		<?=$checked?> />  
		<?=$row['icecream_name']?>
		<br/>
<?
endwhile;
?>
<div style="float:right";>
<input type="reset" name="reset" value="Reset" />&nbsp;<input type="submit" name="submit" value="Submit" />
</div>
<div style="clear:both; padding-bottom:5px;"> &nbsp;</div>
</form>

 

[clanm]

Wow...thanks jpadie!

I'll try it out tonight!