[RESOLVED] UPDATE syntax problem

gamber

Have a little problem I need a second set of eyes to fix please - more I stare at it, more annoyed I become.

ERROR:
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 '' at line 9.

SYNTAX
$query = "UPDATE user
SET
Username='$Username',
Password='$Password',
FirstName='$FirstName',
LastName='$LastName',
Email='$Email',
AccessLevel= $AccessLevel
WHERE UserID={$_GET['UserID']}";

Many thanks

BTW -
Variables are defined prior and when I enter the same information through phpMyAdmin, the update works - thus the confusion

etully

The main way to troubleshoot this is to say print "$query" and see what's actually being set.

I'm pretty sure that it's not resolving the {$_GET['UserID']} inside the variable but I haven't tested it. I would write it like this:

$UserID = $_GET['UserID'];
$query = "UPDATE user
SET
Username='$Username',
Password='$Password',
FirstName='$FirstName',
LastName='$LastName',
Email='$Email',
AccessLevel= $AccessLevel
WHERE UserID=$UserID";

Give that a try, anyway.

napper

Always use addslashes() around text being inputted into a field. For example, O'reilly would cause your code to fail as a last name.

Try:

$query = "UPDATE user SET";
$query .= "Username='" . addslashes($Username) . "', ";
$query .= "Password='" . addslashes($Password) . "', ";
$query .= "FirstName='" . addslashes($FirstName) . "', ";
$query .= "LastName='" . addslashes($LastName) . "', ";
$query .= "Email='" . addslashes($Email) . "',"
$query .= "AccessLevel= '" . addslashes($AccessLevel) . "'";
$query .= "WHERE UserID='" . $_GET['UserID'] . "'";

gamber

what about

$editUser = $_SERVER['PHP_SELF'];					//trigger
if (isset($_POST['submit'])) {						//handle the form
	//define the variables
		$Username = safe($_POST['Username']);
		$Password = safe($_POST['Password']);
		$FirstName = safe($_POST['FirstName']);
		$LastName = safe($_POST['LastName']);
		$Email = safe($_POST['Email']);
		$AccessLevel = safe($_POST['AccessLevel']);
	//connect to the database
	mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the query
	$query = "UPDATE user
		SET
			Username='$Username', 
			Password='$Password',
			FirstName='$FirstName',
			LastName='$LastName',
			Email='$Email',
			AccessLevel= $AccessLevel
		WHERE UserID={$_GET['UserID']}";

Where the function is:

function safe($variable) {
  $variable = addslashes(trim($variable));
  return $variable;
}

gamber

etully,
query was:

UPDATE user SET Username='Skoki', Password='woof', FirstName='Skoki', LastName='The Dog', Email='woof@woofmail.com', AccessLevel= 4 WHERE UserID=

Apparently, it is not picking up the UserID through the

{$_GET['UserID']}

Funny, it is not pulling the UserID though {$_GET['UserID']}. It has already been active to pull the information into the form for editing. :bemused:

gamber

Again, the EXACT same code has already been used to retrieve the information for the form

UserID={$_GET['UserID']}

Here are the results:

The UserID is:

The query was:
UPDATE user SET Username='Skoki', Password='woof', FirstName='Skoki', LastName='The Dog', Email='woof@woofmail.com', AccessLevel= 4 WHERE UserID=

Could not update the entry because:
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 '' at line 9.

Here is my entire code, less the includes:

//get the information from the form
$editUser = $_SERVER['PHP_SELF'];					//trigger
if (isset($_POST['submit'])) {						//handle the form
	//define the variables & filter through the safe function
		$Username = safe($_POST['Username']);
		$Password = safe($_POST['Password']);
		$FirstName = safe($_POST['FirstName']);
		$LastName = safe($_POST['LastName']);
		$Email = safe($_POST['Email']);
		$AccessLevel = safe($_POST['AccessLevel']);
	//connect to the database
	mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the query
	$query = "UPDATE user
		SET
			Username='$Username', 
			Password='$Password',
			FirstName='$FirstName',
			LastName='$LastName',
			Email='$Email',
			AccessLevel= $AccessLevel
		WHERE UserID={$_GET['UserID']}";
	//execute the query
	if (@mysql_query ($query)) {
		echo "Query was successful, it was $query";	//DEBUG
		//header ("Location: ./index.php");			//redirect to UserType index page
		mysql_close();								//close the database
		exit();										//exit the script
	} else {
			$UserID=($_GET['UserID']);
			echo "<p>The  UserID is: $UserID</p>";
			echo "<p>The query was:<br /><b>$query</b><br /></p>";
			echo "<p>Could not update the entry because: <b><br />" .mysql_error(). "</b>.</p>";
			exit();
		}
}

ahundiak

Well, according to the SQL you pasted in: WHERE UserID=

So the id is not getting retrieved.

Noticed that you seem to be using both GET and POST at the same time. Is that intentional? Does the action attribute in your form element contain UserID=666?

Usually one uses either GET or POST. I suspect you need to add UserID as a hidden variable to your form.

gamber

Already done within the form as:

<input type="hidden" name="UserID" value="'.$_GET['UserID'].'"/>
<input name="submit" type="submit" value="Submit Changes to User"/>

666 is NOT the id BTW

gamber

Here is the entire form - hope it helps, and I apologize for the length of the posting

<?php $pageAccessLevel = 2 									//set page access?>
<?php include ('../../includes/logincheck.php');  			//check to see if the user has logged in & has privledges?>
<?php include ('../../connections/connMI.php'); 			//establish connection to server?>
<?php
//get the information from the form
$editUser = $_SERVER['PHP_SELF'];					//trigger
if (isset($_POST['submit'])) {						//handle the form
	//define the variables
		$UserID = $_GET['UserID'];
		$Username = $_POST['Username'];
		$Password = $_POST['Password'];
		$FirstName = $_POST['FirstName'];
		$LastName = $_POST['LastName'];
		$Email = $_POST['Email'];
		$AccessLevel = $_POST['AccessLevel'];
	//connect to the database
	mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the query
	$query = "UPDATE user
		SET
			Username='$Username', 
			Password='$Password',
			FirstName='$FirstName',
			LastName='$LastName',
			Email='$Email',
			AccessLevel= $AccessLevel
		WHERE UserID=$UserID";
	//execute the query
	if (@mysql_query ($query)) {
		echo "Query was successful, it was $query";	//DEBUG
		//header ("Location: ./index.php");			//redirect to UserType index page
		mysql_close();								//close the database
		exit();										//exit the script
	} else {
			$UserID=$_GET['UserID'];
			echo "<p>The  UserID is: $UserID</p>";
			echo "<p>The query was:<br /><b>$query</b><br /></p>";
			echo "<p>Could not update the entry because: <b><br />" .mysql_error(). "</b>.</p>";
			exit();
		}
}
?>
<?php														//select and display the information to be edited
	if(is_numeric ($_GET['UserID']) ) {						//check to see if there is a valid UserID in the URL
		mysql_select_db ($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');		//select the database
		$query_rsUser = "SELECT * FROM user WHERE UserID={$_GET['UserID']}";
		$rsUser = mysql_query($query_rsUser, $connMI) or die(mysql_error());
		$row_rsUser = mysql_fetch_assoc($rsUser);
		$totalRows_rsUser = mysql_num_rows($rsUser);
	} else {												//if no TypeID is set
			echo "There is no valid user in the database";
			//header ("Location: ./index.php");				//redirect to UserType index page
			mysql_close();									//close the database
			exit();											//exit the script
			}
?>
<?php
//connect to the database and define the recordsets for the dynamic drop down box and its limits
	//usertype recordset
		//allow only the system administrator to add users with same access level, all other AccessLevels add user below
	if ($_SESSION['loginAccessLevel'] == 1) {
	$query_rsUserType = "SELECT UserType, AccessLevel FROM usertype WHERE AccessLevel >= ('{$_SESSION['loginAccessLevel']}')";
		} else {
		$query_rsUserType = "SELECT UserType, AccessLevel FROM usertype WHERE AccessLevel >= ('{$_SESSION['loginAccessLevel']}'+1)";
		}

$rsUserType = mysql_query($query_rsUserType, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
$row_rsUserType = mysql_fetch_assoc($rsUserType);
$totalRows_rsUserType = mysql_num_rows($rsUserType);
?>
[CODE]<!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"><!-- InstanceBegin template="/Templates/admin.dwt.php" codeOutsideHTMLIsLocked="false" -->
<head>
	<!-- InstanceBeginEditable name="doctitle" -->
	<title>marketinginsider.ca - admin - user - edit</title>
	<!-- InstanceEndEditable -->

<!-- heading SSI -->
	<?php virtual('/includes/header.php'); ?>
</head>

<body>

<!--heading and logo -->
<div id="top" class="clearfix">
 	<div id="header" class="clearfix">
		<h1><a href="http://marketinginsider.ca/" title="marketinginsider.ca - strategies for RESULTS">marketinginsider.ca</a></h1>

<div id="content" class="clearfix">
	<h2>Administration: <!-- InstanceBeginEditable name="admin title" -->Add new User <!-- InstanceEndEditable --></h2>

<!-- InstanceBeginEditable name="content" -->
<p>Complete the form below to add a new user </p>
<p>&nbsp;</p>
<div align="center">
	<form name="edit User" method="POST" action="<?php echo $editUser; ?>">
	<table>
	<tr>
	  <td nowrap align="right">Username:</td>
	  <td><input type="text" name="Username" value="<?php echo $row_rsUser['Username']; ?>" size="32"></td>
	</tr>
	<tr>
	  <td nowrap align="right">Password:</td>
	  <td><input type="password" name="Password" value="<?php echo $row_rsUser['Password']; ?>" size="32"></td>
	</tr>
	<tr>
	  <td nowrap align="right">First Name:</td>
	  <td><input type="text" name="FirstName" value="<?php echo $row_rsUser['FirstName']; ?>" size="32"></td>
	</tr>
	<tr>
	  <td nowrap align="right">Last Name:</td>
	  <td><input type="text" name="LastName" value="<?php echo $row_rsUser['LastName']; ?>" size="32"></td>
	</tr>
	<tr>
	  <td nowrap align="right">Email:</td>
	  <td><input type="text" name="Email" value="<?php echo $row_rsUser['Email']; ?>" size="32"></td>
	</tr>
	<tr>
	<!-- code to bring in the access levels from the UserType database -->
	<td nowrap align="right">Access Level:</td>
	  <td><select name="AccessLevel">
		<?php
		do {  
		?><option value="<?php echo $row_rsUserType['AccessLevel']?>"<?php if (!(strcmp($row_rsUserType['AccessLevel'], $row_rsUserType['AccessLevel']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsUserType['UserType']?></option>
						<?php
		} while ($row_rsUserType = mysql_fetch_assoc($rsUserType));
		  $rows = mysql_num_rows($rsUserType);
		  if($rows > 0) {
			  mysql_data_seek($rsUserType, 0);
			  $row_rsUserType = mysql_fetch_assoc($rsUserType);
		  }
		?>
		</select></td>
	</tr>
	<tr>
	  <td>&nbsp;</td>
	  <td align="right">
		<input type="hidden" name="UserID" value="'.$_GET['UserID'].'"/>
		<input name="submit" type="submit" value="Submit Changes to User"/>
	  </td>
	</tr>
	</table>
	</form>
	</div>
    <p>&nbsp;</p>
<!-- InstanceEndEditable --></div>
<!--end of content div-->

<!--SSI Navigation-->
	<?php virtual('/includes/navigation_admin.php'); ?>
<!--end of navigation-->
<!--end of sidebar div -->
    </div>
 	<!--end of header div-->
</div> <!--end of top div-->

</body><!-- InstanceEnd --></html>

<?php
mysql_free_result($rsUser);
mysql_free_result($rsUserType);
?>[/code]

Rodney_H_

Here are my two cents:

I suggest you use the mysql_real_escape_string() function instead of the addslases() function.

You should test to make sure the user ID is set in the query string, and not assume that it is there. To do so, use the isset() function.

So:

<?php
if(isset($_GET['UserID']))
{
     $UserID = $_GET['UserID']; 
}
?>

Further, you should never assume that the value passed in the query string is SAFE, and it should be processed, or cleansed.

Therefore, you may want to see if the set value is numeric. So:

<?php
if(isset($_GET['UserID'])  &&  is_numeric($_GET['UserID'])) 
{
     $UserID = trim($_GET['UserID']); 
}  

else {
     echo 'no user has been selected.'
     exit;
}
?>

ahundiak

Plus when you post the forn the id will be in $_POST['UserId]. When it's passed as part of the url then you use GET.

gamber

POST obtains the information from the form, while
GET obtains the information from the URL

That is understood.

Completely understood - has been since the inital post.

What I am looking for is to update information from the form (with the POST) provided that it equals the variable UserID in the GET

My lack of understanding is related to:
1. The information exists, it is used to move from the index page to the edit page that has the user information included and ready for edit
2. How does this information disappear when I hit the submit button?

Not so difficult I thought as I have done this before.

ahundiak

Now you are getting closer. No pun intended.

Each time your script executes it will start over fresh. Data from one page will not carry over to another unless you start using sessions. So when you submit $POST will be filled in. And when it's just the url then $GET will be filled.

It is possible to mix the two but that is generally not done as it can get confusing.

Just change
if (isset($POST['submit'])) { //handle the form
//define the variables
$UserID = $GET['UserID'];
to
if (isset($POST['submit'])) { //handle the form
//define the variables
$UserID = $POST['UserID'];

gamber

ahundiak,
things are getting a little clearer - once I define $UserID earlier in the script thought I had it licked - but no

After cleaning up the code - all the changing and debugging - here is what I am down to:

//get the information from the form
$editUser = $_SERVER['PHP_SELF'];					//trigger
if (isset($_POST['submit'])) {						//handle the form
	//define the variables
	$Username = trim($_POST['Username']);
	$Password = trim($_POST['Password']);
	$FirstName = trim($_POST['FirstName']);
	$LastName = trim($_POST['LastName']);
	$Email = trim($_POST['Email']);
	$AccessLevel = $_POST['AccessLevel'];
	//connect to the database
	mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the query
	$query = "UPDATE user
		SET
			Username='$Username', 
			Password='$Password',
			FirstName='$FirstName',
			LastName='$LastName',
			Email='$Email',
			AccessLevel=$AccessLevel
		WHERE UserID=$UserID";
	//execute the query
	if (@mysql_query ($query)) {
		echo "Query was successful, it was $query";	//DEBUG CODE
		//header ("Location: ./index.php");			//redirect to UserType index page
		mysql_close();								//close the database
		exit();										//exit the script
	} else {
			echo "<p>Could not update the entry because: <b><br />" .mysql_error(). "</b>.</p>";
			echo "<p>The query was <br /><b>$query</b></p>";
			exit();
		}
	echo "<p>This is the first query.  The  UserID is: $UserID</p>";
	echo "<p>The query was:<br /><b>$query</b><br /></p>";
	echo 'no valid user has been selected.';
	exit; 
}

resulting in errors of:

Could not update the entry because:
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 '\'.$_GET[\'UserID\'].\'' at line 9.

The query was
UPDATE user SET Username='Banner', Password='woof', FirstName='Banner', LastName='The Runt', Email='', AccessLevel=4 WHERE UserID=\'.$_GET[\'UserID\'].\'

It appears this relates to the submit part of my form which is

<input type="hidden" name="UserID" value="'.$_GET['UserID'].'"/>
<input name="submit" type="submit" value="Submit Changes to User"/>

As a result, the value= "'.$GET['UserID'].'" resulting in

'\'.$_GET[\'UserID\'].\''

FINALLY AT THE ROOT OF THE PROBLEM! The offender is the Value within the hidden input portion of the form.

Any quick ideas?

ahundiak

Just need to flip into php to echo the actual value.

Just like you do here:
<input type="text" name="LastName" value="<?php echo $row_rsUser['LastName']; ?>" size="32">

BTW, <?= is shorthand for <?php echo

gamber

Who da man?

You da man!

Now if we could have only sorted this out 8 hours ago.

Thanks to all, especially ahundiak.