update query does nothing
Moderator: General Moderators
-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
update query does nothing
I have two pages that I am using to update a mysql database. Page 1 has the form and shows the rows available for updating. Each row contains a select drop down box to make your choice and then the form is submitted. The for page shows the correct data and appears to function just fine. Page 2 has the update query for the database and is referenced by page 1. The bottom line is the update query is having no effect on the database. Any help with where to look would be great as I am major php rookie and have already been at this for several days. Code for both pages listed below:
<?php
session_start();
?>
<!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=iso-8859-1" />
<title>form</title>
</head>
<?php require('/conn.php'); ?>
<?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;
}
}
$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, $conn);
$query_rsGMDetail = sprintf("SELECT GMFirst, GMLast FROM tbluseradmin WHERE GMEmail = %s", GetSQLValueString($colname_rsGMDetail, "text"));
$rsGMDetail = mysql_query($query_rsGMDetail, $conn) 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);
$player = $_POST['hideplayerID'];
// Post variables
$submit = $_POST['submit'];
?>
<body bgcolor="#FFFFFF">
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form id="frmupdate" name="frmupdate" method="post" action="update.php">
<tr>
<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>
<?php
// Fetch record rows in $DetailRS1 by while loop and put them into $row.
while($row=mysql_fetch_assoc($DetailRS1)){
?>
<tr>
<td bgcolor="#FFFFCC"><? echo $row['PlayerID']; ?>
<input name="hideplayerID" type="hidden" id="hideplayerID" value="<?php echo $row_DetailRS1['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[]">
<option value="Active" selected="selected">Active</option>
<option value="Bench">Bench</option>
</select>
</label></td>
</tr>
</form>
<?php } // End while loop. ?>
</table>
<input type="submit" name="submit" value="Update" />
</body>
</html>
Page 2 (Update):
<!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>update</title>
</head>
<body>
<?php require_once('../Connections/conn.php'); ?><?php
// If receive Submit button variable.
if($_POST['submit']){
require_once('../Connections/conn.php');
// 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;
}
}
# Script 2.5 - update.php
//Validate the status and combat Magic Quotes, if neccessary.
if (!empty($_REQUEST['select'])) {
$select = stripslashes($_REQUEST['select']);
} else {
$select = NULL;
echo '<p>You forgot to change your player status!</p>';
}
//If everything else is okay, print the message
echo "<p>Thank you</p>";
} else {//Selection was not made on form.
echo "<p>Please go back and complete your selections.</p>";
}
mysql_select_db($database_conn, $conn);
$query_result = "SELECT PlayerID FROM tblplayers ORDER BY PlayerID ASC";
$result = mysql_query($query_result, $conn) or die(mysql_error());
$row_result = mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result);
// Fetch record rows in $result by while loop and put them into $row.
while($row = mysql_fetch_assoc($result)){
//imploe the array variables
$select = array("Active", "Bench");
//start a counter in order to number the input fields for each record
$selectChoice = implode(" ", $select);
for($i = 0; $i < $count; $i++){
if ($select[$i] != ""){
echo "selectChoice #$i = $select[$i] <br />";
// Update field "StatusChangeChoice", matching with "PlayerID" value by while loop.
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$selectChoice' where PlayerID = (''$player[$i]')";
}
}
$update = mysql_query($query_update, $conn) or die(mysql_error());
if (!$update) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query_update;
die($message);
}
}
echo "--- Update Complete ---";
echo $query_update;
?>
<p><?php
echo "<pre>";
var_dump($select);
echo "</pre>";
print_r($row[PlayerID]);
var_dump($_POST);
echo $update;
?> </p>
</body>
</html>
<?php
mysql_free_result($result);
mysql_free_result($update);
?>
<?php
session_start();
?>
<!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=iso-8859-1" />
<title>form</title>
</head>
<?php require('/conn.php'); ?>
<?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;
}
}
$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, $conn);
$query_rsGMDetail = sprintf("SELECT GMFirst, GMLast FROM tbluseradmin WHERE GMEmail = %s", GetSQLValueString($colname_rsGMDetail, "text"));
$rsGMDetail = mysql_query($query_rsGMDetail, $conn) 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);
$player = $_POST['hideplayerID'];
// Post variables
$submit = $_POST['submit'];
?>
<body bgcolor="#FFFFFF">
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form id="frmupdate" name="frmupdate" method="post" action="update.php">
<tr>
<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>
<?php
// Fetch record rows in $DetailRS1 by while loop and put them into $row.
while($row=mysql_fetch_assoc($DetailRS1)){
?>
<tr>
<td bgcolor="#FFFFCC"><? echo $row['PlayerID']; ?>
<input name="hideplayerID" type="hidden" id="hideplayerID" value="<?php echo $row_DetailRS1['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[]">
<option value="Active" selected="selected">Active</option>
<option value="Bench">Bench</option>
</select>
</label></td>
</tr>
</form>
<?php } // End while loop. ?>
</table>
<input type="submit" name="submit" value="Update" />
</body>
</html>
Page 2 (Update):
<!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>update</title>
</head>
<body>
<?php require_once('../Connections/conn.php'); ?><?php
// If receive Submit button variable.
if($_POST['submit']){
require_once('../Connections/conn.php');
// 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;
}
}
# Script 2.5 - update.php
//Validate the status and combat Magic Quotes, if neccessary.
if (!empty($_REQUEST['select'])) {
$select = stripslashes($_REQUEST['select']);
} else {
$select = NULL;
echo '<p>You forgot to change your player status!</p>';
}
//If everything else is okay, print the message
echo "<p>Thank you</p>";
} else {//Selection was not made on form.
echo "<p>Please go back and complete your selections.</p>";
}
mysql_select_db($database_conn, $conn);
$query_result = "SELECT PlayerID FROM tblplayers ORDER BY PlayerID ASC";
$result = mysql_query($query_result, $conn) or die(mysql_error());
$row_result = mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result);
// Fetch record rows in $result by while loop and put them into $row.
while($row = mysql_fetch_assoc($result)){
//imploe the array variables
$select = array("Active", "Bench");
//start a counter in order to number the input fields for each record
$selectChoice = implode(" ", $select);
for($i = 0; $i < $count; $i++){
if ($select[$i] != ""){
echo "selectChoice #$i = $select[$i] <br />";
// Update field "StatusChangeChoice", matching with "PlayerID" value by while loop.
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$selectChoice' where PlayerID = (''$player[$i]')";
}
}
$update = mysql_query($query_update, $conn) or die(mysql_error());
if (!$update) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query_update;
die($message);
}
}
echo "--- Update Complete ---";
echo $query_update;
?>
<p><?php
echo "<pre>";
var_dump($select);
echo "</pre>";
print_r($row[PlayerID]);
var_dump($_POST);
echo $update;
?> </p>
</body>
</html>
<?php
mysql_free_result($result);
mysql_free_result($update);
?>
Re: update query does nothing
First recommendation... use "
seems that you have an extra ' before $player ... check that and try, otherwise just use
to check what is going on with your sentence.
Miko
Code: Select all
" to post your code... it will make it more readable and easy people that want to help you :wink:
now...
In this line:
[syntax=php]// Update field "StatusChangeChoice", matching with "PlayerID" value by while loop.
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$selectChoice' where PlayerID = (''$player[$i]')";Code: Select all
echo "This is the query : ". $query_update;Miko
-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
Re: update query does nothing
Thanks for the help. I made the changes with no change in results. Would it be better for me to use only one page for all the code unstead of breaking it up into two pages?
Re: update query does nothing
Did you echo your query?... what did you get?.... if your sentence is valid... did you validate it against your data?
Code: Select all
echo "This is the query : ". $query_update;-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
Re: update query does nothing
Below is where I placed the echo you requested:
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$selectChoice' where PlayerID = ('$player[$i]')";
}
}
$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 ---";
echo "This is the query : ". $query_update;
?>
Here is what shows up on the update page after the submit button is submitted:
Please go back and complete your selections.
Query was empty
$query_update = "UPDATE tblplayers SET StatusChangeChoice = '$selectChoice' where PlayerID = ('$player[$i]')";
}
}
$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 ---";
echo "This is the query : ". $query_update;
?>
Here is what shows up on the update page after the submit button is submitted:
Please go back and complete your selections.
Query was empty
Re: update query does nothing
I don't have too much time to read your whole code (specially without formating/indenting try to read it is just painful), but caugh my attention this:
- In your first file you have this line:
- And in your second file this line:
why your connections are different?... it triggers my next questions...
- are you sure that you are connecting to the DB in your second file? . try to change this line
for this one
and see if you are getting any error....
- also are you sure that the while loop in the second file is being executed?
- In your first file you have this line:
Code: Select all
<?php require('/conn.php'); ?>Code: Select all
require_once('../Connections/conn.php');- are you sure that you are connecting to the DB in your second file? . try to change this line
Code: Select all
mysql_select_db($database_conn, $conn);Code: Select all
mysql_select_db($database_conn, $conn) or die(mysql_error());- also are you sure that the while loop in the second file is being executed?
-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
Re: update query does nothing
The reason for the differences are that this page has been created from bits of posts from other sites and forums, so I probably missed it when I reviewed for consistency. Your suggested changes have been made and when the update opens up I get this message:
Please go back and complete your selections.
Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/update.php on line 59
That warning makes me think that the selections from the form are not posting. As for the loop, I don't know how to verify that it is working.
Please go back and complete your selections.
Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/update.php on line 59
That warning makes me think that the selections from the form are not posting. As for the loop, I don't know how to verify that it is working.
Re: update query does nothing
ok... there is your biggest problem then... inconsistence in your connections...
this message
is telling you exactly that ... wrong/undefined connection... therefore all the rest of the code is invalid.
start checking your connection code in both files.
check then that your form is submitting the data that you are expecting...
good luck
this message
Code: Select all
Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/update.php on line 59start checking your connection code in both files.
check then that your form is submitting the data that you are expecting...
good luck
-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
Re: update query does nothing
Thanks, I'll keep working on it. I will say that until I added the " or die(mysql_error());" I was not getting that error.
thanks again for all your help
thanks again for all your help
Re: update query does nothing
correct... because you were not controlling the error... the addition of "die(mysql_error())" allow you to know now that the sentence mysql_select_db was failing (more likely because you have problems with your DB connections).mrgrinch12 wrote:I will say that until I added the " or die(mysql_error());" I was not getting that error.
-
mrgrinch12
- Forum Newbie
- Posts: 16
- Joined: Mon Jun 07, 2010 10:20 am
Re: update query does nothing (partial success)
I went back to my old connection script and the error went away. This is the message on the update page after the form was submitted:
Thank you
Query was empty
I am assuming it is not getting the data from the form because something is incorrect with the variable coding.
Thank you
Query was empty
I am assuming it is not getting the data from the form because something is incorrect with the variable coding.