logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > PHP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 07-16-2011, 07:30 PM   #1
sir_amin
 
Join Date: Jul 2011
Posts: 2
Red face Update multiple rows in a dynamic table Dreamweaver CS5

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 :

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:

Code:
<?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'] [$j], "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;">&nbsp;</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
sir_amin is offline   Reply With Quote
Old 07-26-2011, 12:26 PM   #2
davidj
davidj's Avatar
 
Join Date: Sep 2005
Location: The Toon (newcastle upon Tyne)
Posts: 8,256
Default

I rarely help with Dreamweaver produced code but you could try...

PHP Code:

UPDATE 
  s_lessons 
SET 
  point
='%s' 
WHERE 
  tid
='%s' 
AND 
  
lid='%s'
AND 
  
sid='%s' 
AND 
term='%s' 
__________________
Would you like to learn PHP from me? Check out -> www.codezenith.co.uk
davidj is offline   Reply With Quote
Old 07-26-2011, 03:04 PM   #3
sir_amin
 
Join Date: Jul 2011
Posts: 2
Default

thank you very much.i had solved it about two weeks ago anyway.
sir_amin is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 05:50 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com