Trouble with MySql updating with an empty result

[rmelino]

I have a member profile page that users can login to and update fields within their profile.  The issue i am having is that when a user is updating fields within their profile, if they leave one of the fields blank, it ends up deleting the previous data in the database for whatever field was left blank.  For example:

 

Lets say you had the following textboxes on the member profile edit page:

 

Name:  _______

Address:  ______

Phone:  ______

 

If the user wanted only to change their phone number but leave Name and Address the same, if they went in and put in a new phone number and left Name and Address blank and then hit submit, the script would update the phone number to the new number but since Name and Address were left blank it would erase whatever data was in the database under Name and Address.  I assume it's some sort of if statement that needs to be inserted to say if field is left blank, make no change to entry.

 

Can you help with this problem?  Thanks in advance!

 

Here is my code:

<?php
include ('functions.php');
include ('config.php');
?>
<?php

$username = $_SESSION['s_username'];

        if (!$_POST['update']) {
        }

        else {

$Name = $_POST['Name'];	
$address = $_POST['address'];	
$phone = $_POST['phone'];	


            $update = @mysql_query("UPDATE Users SET Name = '$Name', address = '$address', phone = '$phone' WHERE Username = '$username' LIMIT 1");

		else {
            if ($update)
                echo 'Your profile has been updated';
            else
                echo 'MySQL error';
		}
?>			


<form method="post">
<table width="50%">
<tr><td>Name: <?php echo ''.form_input(text,name,$Name, 25, 30).''; ?> </td></tr>
<tr><td>Address: <?php echo ''.form_input(text,address,$address, 25, 30).''; ?> </td></tr>
<tr><td>Phone: <?php echo ''.form_input(text,phone,$phone, 25, 30).''; ?> </td></tr>
<tr><td></td></tr>
<tr><td><?php echo ''.form_button(submit, update, Update).''; ?></td></tr>
</table>

</form>

 

 

[vineld]

Check if trim($anyfield) is blank ("") and then don't include that field in the sql.

[rmelino]

sorry to be a pain, but could you give an example of how i would write that?  lets say we're talking about the $name field...

[vineld]

You need to build the sql string prior to the actual insert.

 

if (trim($name) == "")

{

      do whatever you want

}

 

Your code as it is now is highly vulnerable to sql injections as well which means people can manipulate your database. Look up mysql_real_escape_string. Always make sure to treat user input with great care. Validation is your friend.

[rmelino]

vineld,

 

Thanks for the tips.

 

regarding injection, i read up on it and could you let me know if i have this right?  I assume i would follow my POST statements with the mysql_real_escape_string function like this:

 

<?php
include ('functions.php');
include ('config.php');
?>
<?php

$username = $_SESSION['s_username'];

        if (!$_POST['update']) {
        }

        else {

$Name = $_POST['Name'];   
$address = $_POST['address'];   
$phone = $_POST['phone'];   

$Name = mysql_real_escape_string($Name);
$address = mysql_real_escape_string($address);
$phone = mysql_real_escape_string($phone);

            $update = @mysql_query("UPDATE Users SET Name = '$Name', address = '$address', phone = '$phone' WHERE Username = '$username' LIMIT 1");

         else {
            if ($update)
                echo 'Your profile has been updated';
            else
                echo 'MySQL error';
         }
?>         


<form method="post">
<table width="50%">
<tr><td>Name: <?php echo ''.form_input(text,name,$Name, 25, 30).''; ?> </td></tr>
<tr><td>Address: <?php echo ''.form_input(text,address,$address, 25, 30).''; ?> </td></tr>
<tr><td>Phone: <?php echo ''.form_input(text,phone,$phone, 25, 30).''; ?> </td></tr>
<tr><td></td></tr>
<tr><td><?php echo ''.form_button(submit, update, Update).''; ?></td></tr>
</table>

</form>

 

Also, regarding the statement you responded with:

 

if (trim($name) == "")

{

      do whatever you want

}

 

I assume i make one of these for every POST value?  In other words:

 

if (trim($name) == "")
{
       do whatever you want
}
if (trim($address) == "")
{
       do whatever you want
}
if (trim($phone) == "")
{
       do whatever you want
}

 

Also, the 'do whatever you want' part, i want it to just ignore the entry so that it doesn't update an empty value in my table.  what command would i insert for 'do whatever you want' to accomplish this?

 

Thanks again for baring with me on this...

[rmelino]

Ok so i think i may have answered my previous question.  Here is the complete code that seems to be working:

 

<?php
include ('functions.php');
include ('config.php');
?>
<?php

$username = $_SESSION['s_username'];

        if (!$_POST['update']) {
        }

        else {

$Name = $_POST['Name'];	
$address = $_POST['address'];		
$phone = $_POST['phone'];	


// prevent injection	
$Name = mysql_real_escape_string($Name);
$address = mysql_real_escape_string($address);
$phone = mysql_real_escape_string($phone);


if (trim($Name) == ""){
        $sql = "SELECT * FROM `Users` LIMIT 0 , 30";
	}
	else {
	$update = @mysql_query("UPDATE Users SET Name = '$Name' WHERE Username = '$username' LIMIT 1");
            if ($update)
                echo 'Your Name has been updated<br />';
            else
                echo 'MySQL error';
		}
	}

if (trim($address) == ""){
        $sql = "SELECT * FROM `Users` LIMIT 0 , 30";
	}
	else {
	$update = @mysql_query("UPDATE Users SET address = '$address' WHERE Username = '$username' LIMIT 1");
            if ($update)
                echo 'Your Address has been updated<br />';
            else
                echo 'MySQL error';
		}

if (trim($phone) == ""){
        $sql = "SELECT * FROM `Users` LIMIT 0 , 30";
	}
	else {
	$update = @mysql_query("UPDATE Users SET phone = '$phone' WHERE Username = '$username' LIMIT 1");
            if ($update)
                echo 'Your Phone has been updated<br />';
            else
                echo 'MySQL error';
		}

?>			


<form method="post">
<table width="50%">
<tr><td>Name: <?php echo ''.form_input(text,Name,$Name, 25, 30).''; ?> </td></tr>
<tr><td>Address: <?php echo ''.form_input(text,address,$address, 25, 30).''; ?> </td></tr>
<tr><td>Phone: <?php echo ''.form_input(text,phone,$phone, 25, 30).''; ?> </td></tr>
<tr><td></td></tr>
<tr><td><?php echo ''.form_button(submit, update, Update).''; ?></td></tr>
</table>

</form>

 

I have more fields that Name, Address and Phone so i just assume the string of code is going to be long.  Perhaps there is a way to consolidate/optimize it?

 

Also, as you can see, when an entry is blank i have it running the following statement:  $sql = "SELECT * FROM `Users` LIMIT 0 , 30";

 

I did this only because i know it leaves the table unchanged.  Is there a better statement i should use to achieve the same result or is this the best option.  Is there some sort of 'IGNORE' statement?

 

[vineld]

I guess the user is logged in when submitting these changes? If so, then why don't you fill out the form with the present values to begin with? That would make it easier for both the user and yourself.

[rmelino]

yes the user is logged in during these changes.

 

i do like the idea of having the present values appear however i'm not sure how to accomplish that.  Could you provide an example that i could work with for say the name value?

[vineld]

I suppose you use CodeIgniter or some other php framework? I do not like using frameworks myself for various reasons. I assume that the field value is what is sent as $Name when creating the field here?

 

form_input(text,Name,$Name, 25, 30)

 

All you need to do then is to select the user data from the database where user = $username and assign the query result to the respective variables.

 

If going for your previous solution I would build the sql string and exclude empty fields from the update sql instead of making multiple database searches the way you do in your code above.

[rmelino]

how would i exclude empty fields from the update sql?

 

I assume that the field value is what is sent as $Name when creating the field here?:  Yes

 

All you need to do then is to select the user data from the database where user = $username and assign the query result to the respective variables:  Ok that sounds easy enough.  I think what I'll do is show the data currently in the table and the supply an edit link below it

 

thanks again...