Update record in MYSQL DB

Forum Moderators: coopster

Message Too Old, No Replies

Update record in MYSQL DB

Update field in database from a form

TheLazyAce

6:28 pm on Jan 16, 2011 (gmt 0)

I am not able to resolve an issue. Warning... I am very new at this.

Here is what I have: I have a script that calls up a user profile from my DB. When a user wants to update the a field in the DB there is a link that takes them to a page with a form at is filled in with all the field data from their record. All of this works correctly. The form is set to post and Action is update.php (another script).

The problem is when the form submits and goes to the update.php script all data is lost in the form.

Do I have to set all the posted fields to Session? I have been following some tutorials but they are all vague. Please a clear example would be grateful Like using just one field.

Matthew1980

7:57 pm on Jan 16, 2011 (gmt 0)

Hi there TheLazyAce,

Welcome to WebmasterWorld!

From what your describing, this shouldn't happen! If you have set your form to POST and pointed the action attribute to the correct file - correct directory, then all the $_POST data should be kept, the only reason you would assign these values to a $_SESSION is if you wanted to span pages to collect more data.

Firstly I would suggest as you run some diagnostics on the receiver script so that you can see the $_POST data is being received on the correct page:-

<?php
error_reporting(E_ALL);

echo "<pre>";
print_r($_POST);
echo "</pre>";
echo "<p> testing the receiver script! </p>";
?>

If that page just displays the data contained within the p tags, then there is something wrong, but if you get the data from the passed $_POST array with the data contained in the p tag, then all is well :)

Other than that, we would need to see the relevant parts to your code before we could offer other solutions.

Cheers,
MRb

TheLazyAce

12:40 am on Jan 17, 2011 (gmt 0)

I ran you code I have the following coming back:
Array
(
)

testing the receiver script!

Here is a part of the code on the 1st script:

<body>

<div id="wb_Form1" style="position:absolute;background-color:#F0F0F0;left:333px;top:141px;width:504px;height:521px;z-index:49">
<form name="Profile_Update" method="post" action="update.php" enctype="text/plain" id="Form1">
<input type="hidden" name="Id" value="<? echo $ID; ?>">
<input type="text" id="Editbox1" style="position:absolute;left:19px;top:19px;width:155px;height:18px;border:1px #C0C0C0 solid;font-family:Courier New;font-size:13px;z-index:0" name="usermame" value="<? echo $UserName; ?>">
<div id="wb_Text2" style="margin:0;padding:0;position:absolute;left:24px;top:6px;width:108px;height:14px;text-align:left;z-index:1;">
<font style="font-size:11px" color="#000000" face="Arial">Log In Name</font></div>
<input type="text" id="Editbox2" style="position:absolute;left:19px;top:61px;width:134px;height:18px;border:1px #C0C0C0 solid;font-family:Courier New;font-size:13px;z-index:2" name="FirstName" value="<? echo $First; ?>">

This is the update.php script:

session_start();
/**
 *This will update the user profile. This is designed to allow
 * the user to update their only profile.
 *
 *update version will update other parts of the table. It will
 * be done with an ACTION variable.
 *
 * @version $Id$
 * @copyright 2011
 */
$UserName = $_SESSION['username'];
$database = "d-------s";
 
 include 'includes/DBCont----.php';
 mysql_connect($dbserver,$dbusername,$dbpassword);

 
$query="UPDATE contacts SET FirstName='$First'WHERE username ='$UserName'";
 mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";

The above code I left out the <?. This is a password protect page that is why there is a session statement. For some reason the post is empty.

If need to see more of the code I will be happy to send the whole thing.

Thanks for the welcome and the extra pair of eyes.

Ed

TheLazyAce

3:55 am on Jan 17, 2011 (gmt 0)

I made a change in the 1st script code and it now works. The following is the line:

<form name="Profile_Update" method="post" action="update.php" enctype="text/plain" id="Form1">

I changed it to this:

<form action="update.php">

Can anyone tell why this made a difference? I was just looking for an answer with out parsing one item at a time.

Ed

Matthew1980

9:36 am on Jan 17, 2011 (gmt 0)

<?php ?>

Not

<? ?>

and careful with using: value="<? echo $UserName; ?>"> because if the values are not set/don't have state, then your script wouldn't function.

It's best to use: value="<?php echo (isset($UserName) ? $UserName : ''); ?>">

ALso, it looks like your using register globals too, which isn't the best Idea, firstly if the $_POST array is set, assign those values when passed to the script to variables - when using register globals, this works:-

<input type="text" name="username">

echo $username;

BUT what you should do, regardless of register globals is this:-

<input type="text" name="username">

echo strip_tags($_POST['username']);

or $username = strip_tags($_POST['username']);

echo $username;

Hopefully you see what I am referring to there.

Cheers,
MRb

rocknbil

5:24 pm on Jan 17, 2011 (gmt 0)

Can anyone tell why this made a difference?

I changed it to this:
<form action="update.php">

Without a specified method, form submissions will default to GET. Get has two side effects, there is a limitation on the amount of data that can be submitted (rarely a big deal except in uploads - another story) and the URL in the address bar changes to an ugly?query&string=where&post=will&not=.

As to why, not sure - something is parsing your input into variables of the same name, bad mojo in any case (i.e., never use unsanitized variables directly in your program.) Looking at your script, I can see the original problems:

$UserName = $_SESSION['username'];

$query="UPDATE contacts SET FirstName='$First'WHERE username ='$UserName'";
mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";

First: Where is $_SESSION['username'] set, and is it set correctly?

Second: Where is $First set? I don't see it set anywhere at all. When you submit by POST, data is in $_POST or $_REQUEST, not $_GET. Prior to your query you chould have

$First = $_POST['First'];
(Note: this is if you leave method="post" in)

Third: You have no error trapping on your query and this is ALWAYS helpful.

mysql_query($query) or die("Cannot update record: " . mysql_error());

... will return any errors output by mysql - but prior to deployment you should remove the mysql_error() for security purposes (it can potentially reveal your database structure to hackers:)

mysql_query($query) or die("Cannot update record");

Last, you're missing a space before your WHERE clause which is definitely a syntax error.

So all together, leaving post in the form, you could do something like

$query="UPDATE contacts SET FirstName=' . mysql_real_escape_string(strip_tags[$_POST['First'])) . "' WHERE username ='$UserName'";
mysql_query($query) or die("Cannot update record: " . mysql_error());