Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

PHP
$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:
SQL
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
<?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><!--close header-->
    <?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><!--close form-->
    <?php else: ?>
    <h2> No data to display</h2>
    <?php endif; ?>

</div><!--close wrapper-->
<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>
Posted
Updated 9-Mar-15 23:01pm
v3
Comments
Mohibur Rashid 10-Mar-15 4:51am    
you will have to send back the student_id to php and update query would have where condition based on student_id. otherwise your entire table will be changed. Also a better practice would be using limit

1 solution

Here is some issue:

1. you did not process class_group in your php code.
Change your select object as follow:
HTML
<SELECT name='class_group'>
   <OPTION value=''>Select gorup</OPTION>
   <OPTION value='a_relevent_number'>janday1</OPTION>
   ...
</SELECT>

Do not send class_group as text. If you do so, you will have to take other step to make sure the column does exists.
in php
PHP
$class_group=$_GET['class_group']; //here class group must have to be a number
//process the class_group and find out what value are you looking for. It would help you
//to patch a security hole.


then create the query as follow:
PHP
if(isset($ARRAY_HOLDING_COLUMN_NAME[$class_group])==true)
{
  sql = "UPDATE classlog SET ".$ARRAY_HOLDING_COLUMN_NAME." = 'P'  WHERE student_id IN (2, 3, 4, 5, any_student_id_SELECTED_BY_USER) ";
}
else{
 exit "Unwanted visitor"; 
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900