Inserting a Calculated Value into a MYSQL Database

ningboring · September 24, 2008

I am creating a simple update form for teachers at my university to mark essays. Teachers enter values and comments (not shown in the code below), the total is then added and stored in the array $tot. The student details are uploaded from an external data source. The calculation was easy enough, the tricky bit is getting the total ($tot) into the MYSQL database - can any one help please? The update form was created with Dreamweaver MX.

<?php require_once('Connections/myconn.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

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

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE results SET name=%s, pro=%s, org=%s, prs=%s, cnt=%s, lng=%s WHERE id=%s",
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['pro'], "int"),
                       GetSQLValueString($_POST['org'], "int"),
                       GetSQLValueString($_POST['prs'], "int"),
                       GetSQLValueString($_POST['cnt'], "int"),
                       GetSQLValueString($_POST['lng'], "int"),
                       GetSQLValueString($_POST['id'], "int"));

  mysql_select_db($database_myconn, $myconn);
  $Result1 = mysql_query($updateSQL, $myconn) or die(mysql_error());
}

mysql_select_db($database_myconn, $myconn);
$query_rs = "SELECT results.id , results.name, results.pro, results.org, results.prs, results.cnt, results.lng FROM results";
$rs = mysql_query($query_rs, $myconn) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
$totalRows_rs = mysql_num_rows($rs);
?>
<html>
<head>
<title>Update Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr >
      <td>Id:</td>
      <td><?php echo $row_rs['id']; ?></td>
    </tr>
    <tr >
      <td>Name:</td>
      <td><input type="hidden" name="name" value="<?php echo $row_rs['name']; ?>" size="32">
        <?php echo $row_rs['name']; ?> </td>
    </tr>
    <tr >
      <td>Pro:</td>
      <td><input type="text" name="pro" value="<?php echo $row_rs['pro']; $pro = $row_rs['pro'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Org:</td>
      <td><input type="text" name="org" value="<?php echo $row_rs['org']; $org = $row_rs['org'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Prs:</td>
      <td><input type="text" name="prs" value="<?php echo $row_rs['prs']; $prs = $row_rs['prs'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Cnt:</td>
      <td><input type="text" name="cnt" value="<?php echo $row_rs['cnt']; $cnt = $row_rs['cnt'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Lng:</td>
      <td><input type="text" name="lng" value="<?php echo $row_rs['lng']; $lng = $row_rs['lng'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Tot:</td>
      <td><?php $tot=$pro+$org+$prs+$cnt+$lng; echo $tot;?>
      </td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" value="Update Record">
      </td>
    </tr>
  </table>
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="id" value="<?php echo $row_rs['id']; ?>">
</form>
<p></p>
</body>
</html>
<?php
mysql_free_result($rs);
?>

Thanks.

Ningboring

MatthewJ · September 24, 2008

Create a hidden field in the form and set its value = to $tot. Then you will be able to grab it on submit. Unless I am missing something

ningboring · September 24, 2008

Thanks MathewJ

Tried that..but with no luck. The value still needs to be inserted into the database. I guess I may need to implode or serialize the array ($tot) first - this is what I cannot work out. The calculated total value is easily output to the screen - just inserting it to the db is proving to be a pain.

Ningboring

MatthewJ · September 24, 2008

If $tot is an array, echo $tot should display the word "array" not the value.

So placing the code below underneath the $tot calculation in the form

<input type='hidden' name='total' value ='<?php echo $tot ?>'>

Does not work?

ningboring · September 25, 2008

Hey MathewJ

I owe you and apology...you were right!! I was just being thick. I made a few other changes and got it working - quite simple really. Changes to the lines below helped. But the problem now is that when I go into the DB tot is not always updated. Any ideas? I can post the whole code again if necessary. Thanks.

$updateSQL = sprintf("UPDATE results SET name=%s, pro=%s, org=%s, prs=%s, cnt=%s, lng=%s, tot=%s WHERE id=%s",

GetSQLValueString($_POST['tot'], "int"),

<input name="tot" type="hidden" id="tot" value ='<?php echo $tot ?>'>

Ningboring

ningboring · September 27, 2008

Still can't get my array $tot to update properly on the database. I am creating a simple update form for teachers at my university to mark essays. Teachers enter values and comments (not shown in the code below), the total is then added and stored in the array $tot. The student's name etc are uploaded from an external data source. The calculation was easy enough, the tricky bit is getting the total ($tot) into the MYSQL database - can any one help please? The update form was created with Dreamweaver MX. Any ideas would be greatly appreciated. Thanks.

<?php require_once('Connections/myconn.php'); ?>
<?php
$currentPage = $HTTP_SERVER_VARS["PHP_SELF"];

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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;
}

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

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE results SET name=%s, pro=%s, org=%s, prs=%s, cnt=%s, lng=%s, tot=%s WHERE id=%s",
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['pro'], "int"),
                       GetSQLValueString($_POST['org'], "int"),
                       GetSQLValueString($_POST['prs'], "int"),
                       GetSQLValueString($_POST['cnt'], "int"),
                       GetSQLValueString($_POST['lng'], "int"),
   					   GetSQLValueString((is_array($_POST['tot']))? implode(',',$_POST['tot']):$_POST['tot'], "int"),
                       GetSQLValueString($_POST['id'], "int"));

  mysql_select_db($database_myconn, $myconn);
  $Result1 = mysql_query($updateSQL, $myconn) or die(mysql_error());
}

$maxRows_rs = 1;
$pageNum_rs = 0;
if (isset($HTTP_GET_VARS['pageNum_rs'])) {
  $pageNum_rs = $HTTP_GET_VARS['pageNum_rs'];
}
$startRow_rs = $pageNum_rs * $maxRows_rs;

mysql_select_db($database_myconn, $myconn);
$query_rs = "SELECT results.id , results.name, results.pro, results.org, results.prs, results.cnt, results.lng FROM results";
$query_limit_rs = sprintf("%s LIMIT %d, %d", $query_rs, $startRow_rs, $maxRows_rs);
$rs = mysql_query($query_limit_rs, $myconn) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);

if (isset($HTTP_GET_VARS['totalRows_rs'])) {
  $totalRows_rs = $HTTP_GET_VARS['totalRows_rs'];
} else {
  $all_rs = mysql_query($query_rs);
  $totalRows_rs = mysql_num_rows($all_rs);
}
$totalPages_rs = ceil($totalRows_rs/$maxRows_rs)-1;

$queryString_rs = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
  $params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rs") == false && 
        stristr($param, "totalRows_rs") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rs = "&" . implode("&", $newParams);
  }
}
$queryString_rs = sprintf("&totalRows_rs=%d%s", $totalRows_rs, $queryString_rs);
?>
<html>
<head>
<title>Update Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr >
      <td>Id:</td>
      <td><?php echo $row_rs['id']; ?></td>
    </tr>
    <tr >
      <td>Name:</td>
      <td><input type="hidden" name="name" value="<?php echo $row_rs['name']; ?>" size="32">
        <?php echo $row_rs['name']; ?> </td>
    </tr>
    <tr >
      <td>Pro:</td>
      <td><input type="text" name="pro"  value="<?php echo $row_rs['pro']; $pro = $row_rs['pro'];?>" size="32">
</td>
    </tr>
    <tr >
      <td>Org:</td>
      <td><input type="text" name="org" value="<?php echo $row_rs['org']; $org = $row_rs['org'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Prs:</td>
      <td><input type="text" name="prs" value="<?php echo $row_rs['prs']; $prs = $row_rs['prs'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Cnt:</td>
      <td><input type="text" name="cnt" value="<?php echo $row_rs['cnt']; $cnt = $row_rs['cnt'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Lng:</td>
      <td><input type="text" name="lng" value="<?php echo $row_rs['lng']; $lng = $row_rs['lng'];?>" size="32">
      </td>
    </tr>
    <tr >
      <td>Tot:</td>
      <td><?php $tot=$pro+$org+$prs+$cnt+$lng; 
  echo $tot;
  ?>
        <input name="tot" type="hidden" id="tot" value ="<?php echo $tot?>"></td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" value="Update Record">
      </td>
    </tr>
  </table>
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="id" value="<?php echo $row_rs['id']; ?>">
</form>
<p>
<table border="0" width="50%" align="center">
  <tr>
    <td width="23%" align="center">
      <?php if ($pageNum_rs > 0) { // Show if not first page ?>
      <a href="<?php printf("%s?pageNum_rs=%d%s", $currentPage, 0, $queryString_rs); ?>">First</a>
      <?php } // Show if not first page ?>
    </td>
    <td width="31%" align="center">
      <?php if ($pageNum_rs > 0) { // Show if not first page ?>
      <a href="<?php printf("%s?pageNum_rs=%d%s", $currentPage, max(0, $pageNum_rs - 1), $queryString_rs); ?>">Previous</a>
      <?php } // Show if not first page ?>
    </td>
    <td width="23%" align="center">
      <?php if ($pageNum_rs < $totalPages_rs) { // Show if not last page ?>
      <a href="<?php printf("%s?pageNum_rs=%d%s", $currentPage, min($totalPages_rs, $pageNum_rs + 1), $queryString_rs); ?>">Next</a>
      <?php } // Show if not last page ?>
    </td>
    <td width="23%" align="center">
      <?php if ($pageNum_rs < $totalPages_rs) { // Show if not last page ?>
      <a href="<?php printf("%s?pageNum_rs=%d%s", $currentPage, $totalPages_rs, $queryString_rs); ?>">Last</a>
      <?php } // Show if not last page ?>
    </td>
  </tr>
</table>
</p>
</body>
</html>
<?php
mysql_free_result($rs);
?>

Ningboring

Sign In

Inserting a Calculated Value into a MYSQL Database

Recommended Posts

ningboring

Link to comment

Share on other sites

MatthewJ

Link to comment

Share on other sites

ningboring

Link to comment

Share on other sites

MatthewJ

Link to comment

Share on other sites

ningboring

Link to comment

Share on other sites

ningboring

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information