Hi,
What I am trying to do is I have five columns named: janday1, janday2, janday3, janday4, janday5.
I have the statement below in my web page:
$sql = "UPDATE classlog SET janday1 = 'P' ";
I want the updated column to change depending on the column name selected in the drop down box called “Pick Column to Update::”.
For example if I select janday1 in the drop down box this means I want column “janday1” to be updated so when I check the checkbox for multiple update, all janday1 column will be updated to ‘P’.
And if I select janday2 in the drop down box and check the checkbox for multiple update, all janday2 column will be updated to ‘P’.
And if I select janday3 in the drop down box and check the checkbox for multiple update, all janday3 column will be updated to ‘P’ and so on.
I want this because I do not want to put checkboxes to update each column.
Sir, I can you help me with this.
Here is the code for the tables:
CREATE DATABASE pathway;
DROP TABLE IF EXISTS pickday;
CREATE TABLE pickday
(dayid BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
day_name varchar(15) ,
PRIMARY KEY (dayid)
);
DROP TABLE IF EXISTS classlog;
CREATE TABLE classlog
(attid BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
student_id INTEGER (11) ,
surname varchar(5) ,
firstname_other varchar(5) ,
class_name varchar(5) ,
janday1 varchar(15) ,
janday2 varchar(15) ,
janday3 varchar(15) ,
janday4 varchar(15) ,
janday5 varchar(15) ,
PRIMARY KEY (attid)
);
INSERT INTO classlog (student_id, surname, firstname_other, class_name) VALUES
(1000,'JOHN','JOHN','BASIC1'),
(1001,'DAVID','DAVID','BASIC2'),
(1002,'BILLY','BILLY','BASIC3'),
(1003,'STEPHEN','STEPHEN','BASIC2'),
(1004,'AHMED','AHMED','BASIC1');
Here is the code for the php page:
<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("pathway") or die(mysql_error());
$queryData = mysql_query("SELECT * FROM classlog") or die(mysql_error());
if(isset($_GET['P'])){
$multiple = $_GET['multiple'];
$i = 0;
$sql = "UPDATE classlog SET janday1 = 'P' ";
foreach($multiple as $item_id) {$i ++;
if ($i == 1) {
$sql .= " WHERE student_id = ". mysql_real_escape_string($item_id) . "";
} else {
$sql .= " OR student_id = ".mysql_real_escape_string($item_id)."";
}
}
mysql_query($sql) or die(mysql_error());
header("location: ".$_SERVER['PHP_SELF']);
exit();
}
?>
<?php require_once('Connections/con_pathway.php');
mysql_select_db($database_con_pathway, $con_pathway);
$query_pickday = "SELECT * FROM pickday";
$pickday = mysql_query($query_pickday, $con_pathway) or die(mysql_error());
$row_pickday = mysql_fetch_assoc($pickday);
$totalRows_pickday = mysql_num_rows($pickday);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
<link href="school/css/attend.css" rel="stylesheet" type="text/css">
</head>
<body>
<div id="wrapper">
</div><!--
<?php if(mysql_num_rows($queryData)>0): ?>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="GET">
Pick Column to Update::
<select name="class_group">
<option value="">Pick Group</option>
<?php
do {
?>
<option value="<?php echo $row_pickday['day_name']?>"><?php echo $row_pickday['day_name']?></option>
<?php
} while ($row_pickday = mysql_fetch_assoc($pickday));
$rows = mysql_num_rows($pickday);
if($rows > 0) {
mysql_data_seek($pickdayp, 0);
$row_pickday = mysql_fetch_assoc($pickday);
}
?>
</select>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<thead>
<tr>
<td>ID</td>
<td>Surname</td>
<td>First/Othernames</td>
<td>Class Name</td>
<td>day1</td>
<td>day2</td>
<td>day3</td>
<td>day4</td>
<td>day5</td>
<td align="center">
<div> <input type="submit" name="P" value="P"></div>
<input type="checkbox" name="day1" id="day1">
</td>
</tr>
</thead>
<tbody>
<?php while ($row = mysql_fetch_assoc($queryData)){?>
<tr>
<td><?php echo $row['student_id'] ?></td>
<td><?php echo $row['surname'] ?></td>
<td><?php echo $row['firstname_other'] ?></td>
<td><?php echo $row['class_name'] ?></td>
<td><?php echo $row['janday1'] ?></td>
<td><?php echo $row['janday2'] ?></td>
<td><?php echo $row['janday3'] ?></td>
<td><?php echo $row['janday4'] ?></td>
<td><?php echo $row['janday5'] ?></td>
<td align="center">
<input type="checkbox" name="multiple[]" value="<?php echo $row['student_id'];?>">
</td>
</tr>
<?php }?>
</tbody>
</table>
</form><!--
<?php else: ?>
<h2> No data to display</h2>
<?php endif; ?>
</div><!--
<script type="text/javascript">
var day1 = document.getElementById('day1');
day1.onclick = function () {
var multiple = document.getElementsByName('multiple[]');
for (i = 0; i < multiple.length; i ++) {
multiple[i].checked = this.checked;
}
}
</script>
</body>
</html>