How to insert a calculated value from 3 other columns and insert to a database

Question

freiheit 0 Newbie Poster

16 Years Ago

Hello ,

Im struggling lately to insert a variable to a database table.

The total columns of the table are 52 :confused:

Here is the insert into database script :

<?php require_once('../Connections/my_connection.php'); ?>
<?php
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;
}
}

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

// variable creation
$variable_1 = @($row_KINISEIS['kinisis_1c']/$row_KINISEIS['kinisis_1b']) * $row_KINISEIS['kinisis_1a'];
$variable_2 = @($row_KINISEIS['100c']/$row_KINISEIS['100b']) * $row_KINISEIS['100a'];
$variable_3 = @($row_KINISEIS['super_1c']/$row_KINISEIS['kinisis_1b']) * $row_KINISEIS['super_1a'];
$variable_4 = @($row_KINISEIS['95_1c']/$row_KINISEIS['95_1b']) * $row_KINISEIS['95_1a'];
$variable_5 = @($row_KINISEIS['95_2c']/$row_KINISEIS['95_2b']) * $row_KINISEIS['95_2a'];
$variable_6 = @($row_KINISEIS['super_2c']/$row_KINISEIS['super_2b']) * $row_KINISEIS['super_2a'];
$variable_7 = @($row_KINISEIS['kinisis_2c']/$row_KINISEIS['kinisis_2b']) * $row_KINISEIS['kinisis_2a'];

//begin of insert statement
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "insert_form")) {
  $insertSQL = sprintf("INSERT INTO kiniseis (kinisis_1a, `100a`, super_1a, `95_1a`, `95_2a`, super_2a, kinisis_2a, kinisis_1b, `100b`, super_1b, `95_1b`, `95_2b`, super_2b, kinisis_2b, kinisis_1c, `100c`, super_1c, `95_1c`, `95_2c`, super_2c, kinisis_2c, kinisis_1d, `100d`, super_1d, `95_1d`, `95_2d`, super_2d, kinisis_2d, kinisis_1e, `100e`, super_1e, `95_1e`, `95_2e`, super_2e, kinisis_2e, kinisis_a, `100_a`, `95_a`, super_a, kinisis_b, `100_b`, `95_b`, super_b, `date`, fpa, `$variable_1` , `$variable_2` , `$variable_3` , `$variable_4` , `$variable_5` , `$variable_6` , `$variable_7`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                       GetSQLValueString($_POST['1'], "double"),
                       GetSQLValueString($_POST['2'], "double"),
                       GetSQLValueString($_POST['3'], "double"),
                       GetSQLValueString($_POST['4'], "double"),
                       GetSQLValueString($_POST['5'], "double"),
                       GetSQLValueString($_POST['6'], "double"),
                       GetSQLValueString($_POST['7'], "double"),
                       GetSQLValueString($_POST['8'], "double"),
                       GetSQLValueString($_POST['9'], "double"),
                       GetSQLValueString($_POST['10'], "double"),
                       GetSQLValueString($_POST['11'], "double"),
                       GetSQLValueString($_POST['12'], "double"),
                       GetSQLValueString($_POST['13'], "double"),
                       GetSQLValueString($_POST['14'], "double"),
                       GetSQLValueString($_POST['15'], "double"),
                       GetSQLValueString($_POST['16'], "double"),
                       GetSQLValueString($_POST['17'], "double"),
                       GetSQLValueString($_POST['18'], "double"),
                       GetSQLValueString($_POST['19'], "double"),
                       GetSQLValueString($_POST['20'], "double"),
                       GetSQLValueString($_POST['21'], "double"),
                       GetSQLValueString($_POST['22'], "double"),
                       GetSQLValueString($_POST['23'], "double"),
                       GetSQLValueString($_POST['24'], "double"),
                       GetSQLValueString($_POST['25'], "double"),
                       GetSQLValueString($_POST['26'], "double"),
                       GetSQLValueString($_POST['27'], "double"),
                       GetSQLValueString($_POST['28'], "double"),
                       GetSQLValueString($_POST['29'], "double"),
                       GetSQLValueString($_POST['30'], "double"),
                       GetSQLValueString($_POST['31'], "double"),
                       GetSQLValueString($_POST['32'], "double"),
                       GetSQLValueString($_POST['33'], "double"),
                       GetSQLValueString($_POST['34'], "double"),
                       GetSQLValueString($_POST['35'], "double"),
                       GetSQLValueString($_POST['36'], "double"),
                       GetSQLValueString($_POST['37'], "double"),
                       GetSQLValueString($_POST['38'], "double"),
                       GetSQLValueString($_POST['39'], "double"),
                       GetSQLValueString($_POST['40'], "double"),
                       GetSQLValueString($_POST['41'], "double"),
                       GetSQLValueString($_POST['42'], "double"),
                       GetSQLValueString($_POST['43'], "double"),
                       GetSQLValueString($_POST['date'], "date"),
                       GetSQLValueString($_POST['select_fpa'], "double"),
		       GetSQLValueString($_POST['"$variable_1"'], "double"),
                       GetSQLValueString($_POST['"$variable_2"'], "double"),
                       GetSQLValueString($_POST['"$variable_3"'], "double"),
                       GetSQLValueString($_POST['"$variable_4"'], "double"),
                       GetSQLValueString($_POST['"$variable_5"'], "double"),
                       GetSQLValueString($_POST['"$variable_6"'], "double"),
                       GetSQLValueString($_POST['"$variable_7"'], "double"));

  mysql_select_db($database_my_connection, $my_connection);
  $Result1 = mysql_query($insertSQL, $my_connection) or die(mysql_error());

  $insertGoTo = "../confirmation.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>

Can someone find the mistake here , cause its the 1st time that im trying to do this thing :sad:

Thank you very much in advance :)

php

Edited 16 Years Ago by freiheit because: n/a

2 Contributors
2 Replies
114 Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by freiheit

Dani AI

Generated 9 Months Ago

A few focused notes that build on pointing out the missing comma and on ’s followup attempt.

The real problems are logical, not just syntax. Computed numbers are PHP variables — they must be passed to the INSERT as values, not treated as column names and not looked up again in $POST. Also avoid hiding errors with the suppression operator (@) because it masks division-by-zero and type problems. The old `mysql*functions are deprecated; move tomysqliorPDO` and use prepared statements to avoid quoting/escaping mistakes and SQL injection.

Checklist and troubleshooting steps:

Verify each computed value before the INSERT (use var_dump() or error_log()).
Guard against division-by-zero and non-numeric input (use is_numeric() or a conditional).
Pass the computed variable itself to the query builder (or bind it), not $_POST[...].
Use prepared statements (PDO or mysqli) and bind parameters instead of interpolating variables into SQL.
Confirm column names and types in the DB match the values being inserted (floats/decimals vs strings).

Example (PDO, minimal) — compute in PHP, then bind the computed values:

$a = (float) $_REQUEST['a_field'];
$b = (float) $_REQUEST['b_field'];
$computed = ($b != 0) ? ($a * ($b / 100.0)) : 0.0;

$pdo = new PDO('mysql:host=localhost;dbname=dbname;charset=utf8', 'user', 'pass', [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$sql = "INSERT INTO kiniseis (col_a, col_b, computed_col) VALUES (:a, :b, :comp)";
$stmt = $pdo->prepare($sql);
$stmt->execute([':a' => $a, ':b' => $b, ':comp' => $computed]);

Quick wrap: fix the comma, stop treating computed values as form keys, remove @, add simple validation, and use prepared statements. This sequence will make the zeros disappear and reveal real errors if any remain.

All 2 Replies

pritaeas 2,276 Most Valuable Poster

16 Years Ago

Line 50 is missing a comma.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

freiheit 0 Newbie Poster · Answer 1 · 2010-07-13T22:58:17+00:00

Line 50 is missing a comma.

To simplify it i give you the below code , but i see only 0 in my database from the values that are supposed to be inserted

// declare variables
$venzini_kinisis_1 = @($_REQUEST['15']/$_REQUEST['8']) * $_REQUEST['1'];
$venzini_100ara = @($_REQUEST['16']/$_REQUEST['9']) * $_REQUEST['2'];
$venzini_super_1 = @($_REQUEST['17']/$_REQUEST['10']) * $_REQUEST['3'];
$venzini_95_1 = @($_REQUEST['18']/$_REQUEST['11']) * $_REQUEST['4'];
$venzini_95_2 = @($_REQUEST['19']/$_REQUEST['12']) * $_REQUEST['5'];
$venzini_super_2 = @($_REQUEST['20']/$_REQUEST['13']) * $_REQUEST['6'];
$venzini_kinisis_2 = @($_REQUEST['21']/$_REQUEST['14']) * $_REQUEST['7'];

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "insert_form")) {
  $insertSQL = sprintf("INSERT INTO kiniseis (polisi_kinisis_1, polisi_100aras, polisi_super_1, polisi_95_1, polisi_95_2, polisi_super_2, polisi_kinisis_2) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       
                       GetSQLValueString($_POST['".$venzini_kinisis_1"'], "double"),
                       GetSQLValueString($_POST['".$venzini_100ara"'], "double"),
                       GetSQLValueString($_POST['".$venzini_super_1"'], "double"),
                       GetSQLValueString($_POST['".$venzini_95_1"'], "double"),
                       GetSQLValueString($_POST['".$venzini_95_2"'], "double"),
                       GetSQLValueString($_POST['".$venzini_super_2"'], "double"),
                       GetSQLValueString($_POST['".$venzini_kinisis_2"'], "double"));

How to insert a calculated value from 3 other columns and insert to a database

Dani AI

Recommended Answers Collapse Answers

All 2 Replies

Recommended Answers