using checkbox to select row of data from select query

mamasita60440 · Aug 6, 2006

I have a page that displays output from a mysql query in a table. I added checkboxes to each row. If the checkboxes are checked when I click the submit button I would like to take the data for just the rows that are checked and send that information to an excell spreadsheet.

The problem is I don't know how to say 'if the boxes are checked this is the data I want to use for...'

Does anyone have any suggestions? This is my first php project. Thanks.

ZOR · Aug 6, 2006

You have asked this question already on the MySQL AB: MySQL Forum. Tony Groves has answered you, and waits for you reply.

ZOR · Aug 6, 2006

Sorry, just realised its now PHP question. Regards

jpadie · Aug 7, 2006

this is not too difficult but to give you a good answer please post the code you use to generate the form

mamasita60440 · Aug 7, 2006

Here is my code:

<?php session_start();

$PfrDate=$_Session["PfrDate"];
?>

<html>
<head>
<title>Performance Data By Date</title>
</head>
<body>
<?php
$db_host = "host";
$db_user = "me";
$db_pwd = "43777";
$db_name = "name";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>
<table>
<?php
$sql = "SELECT * FROM Performance where InstallDate= '$PfrDate'";

echo "table border='1'>";
eho "<tr><th>Select</th><th>Handle</th><th>Load</th></tr>;

$query= mysql_query($sql);
while($row = mysql_fetch_array($query)){

echo"<tr><td>";
echo "<input type='checkbox' name='ReportData'>";
echo "</td><td>";
echo $row['HandleName']'
echo "</td><td>";
echo $row['LoadName]'
echo "</td></tr>";

}
?>
</table>
<br><br><input type="submit" value="Submit">
</form>
</body>
</html>

Here is part of the code. Any assistance you can offer is greatly appreciated. Thanks.

bam720 · Aug 7, 2006

Here's a nifty trick (I've learned from some of the pros here)

Code:

echo "<input type='checkbox' name='ReportData[COLOR=#ff0000][ " .$i. " ][/color] '>";
[code]
where $i is a simple couter to tell you what row you are on. When you get to the next page to parse it simply use
[code]
$ReportData = $_Post['ReportData'];

Now $ReportData is an array. The tricky part is checkboxes return values of "on" if checked and nothing if not checked. Now it's a simple if statement inside a loop

Code:

for($i=0,$i<$TotalRows,$i++){
if($ReportData[$i] == "on"){
//Export To Excel
}
}

I may be off by a comma or a ; but it gives you the idea right?

Now someone mentioned that this wasn't W3C compliant, but to them I say find me an easier and compliant way and I'll use it. Till then I'm using this.

jpadie · Aug 8, 2006

yup. i'd do it the same conceptual way as bam720 suggests. i'd probably dopt a different syntax tho:
formgenerator:

Code:

while($row = mysql_fetch_assoc($query)){
echo <<<STR
<tr><td>
<input type='checkbox' name="ReportData[$i]" value="$i">
</td><td>
<input type="text" name="HandleName[$i]" value="{$row['HandleName']}" />
</td><td>
<input type="text" name="LoadName[$i]" value="{$row['LoadName']}" />
</td></tr>

STR;
}

code parsing

Code:

//assume post method
foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):
 echo "data to process is<br/> loadname = {$_POST['LoadName'][$cbox]}; and <br/> HandleName = {$_POST['HandleName'][$cbox]};<br/>";
endforeach;

mamasita60440 · Aug 8, 2006

I incorporated jpadie's code into into my form (PfrReportData.php).

<?php session_start();

$PfrDate=$__SESSION["PfrDate"];
?>

<html>
<head>
<title>Displaying MySQL Data</title>
</head>
<body>
<?php
$db_host = "host";
$db_user = "me";
$db_pwd = "43777";
$db_name = "dbname";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>

<form id="FormName" action= "DisplayReportData.php" method="post" name="FormName">

<table>
<?php
$sql = "SELECT * FROM Performance where InstallDate='$PfrDate'";

echo "<table border='1'>";
echo "<tr><th>Select</th><th>Handle</th><th>Load</th>
------------------------------------------------------------------------------------------------------------------------

But when I try to display the output on another form (DisplayReportData.php) I get an error message. The code is below.

<?php session_start();

$_SESSION["ReportData"]=$_POST["ReportData"];
$_SESSION["HandleName"]=$_POST["HandleName"];
$_SESSION["LoadName"]=$_POST["LoadName"];

foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):

echo "<tr><td>HandleName= $_POST['HandleName'][$cbox]};</td>
<td>LoadName= $_POST['LoadName'][$cbox]};</td>;

endforeach;

?>

<form action="insertPfrGraph.xls" method= "post" name="FormName">

<html>
<body>
<table>

<table border='1'>
<tr><th>Handle</th><th>Load</th></tr>

</table>
<br><br><input type="Display Graph" value="Submit">
</form>
</body>
</html>

bam720 · Aug 8, 2006

I get an error message.

This is a little vague. What exact error message are you getting?

The only thing I see wrong (which took me a while to spot) is here:

Code:

echo "<tr><td>HandleName= $_POST['HandleName'][$cbox]};</td>
<td>LoadName= $_POST['LoadName'][$cbox]};</td>;

Your echo doesn't have a close " You aslo need to move the foreach statement below the tabe HTML like so...

Code:

<form action="insertPfrGraph.xls" method= "post" name="FormName">

<html>
<body>
<table>

<table border='1'>
<tr><th>Handle</th><th>Load</th></tr>
[red]foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):
[blue]$HandleName= {$_POST['HandleName'][$cbox]};
$LoadName= {$_POST['LoadName'][$cbox]};[/blue]
echo "<tr><td>$Handlename</td>
<td></td>$LoadName</td>[blue]</tr>"[/blue];

endforeach;
[/red]
</table>

Ok So the more I started editing the code the more i found wrong. The red in general is what I moved, the Blue is what I changed.

Itshim · Aug 8, 2006

Not to take this solution in another direction, but you could also...

If you have a unique id associated with the db table rows, use that id as the value of the checkbox, so that when the form is submitted you will have an array of all the unique ids for the table rows selected. Since the ids are unique you can then use them as the indexes of the HandleName, and LoadName arrays.

This way on the following page you can loop through the $_POST['ReportData'], using the value of each element as the index of the HandleName and LoadName arrays to generate an output page or send to excel...

mamasita60440 · Aug 8, 2006

I tried that code as well as this variation:

foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):

$HandleName= {$_POST["HandleName"][$cbox]};
$LoadName= {$_POST["LoadName"][$cbox]};
echo "<tr><td><?php echo $_POST["HandleName"]; ?></td>
<td></td><?php echo $_POST["LoadName"]; ?></td></tr>";

echo "</td>";
echo "</tr>";

endforeach;

</table>

But in each instance when I hit submit instead of the values displaying in the table 'Array' is in the first data field.

This is also displaying on the page:

foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):
Array
$HandleName= {$_POST["HandleName"][$cbox]};
$LoadName= {$_POST["LoadName"][$cbox]};

bam720 · Aug 8, 2006

Code:

<table border='1'>
<tr><th>Handle</th><th>Load</th></tr>
[red]<?php[/red]
foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):

$HandleName= {$_POST["HandleName"][$cbox]};
$LoadName= {$_POST["LoadName"][$cbox]};
echo "<tr><td>[red]".$HandleName."[/red]</td>
<td></td>[red]".$HandleName."[/red]</td></tr>";

echo "</td>";
echo "</tr>";

endforeach; [red]?>[/red]

</table>

Im not sure why you changed ti back to be using the POST varibles when you assign the other varibles above it. Also I think it's showing array becuase it doesn't know how to interpret the checkbox values. As I mentioned above a checkbox returns a "on" if checked, not a "1" as you would think. I never use the foreach statement, as I prefer for loops. You could try changing all the chekcbox values to a "1" if "on" and a 0 if not set (if a checkbox is not checked then the result is empty not "0" or "off").

bam720 · Aug 8, 2006

opps the second $HandleName should be a $LoadName

mamasita60440 · Aug 8, 2006

Could someone give me an example of how I would assign an id to each row?

Thanks

Itshim · Aug 8, 2006

When I referred to a unique id for each row...I noticed that you were pulling all columns from the db table [SELECT *], and thought you might have an 'id' column in the database table which is used as an identifier for a specific row.

If so then that would be your unique id. If you do not have or need that column then I would not add it just for this situation, because adding a unique id to the row is what your doing with the iterator others have suggested. The code jpadie posted in his second reply is basically what I was referring to without the db column.

mamasita60440 · Aug 8, 2006

I tried the following, but I am still seeing the same results.

<?php session_start();

$_SESSION["ReportData"]=$_POST["ReportData"];
$_SESSION["HandleName"]=$_POST["HandleName"];

?>

<form action="insertPfrGraph.xls" method= "post" name="FormName">

<html>
<body>
<table>

<table border='1'>
<tr><th>Handle</th><th>Load</th></tr>

<?php
foreach ($_POST['ReportData'] as $cbox=>$cboxvalue):

$HandleName= {$_POST["HandleName"][$cbox]};
$LoadName= {$_POST["LoadName"][$cbox]};
echo "<tr><td>".$HandleName."</td>
<td></td>".$LoadName."</td></tr>";

echo "</td>";
echo "</tr>";

endforeach; ?>

</table>

<br><br><input type="Submit" value="Display Graphs">
</form>
</body>
</html>

--------------------------------------------------------
I also tried this with no luck:

<?php session_start();

$_SESSION["ReportData"]=$_POST["ReportData"];
$_SESSION["HandleName"]=$_POST["HandleName"];

?>

<form action="insertPfrGraph.xls" method= "post" name="FormName">

<html>
<body>
<table>

<table border='1'>
<tr><th>Handle</th><th>Load</th></tr>

$temp=$_POST["ReportData"];
for($i=0; $temp[$i]<>Null;$i++){
if($temp=="on"
echo "<tr><td><?php echo $_POST["HandleName"]; ?>
</td>
<td></td><?php echo $_POST["LoadName"]; ?>
</td></tr>";

echo </td>
echo </tr>

</table>

<br><br><input type="Submit" value="Display Graphs">
</form>
</body>
</html>

Any help is greatly appreciated. Thanks.

jpadie · Aug 9, 2006

i'm not sure whether we may have all misunderstood your original intention.

Just in case we have, let's step right back to first principles.

copy and past this code and get a feel for how it works. we are using a unique id (the array key). this can be easily adapated to use any unique id (eg the db primary key) as itshim suggests.

Code:

<?
$items = array("apple", "banana", "currant", "damson", "elderflower", "foxglove");
$types= array("fruit", "fruit", "fruit", "fruit", "flower", "flower");

if (isset($_POST['submit'])):
	if (count($_POST['checkbox']) < 1):
		echo "No items were selected";
	else:
		
		echo "The following items were selected<br/><ul>";
		foreach ($_POST['checkbox'] as $key=>$val):
			echo "<li> " . $items[$val] . " which is a " . $types[$val] . "<br/></li>";
		endforeach;
		echo "</ul>";
	endif;
	echo "<br/><br/><hr/>";
endif;

echo '
<form method="post" action="' . $_SERVER['PHP_SELF'] . '" >
';
foreach($items as $key=>$val):
	echo <<<EOL

	<input type="checkbox" name="checkbox[$key]" value="$key" />
	&nbsp;
	{$val}
	&nbsp;
	which is a {$types[$key]}
	<br/>

EOL;
endforeach;
echo '<input type="submit" name="submit" value="Try Me" />';
echo "</form>";
?>

mamasita60440 · Aug 9, 2006

yes this is what i am trying to do. I would like for the user to be able to select an item, receive a confirmation displaying what they have selected.

finally, this information will be sent to an excel spreadsheet to create a graph.

Can you help me?

jpadie · Aug 9, 2006

well ... the code above allows a user to select various rows and then returns the data to the screen to show that the rows have been selected.

does this not do the lion's share for you already? what more do you need on the checkbox-row part?

how are you planning to send things to a spreadsheet? using COM or PEAR::spreadsheetwriter or just dumping csv file? can your spreadsheet not communicate directly with the database through odbc (i think this would be the way i would do what you were after)?

harmmeijer · Aug 10, 2006

If the information comes from a database someone else might
add data between selecting items and reporting it.
This would screw up getting the index of a certain row when your data was retreived. When sorting is applied between selecting data it gets even more complicated.
Or when the reporter has to select rows that have to report data that the reporter is not allowed to see (report is sent to manager or whatever).
It is best practice I think to generate the report based on
the ID of the row in the database

Code:

<?php
/*The following class represents a table of Persons
The reporter reports on persons but cannot see the
birthday of the persons, the report is generated
to HR, they have to see the birthday
you need to get a list of ids to report on since
birthday values are not available to the reporter
*/
class Person {
	public $ID;	// the database ID
    public $FirstName;
    public $LastName;
	public $BirthDay; // the reporter may not see this data
					// but has to report on it (say by email)
	function Person($ID,$FirstName,$LastName,$BirthDay){
		$this->ID = $ID;
		$this->FirstName = $FirstName;
		$this->LastName = $LastName;
		$this->BirthDay = $BirthDay;
	}
}
// creating the dataset
$DataSet[0] = new Person(2, "A","A","A"); 
$DataSet[2] = new Person(1, "B","B","B"); 
$DataSet[3] = new Person(3, "C","C","C"); 
$DataSet[4] = new Person(10, "D","D","D"); 
$DataSet[5] = new Person(4, "E","E","E"); 
$DataSet[6] = new Person(5, "F","F","F"); 
$DataSet[7] = new Person(6, "G","G","G"); 
$DataSet[8] = new Person(7, "H","H","H"); 
$DataSet[9] = new Person(8, "I","I","I"); 
$DataSet[10] = new Person(9, "J","J","J"); 
// getting a person (Row) by ID
function getPersonById($id){
	global $DataSet;	
	foreach($DataSet as $person){
		if($person->ID == $id){
			return $person;
		}
	}
}
?>
  <form method="post" >
<?PHP
if($Report){
	// generate report for HR	
	print "<h1>Report to send to HR</h1>";
	// Get all the ID's of persons selected (ID in the database)
	$arr = $_POST['person'];
	// check to see if any person was selected for report purposes
	if ($arr != null){
		// the query would look like:
		// "SELECT * FROM tblPerson WHERE ID IN (" .implode(",", $arr) .")
		print "SELECT * FROM tblPerson WHERE ID IN (" .implode(",", $arr) .")<br />";
		foreach($arr as $id){
			$pers = getPersonById($id);
			print $pers->FirstName .", " .$pers->FirstName .", " .$pers->FirstName ."<br />"; 
		}
	}else {
		// no persons were selected for the report (do nothing)
		print "(nothing to report on)";
	}
	print "<br /><br /><br />";
}

// generate the list for the reporter
// if you'd get this from a database it 
// would look like "SELECT * FROM tblPersons WHERE ...."
$counter=0;
foreach($DataSet as $Row){
	print <<<REPORTER_OUTPUT
$Row->FirstName, $Row->LastName, <input type="checkbox" name="person[$counter]" value="$Row->ID"/> <br />


REPORTER_OUTPUT;
		$counter++;
	}
?>
	Check this to generate report<input type="checkbox" name="Report" value="1"/><br />
	<input type="submit" name="submit" value="submitForm"/>
  </form>

Greetings, Harm Meijer

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

using checkbox to select row of data from select query

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor