database will not update

mrgrinch12

I have a two page for to update multiple rows in one column of my database. The form appears ok, yet the data never updates, I am only left with blank rows...any suggestions on mis-named variables or is it something else.

Thanks for your help.

Page one code:

<?php require('/conn.php'); ?>
<?php
session_start();
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}

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

$colname_DetailRS1 = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_DetailRS1 = $_SESSION['MM_Username'];
}
mysql_select_db($database_conn, $conn);
$query_DetailRS1 = sprintf("SELECT PlayerID, FanID, Type, InjuryID, Player, StatusID, StatusChangeChoice, CurrentWeek, GMEmail FROM tblplayers WHERE GMEmail = %s ORDER BY tblplayers.Type Asc", GetSQLValueString($colname_DetailRS1, "text"));
$DetailRS1 = mysql_query($query_DetailRS1, $conn) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);


$colname_rsGMDetail = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_rsGMDetail = $_SESSION['MM_Username'];
}
mysql_select_db($database_conn_ssfhl, $conn_ssfhl);
$query_rsGMDetail = sprintf("SELECT GMFirst, GMLast FROM tbluseradmin WHERE GMEmail = %s", GetSQLValueString($colname_rsGMDetail, "text"));
$rsGMDetail = mysql_query($query_rsGMDetail, $conn_ssfhl) or die(mysql_error());
$row_rsGMDetail = mysql_fetch_assoc($rsGMDetail);
$totalRows_rsGMDetail = mysql_num_rows($rsGMDetail);

// Count rows in table
$count=mysql_num_rows($DetailRS1);

// start a counter in order to number the input fields for each record
$i = 0;
?>

<body>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form id="form1" name="form1" method="post" action="update.php">
<td>
<tr class="statshdr">
<td align="center" bgcolor="#FFCC00"><strong>ID</strong></td>
<td align="center" bgcolor="#FFCC00">Position</td>
<td align="center" bgcolor="#FFCC00">Player</td>
<td align="center" bgcolor="#FFCC00">Health</td>
<td align="center" bgcolor="#FFCC00">Current Status</td>
<td align="center" bgcolor="#FFCC00"><strong>Change Status</strong></td>
</tr>
<?
// Fetch record rows in $result by while loop and put them into $row.
while($row=mysql_fetch_assoc($DetailRS1)){
?>
<tr>
<td bgcolor="#FFFFCC"><? echo $row['PlayerID']; ?></td>
<td bgcolor="#FFFFCC"><? echo $row['Type']; ?></td>
<td bgcolor="#FFFFCC"><? echo $row['Player']; ?></td>
<td bgcolor="#FFFFCC"><? echo $row['InjuryID']; ?></td>
<td bgcolor="#FFFFCC"><? echo $row['StatusID']; ?></td>
<td bgcolor="#FFFFCC"><label>
  <select name="select<? echo $row['id'];?>" id="select<? echo $row['id'];?>"/>
    <option value="Active" selected="selected">Active</option>
    <option value="Bench">Bench</option>
  </select>
  </label>
</form>
</td>
</tr>
<? } // End while loop. ?>
</table>
<input type="submit" name="Submit" value="Update" />
</body>

update page code:

<?php require_once('../conn.php'); ?>
<?php
session_start();
// Select all data records in table "tblplayers" and put them into $result.
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}

mysql_select_db($database_conn, $conn);
$query_result = "SELECT * FROM tblplayers ORDER BY Type ASC";
$result = mysql_query($query_result, $conn) or die(mysql_error());
$row_result = mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result);

if($_POST['Submit']){ // If receive Submit button variable.

// Fetch record rows in $result by while loop and put them into $row.
while($row=mysql_fetch_assoc($result)){
// Get the posted value "name_ID value" from form.php. This variable change it's value by while loop.
$select=$_POST["select".$row[id]];

// Update field "name", matching with "id" value by while loop.
mysql_query("UPDATE tblplayers SET StatusChangeChoice = '$select' where PlayerID = '$row[PlayerID]'");
}
echo "--- Update Complete ---";
}
?>

<body>
<p><?php //echo $select['$i'];
echo "<pre>";
var_dump($select);
echo "</pre>";
print_r($result); 
var_dump($_POST);?> </p>
</body>

zypher11

You don't get any errors or warnings?

dabaR

Not exactly the cause of your problem, but "'" . $theValue . "'" is equivalent to "'$theValue'" and has fewer characters, so I suggest you use it.

Also, function GetSQLValueString is duplicated in your code. You should extract it into it's own file then require_once() it in all the scripts that need to use it.

mrgrinch12

No, I don't get any warnings when the page is submitted.

Thanks for the info on simplification of the GetStringValue, I'll give that a whirl.

dabaR

Did you try the mysql_query() || die('Error:' . mysql_error()); format?

It is shown, though syntactically slightly differently, in the first example here: http://ca2.php.net/manual/en/function.mysql-query.php

mrgrinch12

I adjusted the update query to add the die feature....still nothing

// Update field "StatusChangeChoice", matching with "PlayerID" value by while loop.
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$select' where PlayerID = '$row[PlayerID]'";
$update = mysql_query($query_update, $conn_ssfhl) or die(mysql_error());
if (!$update) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query_update;
    die($message);
}
}
echo "--- Update Complete ---";
}
?>

dabaR

That's great. Now echo $query_update, and then run that manually in the DBMS.

mrgrinch12

still nothing

dabaR

So when you manually run the query in the database, no update?

Show the query. The exact output of $query_update

mrgrinch12

Error

SQL query: Documentation

$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$select' where PlayerID = '$row[PlayerID]'";

MySQL said: Documentation
#1064 - 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 '$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$select' where Play' at line 1

dabaR

What I mean is, show the query you paste into mysql.

The output of echo $query_update

mrgrinch12

Here is the query I tried to run in mysql:
UPDATE tblplayers SET StatusChangeChoice = '$select' where PlayerID = '$row[PlayerID]'

It gave me zero rows affected

dabaR

In your code, you have a line that says $query_update = ...

On the next line, put echo $query_update, and then load the script in your browser. You should get the UPDATE statement in plain text somewhere in your screen. Please paste it here.

mrgrinch12

is there something wrong with the variables?

mrgrinch12

The URL is not valid and cannot be loaded.

mrgrinch12

all i get is NULL