Update bug

[sted999]

Hiya all.

Being a newbie to PHP im not totally sure if this is possible, thats why I thought i'd ask you lot. So far i havent been able to find an answer and ive got a tonne of books next to me!

 

What i want to do is to allow a user to update their account details via a form. The problem is when i update, it puts an empty field if I dont fill one of them in. Is thier an easy way round this? Or would I need a update button and code for every field.

 

 <?php 
		include ('connect.php'); 
		mysql_select_db("a6188092") or die(mysql_error());

		if ($_POST['edit'] == 'Update your account')
		{

		$loginName=($_POST['loginName']);
		$password=($_POST['password']);
		$secretQuestion=($_POST['secretQuestion']);
		$secretAnswer=($_POST['secretAnswer']);
		$email=($_POST['email']);
		$title=($_POST['title']);
		$firstName=($_POST['firstName']);
		$surname=($_POST['surname']);
		$gender=($_POST['gender']);

		$dob_year = $_POST['dob_year'];
		$dob_month = $_POST['dob_month'];
		$dob_day = $_POST['dob_day'];
		$date_of_birth = "$dob_year-$dob_month-$dob_day"; 


		$query = mysql_query(sprintf("UPDATE Member SET loginName='$loginName',title='$title' WHERE loginName='%s'", mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());

			echo("Details updated <br /><br /><br />");
		} 


			?>

 

For example i tried to update my title, but this then set the loginName field to blank?

 

Any help would be amazing.

Thanks.

[jackpf]

I believe you have two options:

 

1. have the original value for that field as the default.

eg

$sql = ... //select from your table
$fetch = mysql_fetch_array($sql);
$title = $fetch['title'];
echo 'Title: <input type="text" name="title" value="'.$title.'" />';

 

Or 2. If the field is null, update it to it's original value

eg

$title = $_POST['title'];
if(empty($title))
{
$sql = ...//select from your table...
$title = $fetch['title'];
}

[Mark Baker]

The most common technique, is to display alll the current data in the form, and allow the user to modify any of those fields. Then update every field in the database with the values from the form. If a user has chosen to blank a field hich previously had a value, then assume that they no longer want that field value in the database.

 

Alternatively, you can loop through each field in the form building the SQL UPDATE statement as you go, only setting values if the field contains data

[sted999]

I've gone for the approach of having the fields already stating what is in the database, but I have now come across another problem. When i click update it just reverts back to the original data in the database becuase its calling the variable which holds the data. What variable should i put in the mysql query instead?

 

             <?php

		include ('connect.php'); 
		mysql_select_db("a6188092") or die(mysql_error());

		$members = mysql_query(sprintf("SELECT * FROM Member WHERE loginName='%s'", mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());

		$fetch = mysql_fetch_array($members);

		$loginName = $fetch['loginName'];

		echo 'New user name: <input type="text" size="20" maxlength="15" name="userName" value="'.$loginName.'" />';
		echo "<br />";


		if ($_POST['edit'] == 'Update your account')
		{

		$query = mysql_query(sprintf("UPDATE Member SET loginName='$loginName' WHERE loginName='%s'", mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());

			echo("Details updated. <br /><br /><br />");
		} 
		?>

 

Thanks.

[sted999]

I have changed my code slightly so the update code is above the query, but this is only run if called. My database is updated but with blank fields only, does anybody know why?

Thanks.

 

 <?php

		include ('connect.php'); 
		mysql_select_db("a6188092") or die(mysql_error());

		if ($_POST['edit'] == 'Update your account')
		{

		$query = mysql_query(sprintf("UPDATE Member SET loginName='$loginName' WHERE loginName='%s'", mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());


		if ($query)
			{
				$messages = "Details updated!";
			}

			else
			{
				$messages = "Account details not updated.";
			}
		} 

		$members = mysql_query(sprintf("SELECT * FROM Member WHERE loginName='%s'", mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());

		$fetch = mysql_fetch_array($members);

		$loginName = $fetch['loginName'];

		echo 'User name : <input type="text" size="20" maxlength="15" name="userName" value="'.$loginName.'" />';

		?>

[jackpf]

What's with the sprintf() ? can't you just use variables..?

 

And also, what is $_POST['edit']?

[sted999]

What's with the sprintf() ? can't you just use variables..?

 

And also, what is $_POST['edit']?

 

I am reusing so code elsewhere with the sprintf(), i worked fine on the other page so I thought i'd use it again in this page.

 

The $_POST['edit'] is the button that is clicked to run the code.

[mrMarcus]

$query = mysql_query(sprintf("UPDATE Member SET loginName='%s' WHERE loginName='%s'", mysql_real_escape_string(trim($_POST['userName'])), mysql_real_escape_string(trim($_COOKIE['loginName'])))) or die ('SQL Error: ' . mysql_error());

i changed your query .. you need to call the var via $_POST['userName'] .. $userName wasn't helping ya.

[mrMarcus]

something like this can be very messy when you get lot's of items in the query :

mysql_real_escape_string(trim($_COOKIE['loginName']))

 

so, the next step is to create a function which does this to your vars and is only called when you need it, ultimately shortening up your query.

 

#clean up function;
function sanitize($input) {
    @trim($input) //trims any whitespace;
    if (get_magic_quotes_gpc()) {
        $input= stripslashes($input);
    }
    $output = mysql_real_escape_string($input);

    return $output;
}

 

then, your query will now look like this :

$query = mysql_query(sprintf("UPDATE Member SET loginName='%s' WHERE loginName='%s'", sanitize($_POST['userName']), sanitize($_COOKIE['loginName']))) or die ('SQL Error: ' . mysql_error());

 

just place that function near the top or preferably in an included file with possibly some other functions so it can be used globally.