Dynamic update

Tezread

I want to be able to perform an update using values from a form.

I know I need to use some sort of loop, and have indeed used such a method to perform looped inserts thus:

However updates as you know are slightly different in that the value follows immediately after the fieldname declaration in the SQL query thus:
"UPDATE <table> SET <fieldnames> = '<form_values>' WHERE X=Y";

I have established therefore that by using two arrays : one for the fields names
and the other for their respective values:


<?php
include("include/session.php");
$sql = 0;
$sql1 = 0;
$sql2 = 0;
$sql3 = 0;
$table = "qualityuser";
$username = $_SESSION['username'];
include("connect.php"); 
$sql1 = "UPDATE $table SET ";
$i=0; 
foreach($HTTP_GET_VARS as $key=>$val) { 
	$fieldname[$i] = $key;
	$values[$i] = $val;
	$sql2 .= $fieldname[$i] . "=" . "'" . $values[$i] . "'" . ",";
	$sql3 = substr($sql2,0,-1);
	$i++; 
	}//----end foreach
	$sql = $sql1 . $sql3 . " WHERE username='$username'";

echo $sq11;
echo $sq12;
echo $sq13;

mysql_query($sql, $connection) or die(mysql_error()); 
?>

My efforts though result in an undefined variable error message for $sql1, $sql2, and $sql3.
Can anyone suggest a way of employing, such loop to construct the required UPDATE query??

leatherback

hm....

I suppose I know where this thread came from ;-)

Here is an example of how this could work. Untested, and on top of my head, so might have some parsers:

$valid_fields = array(
'Field1' => 'Colum1',
'Field2' => 'Colum2',
'Field3' => 'Colum3');

// create a function to handle it
function getpost($fields)
  {
  $sql = array();
  global $_POST;
  foreach($_POST as $key => $value)
    {
    if(array_key_exists($key, $fields))
      {
      // Do some data verification here, e.g., 
      $val = mysql_real_escape_string($value);
      $sql[] = "$fields[$key] = '$val'";
      }
    }
  $sql = implode($sql, ", ");
  return $sql;
  }

// use it:
$update = getpost($valid_fields);

Tezread

Ahh Leatherback- nice to hear from you 😃
I can see how this function handles the fields and columns but how would you incorporate the $sql that does the UPDATE... SET etc

leatherback

😃 If I kick someone down, and they take notice, then there is not option but to give them a hand, is there?

Basically, what you see happening is that the function validates the userinput

Checks whether the submitted keys in the POST array are valid for your form, and links them to the colum names in your database (This way, you do not give away that golden nugget of info).
Takes the basic bite out of standard SQL-inject practices.

What it returns should be a string of just <col>='<value>' separated by colums.

So, as per my example, you could use $update to build your sql around:

$sql = "UPDATE <table> SET ".$update." WHERE X=Y";

Have a bit of a play, and echo out some of the intermediate steps. I know it is a horror to figure these things out, but it is the only way to learn it properly.
J.