0 Replies Latest reply on Jul 16, 2011 1:05 PM by sir_amin

    Update multiple rows in a dynamic table Dreamweaver CS5.5

    sir_amin Level 1

      hello there

       

      i want to update multiple rows which comes from a dynamic table in Dreamweaver CS5 (a loop in php) here is my Mysql table :

       

      sql code
      CREATE TABLE `register`.`s_lessons` (
      `lid` int( 5 ) NOT NULL ,
      `sid` int( 9 ) NOT NULL ,
      `term` int( 5 ) NOT NULL ,
      `tid` int( 5 ) NOT NULL ,
      `point` double NOT NULL DEFAULT '0',
      PRIMARY KEY ( `lid` , `sid` , `term` ) ,
      KEY `tid` ( `tid` ) ,
      KEY `point` ( `point` )
      ) ENGINE = MYISAM DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci;

       

      and this is my page source code:

       

      php file
      <?php require_once('../Connections/register.php'); ?>
      <?php
      session_start
      ();
      if (!
      function_exists("GetSQLValueString")) {
      function
      GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
      {
        if (
      PHP_VERSION < 6) {
         
      $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
        }

       
      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

        switch (
      $theType) {
          case
      "text":
           
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
            break;   
          case
      "long":
          case
      "int":
           
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
            break;
          case
      "double":
           
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
            break;
          case
      "date":
           
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
            break;
          case
      "defined":
           
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
            break;
        }
        return
      $theValue;
      }
      }

      $colname1_rs1 = "-1";
      if (isset(
      $_GET['term'])) {
       
      $colname1_rs1 = $_GET['term'];
      }
      $colname_rs1 = "-1";
      if (isset(
      $_GET['lid'])) {
       
      $colname_rs1 = $_GET['lid'];
      }
      $colname2_rs1 = "-1";
      if (isset(
      $_SESSION['tid'])) {
       
      $colname2_rs1 = $_SESSION['tid'];
      }
      mysql_select_db($database_register, $register);
      $query_rs1 = sprintf("SELECT s_lessons.sid, s_lessons.lid, s_lessons.term, s_lessons.tid, s_lessons.point FROM s_lessons WHERE s_lessons.lid = %s AND s_lessons.term = %s AND s_lessons.tid  = %s", GetSQLValueString($colname_rs1, "int"),GetSQLValueString($colname1_rs1, "int"),GetSQLValueString($colname2_rs1, "int"));
      $rs1 = mysql_query($query_rs1, $register) or die(mysql_error());
      $row_rs1 = mysql_fetch_assoc($rs1);
      $totalRows_rs1 = mysql_num_rows($rs1);
      $count=mysql_num_rows($rs1);



      $editFormAction = $_SERVER['PHP_SELF'];
      if (isset(
      $_SERVER['QUERY_STRING'])) {
       
      $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
      }

      for (
      $j = 0, $len = count($_POST['lid']); $j < $len; $j++) {
      if ((isset(
      $_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
       
      $updateSQL = sprintf("UPDATE s_lessons SET point=%s WHERE tid=%s, lid=%s, sid=%s, term=%s",
                            
      GetSQLValueString($_POST['point'] [$j], "double"),
                                  GetSQLValueString($_SESSION['tid'], "int"),
                            
      GetSQLValueString($_POST['lid'] [$j], "int"),
                                  GetSQLValueString($_POST['sid'] [$j], "int"),
                                  GetSQLValueString($_POST['term'] [$j], "int"));

       
      mysql_select_db($database_register, $register);
       
      $Result1 = mysql_query($updateSQL, $register) or die(mysql_error());
      }
       
      $updateGoTo = "student_lists.php";
        if (isset(
      $_SERVER['QUERY_STRING'])) {
         
      $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
         
      $updateGoTo .= $_SERVER['QUERY_STRING'];
        }
       
      header(sprintf("Location: %s", $updateGoTo));
      }

      ?>

      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml">
      <head>
      <meta name="keywords" content="" />
      <meta name="description" content="" />
      <meta http-equiv="content-type" content="text/html; charset=utf-8" />
      <title>r</title>
      <link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" />
      <link href="styles/in_styles.css" rel="stylesheet" type="text/css" media="screen" />
      </head>
      <body>
      <div id="wrapper">
           <div id="header-wrapper">
               
           </div>
           <!-- end #header -->
           <div id="page">
                <div id="page-bgtop">
                     <div id="page-bgbtm">
                          <div id="content">
                               <div class="post">
                                 <div style="clear: both;">
                              <form name="form1" id="form1" method="post" action="<?php echo $editFormAction; ?>">
                              <table border="1" align="center">
                                <tr>
                                  <th>Student ID</th>
                                  <th>Lesson ID</th>
                                  <th>Semester</th>
                                  <th>Point</th>
                                 
                                </tr>
                                <?php do { ?>
                                  <tr>
                                    <td class="data"><label for="sid[]"></label>
                                    <input name="sid[]" type="text" id="sid[]" value="<?php echo $row_rs1['sid']; ?>" size="9" readonly="readonly" /></td>
                                    <td class="data"><label for="lid[]"></label>
                                    <input name="lid[]" type="text" id="lid[]" value="<?php echo $row_rs1['lid']; ?>" size="5" readonly="readonly" /></td>
                                    <td class="data"><label for="term[]"></label>
                                    <input name="term[]" type="text" id="term[]" value="<?php echo $row_rs1['term']; ?>" size="4" readonly="readonly" /></td>
                                    <td><label for="point[]"></label>
                                      <input name="point[]" type="text" id="point[]" value="<?php echo $row_rs1['point']; ?>" size="4" />                             
                                </tr>
                               
                                  <?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>
                              </table>
                              <p>
                                <input type="submit" name="Submit" id="Submit" value="Submit" />
                                <input type="hidden" name="MM_update" value="form1" />
                              </p>
                              </form>
                                 </div>
                               </div>
                          <div style="clear: both;">
                      </div>
                          </div>
                          <!-- end #content -->
                         
                          <!-- end #sidebar -->
                          <div style="clear: both;"> </div>
                     </div>
                </div>
           </div>
           <!-- end #page -->
      </div>
      <!-- end #footer -->
      </body>
      </html>
      <?php
      mysql_free_result
      ($rs1);
      ?>

       

       

       

      All i want is that when users click on SUBMIT button values of point column in s_lessons(database table) be updated by new entries from user.

       

      i did my best and result with that code is :

       

      You  have an error in your SQL syntax; check the manual that corresponds to  your MySQL server version for the right syntax to use near ' lid=888,  sid=860935422, term=902' at line 1

       

      I would appreciate any idea.

       

      with prior thanks