Updating fields in database

[perrij3]

This is me right now 

 

My problem is trying to get my program to update my database after someone edits a form. I have a form on the page, it populates with the data for each comment, so the information changes depending on what comment the user selects to edit. Populating the form works get, I have no problem there.

 

It's when I hit the submit button that I have an issue. The data isn't updated in the database. I have use <?php echo '<pre>'.print_r($_POST, true).'</pre>'; ?> to see if the data has been collect and posted and it returns with the updated values, but in my database there is no change. I know the connect to the database has to be working because I use it to populate my fields.

 

Here is the code:

//Remove backslashes
nukeMagicQuotes();	

//initialize flag
$done = false;

//prepare an array of expected items
$expected = array('name', 'Email, 'Address', 'City', 'State', 'Zip', 'Comment', 'user_ID', 'comment_ID');

//if form has been submitted, add record to comments
if (array_key_exists('edit', $_POST)) {
//prepare expected items for insertion into database
foreach ($_POST as $key => $value) {
	if (in_array($key, $expected)) {
		${$key} = mysql_real_escape_string($value);
		}
	}
//abandon the process if primary key invalid
if (!is_numeric($user_ID)) {
	die('Invalid Request');
	}

//prepare the SQL query
  $sql = "UPDATE comments SET Name = '$name', Email = '$Email', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Comment = '$comment'
        WHERE FK_user_ID = '$user_ID'
	    AND comment_ID = '$comment_ID'";

//process the query
  $done = mysql_query($sql) or die(mysql_error());

//redirect page of $user_ID is invalid
if ($done || !isset($user_ID)) {
$_SESSION['post'] = $_POST;
   header('Location: view_list.php');	exit;
}

 

Thanks for any help you can offer.

[abazoskib]

the only thing i can think of is your user_ID and commentID might be numbers, if so remove the single quotes from both:

 

   //prepare the SQL query
     $sql = "UPDATE comments SET Name = '$name', Email = '$Email', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Comment = '$comment'
           WHERE FK_user_ID = $user_ID
          AND comment_ID = $comment_ID";

 

other than that, try

 

echo mysql_error();

 

after the query is executed and see what error messages you get

[perrij3]

$user_ID and $comment_ID are numbers. I did change to my code prior to the sql statement to make sure they were both numbers. Before I was just checking to see is $user_ID was a number.

 

	//abandon the process if primary key invalid
if (!is_numeric($user_ID) && !is_numeric($comment_ID)) {
	die('Invalid Request');
	}

//prepare the SQL query

 

When I tried to remove the quotes around the $user_ID and $comment_ID, I received the following error message.

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 3.

[jarvis]

You have lowercase n on name but in the query it's uppercase?

 

What happens if you echo the query out to see what happens?

 

Also, if you have access to PHPMyAdmin, run the query direct but change the id numbers to those that you're actually trying to update. It may help show you were the error is.

[perrij3]

Sorry for the delay in responding to this. When I echo the query, it shows me the updated information. When I run the sql statement in PHPMyAdmin, it updates the record. I truly am lost at why the code below doesn't update my datebase. 

[MatthewJ]

Try

 

<?php
//Remove backslashes
nukeMagicQuotes();   

//initialize flag
$done = false;

//prepare an array of expected items
$expected = array('name', 'Email', 'Address', 'City', 'State', 'Zip', 'Comment', 'user_ID', 'comment_ID');

//if form has been submitted, add record to comments
if (array_key_exists('edit', $_POST)) {
   //prepare expected items for insertion into database
   foreach ($_POST as $key => $value) {
      if (in_array($key, $expected)) {
         ${$key} = mysql_real_escape_string($value);
         }
      }
   //abandon the process if primary key invalid
   if (!is_numeric($user_ID)) {
      die('Invalid Request');
      }
      
   //prepare the SQL query
     $sql = "UPDATE comments SET Name = '$name', Email = '$Email', Address = '$Address', City = '$City', State = '$State', Zip = '$Zip', Comment = '$comment'
           WHERE FK_user_ID = $user_ID
          AND comment_ID = $comment_ID";
         
//process the query
  $done = mysql_query($sql) or die(mysql_error());

//redirect page of $user_ID is invalid
if ($done || !isset($user_ID)) {
   $_SESSION['post'] = $_POST;
   header('Location: view_list.php');   exit;
   }
?>

[perrij3]

When I tried to remove the quotes around the $user_ID and $comment_ID, I received the following error message.

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 3.

[perrij3]

Ok, here is what I have discovered. It seems that the $user_ID and the $comment_ID when I hit the submit button are not being passed as numbers.  I have tried creating hidden fields collecting the numbers from the table they are being held in, but that isn't working either.

 

When I hit submit, the code runs those this line

 

   //abandon the process if primary key invalid
   if (!is_numeric($user_ID)) {
      die('Invalid Request');
      }

 

 

I use a get statement to receive these numbers from the page before.

 

//Remove backslashes
nukeMagicQuotes();
//initialize flag
$done = false;

//prepare an array of expected items
$expected = array('name', 'Email', 'Address', 'Address2', 'City', 'State', Zip', 'user_ID',  'comment_ID', 'Approved');
//create database connection
$conn = dbConnect('');

//get details of selected record
if ($_GET && !$_POST) {
if (isset($_GET['user_ID']) && is_numeric($_GET['user_ID'])) {
	$user_ID = $_GET['user_ID'];
	$comment_ID = $_GET['user_ID'];		
}
else {
	$user_ID = NULL;
}
if ($user_ID) {
	//Retreive information about whom the guestbook is for
	$sql = "SELECT * FROM obituaries
			WHERE user_ID = $user_ID";
	$result = mysql_query($sql) or die (mysql_error());
	$row = mysql_fetch_assoc($result);
	//Retrieve all content for single comment entry based on user ID and comment ID
	$gb_sql = "SELECT * FROM comment
					  WHERE user_ID = $user_ID
					  AND comment_ID = $comment_ID";
	$gb_result = mysql_query($gb_sql) or die (mysql_error());
	$gbrow = mysql_fetch_assoc($gb_result);
	}
}

 

I have echo these numbers and they do show up, so I know they are being passed. Does anyone know why the variables I am trying to use will come in as a number, but when I hit the post button, they don't seem to stay as a number?

[MatthewJ]

what type of fields are they stored in in the database?

[perrij3]

They are both stored as MEDIUMINT.