[RESOLVED] Update multiple record, but row based on form variable

pb1uk

Hello all,

I am trying to update all records in a table in a database. However the row that is updated varies and depends on a value passed from a drop down menu on the previous page. The menu passes in a value that is stored as $scoreweek. This works ok and in the dynamic table it displays the correct values. However this is where my success ended though. When i click submit to update the records it doesn't seem to work and it says that scoreweek is undefined.

I tried out the update to check it was working by taking out $scoreweek and replacing it with the actual row name, so the row to update wasn't based on a value being passed and had its acctual name.

Below is the code i have upto now. I have been trying different bits and pieces, researching and scratching my head for hours and i just can't crack it. Any advice or help would really be appreciated. Thanks for taking the time to read this.

<?php require_once('Connections/conn_fyp09.php'); ?>
<?php
if(isset($_POST['submit'])){
	foreach($_POST['$scoreweek'] as $key=>$value){
		$sql1="UPDATE tbl_gks SET '$scoreweek' ='$value' WHERE gkid_gk='$key'";
		$result1=mysql_query($sql1);
	}
	echo "update complete";	
}
mysql_close();
?>
<?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;
}
}
$scoreweek = "-1";
if(isset($_POST['pickweek'])){
$scoreweek = $_POST['pickweek'];
}
mysql_select_db($database_conn_fyp09, $conn_fyp09);
$query_rs_test = "SELECT tbl_gks.gkid_gk,tbl_gks.gkname_gk,tbl_gks.$scoreweek FROM tbl_gks";
$rs_test = mysql_query($query_rs_test, $conn_fyp09) or die(mysql_error());
$row_rs_test = mysql_fetch_assoc($rs_test);
$totalRows_rs_test = mysql_num_rows($rs_test);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Administrator Update Gk Scores</title>
</head>

<body>

<form name="addgkscores" method="post" action="" >
<table border="1">
  <tr>
  <td>id</td>
    <td>gkname_gk</td>

<td>points</td>
  </tr>
  <?php while($row_rs_test=mysql_fetch_array($rs_test)) { ?>
    <tr>
    <td><?php echo $row_rs_test['gkid_gk']; ?></td>
      <td><?php echo $row_rs_test['gkname_gk']; ?></td>

  <td><input name="<?php echo $scoreweek; ?>[<?php echo $row_rs_test['gkid_gk']; ?>]" type="text" id="<?php echo $scoreweek; ?>" value="<?php echo $row_rs_test["$scoreweek"]; ?>" /></td>
</tr>

<?php } ?>
</table>

<input type="submit" value="Insert scores" name="submit"/>
</form>
</body>
</html>
<?php
mysql_free_result($rs_test);
?>

bradgrafelman

First problem I notice is here:

foreach($_POST['$scoreweek']

I'm guessing that you don't have a field name that contains a literal dollar sign followed by 'scoreweek'. What's the dollar sign doing in the array index, then?

Also, inside the loop you've got two problems with your query:

$sql1="UPDATE tbl_gks SET '$scoreweek' ='$value' WHERE gkid_gk='$key'";

For one, $scoreweek is undefined - perhaps you meant $_POST['scoreweek'] instead?

Also, single quotes in a MySQL query denote strings, not identifiers (e.g. column names). For that, either don't use any quotes at all, or use backticks (`) instead.

In addition, note that your code is vulnerable to SQL injection attacks (or just plain SQL errors). User-supplied data should never be placed directly into a SQL query. Instead, it must be sanitized with a function such as [man]mysql_real_escape_string/man (hint: you define a function, GetSQLValueString, that would do the job.. and yet you never actually use it).

pb1uk

Hi,

Thanks for your response. You're right i don't have a field called $scorename! The field to be updated is passed as a form variable from the previous page and is stored as $scoreweek and used in the query to get the relevant information from the database and to display it.

What would be the proper way to pass the name of the field to be updated to the update part? (i though $_POST['$scoreweek'] would do it, which is obviously wrong!)

I do understand the code maybe vunerable, but it will be only used by the site administrator to add scores to the database.

pb1uk

bradgrafelman

Where is $scoreweek ever defined?

pb1uk

bradgrafelman;10947713 wrote:
Where is $scoreweek ever defined?

In the query to get the fields to be updated from the database the following code gets the name of field passed from te drop down menu of the previous page.

$scoreweek = "-1"; 
if(isset($_POST['pickweek'])){ 
$scoreweek = $_POST['pickweek']; 
} 
mysql_select_db($database_conn_fyp09, $conn_fyp09); 
$query_rs_test = "SELECT tbl_gks.gkid_gk,tbl_gks.gkname_gk,tbl_gks.$scoreweek FROM tbl_gks";

i don't know how to pass this to the update query, please can someone help me, i just can't figure it out 😕

pb1uk

Problem was solved by adding a hidden field to the form that could pass the name of the row in the table to be updated to the update function:

<input type="hidden" name="week" value="<?php echo $scoreweek; ?>">

then the update became:

<?php
if(isset($_POST['submit'])){	
	$score = $_POST['week'];
	foreach($_POST['foo'] as $key=>$value){

$sql1="UPDATE tbl_gks SET `$score` ='$value' WHERE gkid_gk='$key'";
	$result1=mysql_query($sql1);		
}
echo "update complete";
}
?>