PHP Forum

**djab** · 08:12 PM

This script is the process file that updates the user's account information. However when the user updates their information and leaves some things blank, it rewrites them as blank in the database. How do i make this so that if they leave the HTML form empty, it doesnt overwrite it in the database?
Here is the process file that gets the variables from the POST and places them in the database.

PHP Code:


			
<?php 
 // Connects to your Database 
 require("libraries/db/dbconnect.php");
 
 // Encrypt Page
 require("libraries/auth/membersarea.php");
 
 // Get Basic Information
 $email = $_POST['email'];
 $pass = $_POST['pass'];
 $pass2 = $_POST['pass2'];
 // Get Contact Information
 $phone1 = $_POST['phone1'];
 $phone2 = $_POST['phone2'];
 $phone3 = $_POST['phone3'];
 $zipCode = $_POST['zip'];
 $address = $_POST['address'];
 // Get Bank Information
 $bankType = $_POST['bankType'];
 $routingNum = $_POST['routingNum'];
 $accountNum = $_POST['accountNum'];
 // Encrypt Bank Information
 $encRouting = base64_encode($routingNum);
 $encAccount = base64_encode($accountNum);
 // Determine $accountType
 switch ($bankType) {
  case "saving":
     $accountType = 1;
     break;
  case "checking":
     $accountType = 0;
     break;
 }
 // match pass and pass2
 
 if ($pass != $pass2) {
 echo "Passwords do not match. Please try again.";
 include("template/footer.php");
 exit();
 } else {
 // Determine Phone number
   $phone = "1-" . $phone1 . "-" . $phone2 . "-" . $phone3;
 // Get city from zip
 $city = mysql_result(mysql_query("select city from zips where zip = ".$zipCode.""),0);
  // Get state from zip
 $stateID = mysql_result(mysql_query("select state from zips where zip = '".$zipCode."'"),0);
 $state = mysql_result(mysql_query("select shortName from zips_states where id = ".$stateID.""),0);
 
  $id=mysql_result(mysql_query("select ID from users where username = '$username'"),0);
  $pass_hash = md5($pass);
  $update_email = mysql_query("update users set email = '$email' where ID = '$id'") or die(mysql_error());
  $update_pass = mysql_query("update users set password = '$pass_hash' where ID = '$id'") or die(mysql_error());
  $update_routingNum = mysql_query("update users set routingNumber = '$encRouting' where ID = '$id' ") or die(mysql_error()); 
  $update_accountNum = mysql_query("update users set accountNumber = '$encAccount' where ID = '$id' ") or die(mysql_error()); 
  $update_bankType = mysql_query("update users set accountType = '$accountType' where ID = '$id' ") or die(mysql_error()); 
  $update_phone = mysql_query("update users set phone = '$phone' where ID = '$id'") or die(mysql_error());
  $update_address = mysql_query("update users set address = '$address' where ID = '$id'") or die(mysql_errno());
  $update_city = mysql_query("update users set city = '$city' where ID = '$id'") or die(mysql_error());
  $update_state = mysql_query("update users set state = '$state' where ID = '$id'") or die(mysql_error());
  echo "Your account has been updated."; 
 }
include("template/footer.php");
?>

Thanks in advance!

**mgraphic** · 02-21-2011, 09:33 PM

The main thing is to validate the data.

Other things you can do is to prepopulate the form data with the users saved data so if they erase it then it would clear it out, but this is not necessary.

Another recommended action to use is to create an array of validated data and then update the db only once instead of multiple times since it's updating the same table and row.

**djab** · 02-21-2011, 09:39 PM

What would the code look like if i were to validate the data? I'm assuming it would be validated on this process page?
Thanks!

**RonnieTheDodger** · 02-21-2011, 10:25 PM

I am not one to reinvent the wheel. There is a decent Php class for form generation and validation you could use. Once validated, you can take that data and update the database. This class also allows you to pre-populate the form with data that you fetch from the database.

You need to register at Php Classes before downloading. Registration is free.

http://www.phpclasses.org/package/1-...lidation-.html

**RonnieTheDodger** · 02-21-2011, 10:30 PM

I just noticed that you are capturing banking information and your form is posting that information over an unsecured connection, more than likely. Probably not a good thing to do if you are not that savvy with Php to begin with. The form needs to send the data over encrypted SSL connection, technically.

**mgraphic** · 02-21-2011, 10:43 PM

Here is a basic example, and could be improved. Also not sure if the join will work as expected too.

PHP Code:


			
<?php 
  
  // Connects to your Database
  require("libraries/db/dbconnect.php");
  
  // Encrypt Page
  require("libraries/auth/membersarea.php");
  
  $data = array();
  
  if (!empty($_POST['pass']))
  {
    if ($_POST['pass'] != $_POST['pass2'])
    {
      echo "Passwords do not match. Please try again.";
      include("template/footer.php");
      exit;
    }
    
    $data['password'] = md5($_POST['pass']);
  }
  
  if (!empty($_POST['email']) AND preg_match('/^[A-Z0-9_.-]+@[A-Z0-9-.]+\.[A-Z]{2,5}+$/i', $_POST['email']))
  {
    $data['email'] = $_POST['email'];
  }
  
  if (ctype_digit($_POST['phone1']) AND strlen($_POST['phone1']) == 3 AND ctype_digit($_POST['phone2']) AND strlen($_POST['phone2']) == 3 AND ctype_digit($_POST['phone3']) AND strlen($_POST['phone3']) == 4)
  {
    $data['phone'] = '1-' . $_POST['phone1'] . '-' . $_POST['phone2'] . '-' . $_POST['phone3'];
  }
  
  if ((int)$_POST['zip'] > 0)
  {
    $query = mysql_query("
      SELECT z.city, zs.shortName AS state
      FROM zips z LEFT JOIN zip_states zs ON z.state = zs.id
      WHERE z.zip = '" . sprintf('%05d', (int)$_POST['zip']) . "'
    ");
    
    if (mysql_num_rows($query) > 0 AND $result = mysql_fetch_assoc($query))
    {
      $data['city'] = $result['city'];
      
      $data['state'] = $result['state'];
    }
  }
  
  if (!empty($_POST['address']))
  {
    $data['address'] = trim($_POST['address']);
  }
  
  if (!empty($_POST['routingNum']))
  {
    $data['routingNumber'] = base64_encode(trim($_POST['routingNum']));
  }
  
  if (!empty($_POST['accountNum']))
  {
    $data['accountNumber'] = base64_encode(trim($_POST['accountNum']));
  }
  
  switch ($_POST['bankType'])
  {
    case 'saving':
      $data['accountType'] = 1;
    break;
    
    case 'checking':
      $data['accountType'] = 0;
    break;
  }
  
  
  // Update
  if (count($data) > 0)
  {
    $id = (int)mysql_result(mysql_query("select ID from users where username = '$username'"),0);
    
    $update = array();
    
    foreach ($data AS $key => $value) $update[] = "$key = '" . mysql_real_escape_string($value) . "'";
    
    mysql_query("UPDATE users SET " . implode(', ', $update) . " WHERE ID = $id") or die(mysql_error());
    
    echo "Your account has been updated."; 
  }
  else
  {
    echo "Your account has not been updated."; 
  }
  
  
  include("template/footer.php");

As Ronnie has stated, you should use SSL for this type of information because you could be held liable for security leaks. I would personally never store bank account numbers in my db also.

**djab** · 02-21-2011, 10:47 PM

Quote:

Originally Posted by RonnieTheDodger

I just noticed that you are capturing banking information and your form is posting that information over an unsecured connection, more than likely. Probably not a good thing to do if you are not that savvy with Php to begin with. The form needs to send the data over encrypted SSL connection, technically.

Were going to get a high quality SSL certificate and were using a special encryption (which i didn't post for security reasons, i just made it base64). ITs not going to be in the database as plain text.

Thanks!

**RonnieTheDodger** · 02-21-2011, 11:02 PM

Quote:

Originally Posted by mgraphic

Here is a basic example, and could be improved. Also not sure if the join will work as expected too.

PHP Code:


			
<?php 

  

  $data = array();

  

  if (!empty($_POST['pass']))

  {

    if ($_POST['pass'] != $_POST['pass2'])

    {

      echo "Passwords do not match. Please try again.";

      include("template/footer.php");

      exit;

    }

    

    $data['password'] = md5($_POST['pass']);

  }

As Ronnie has stated, you should use SSL for this type of information because you could be held liable for security leaks. I would personally never store bank account numbers in my db also.

Agreed on the storing of banking information. May suffice to store an encrypted version of it on the client side in a cookie, and call it when you need it. If the cookie gets deleted, oh well, they just have to re-enter the information again -- at least they know you are not storing somewhere else and it may make them feel better about it.

Also I noticed that you encrypted the password too, which I wanted to make note of too. This is something that a lot of people forget to do.

**djab** · 11:13 PM

If i fill out all the fields I get this error. It still puts the information in the database but i get this error. What do i need to fix? I used the script mgraphic gave me.

Code:

Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/raibilli/public_html/editprofileprocess.php on line 41
Your account has been updated.

Thanks everyone for the help so far!
Oh and one last thing. The zip code is showing as 0 in the database.

**mgraphic** · 02-21-2011, 11:21 PM

There is some type of SQL error in the city/state db lookup query. Look at the error by using echo mysql_error(); after the mysql call.

**djab** · 02-21-2011, 11:25 PM

Its saying

Code:

  Table 'raibilli_secure.zip_states' doesn't exist

This is true. The table is zips_states. (Note the zipS). I fixed this.
However it is still putting 0 in the database for the zip code.

**RonnieTheDodger** · 02-21-2011, 11:30 PM

That means this line failed to supply a valid result for $query:

Code:

$query = mysql_query("
      SELECT z.city, zs.shortName AS state
      FROM zips z LEFT JOIN zip_states zs ON z.state = zs.id
      WHERE z.zip = '" . sprintf('%05d', (int)$_POST['zip']) . "'
    ");

He said that the join may not be proper. I am not good with joins either. It seems to me that you can probably combine those two tables and get rid of that need to join them. I am assuming that "shortname" for the state is a two-letter designator? If so, just add another field to you zips table and enter those values into it.

**RonnieTheDodger** · 02-21-2011, 11:40 PM

Quote:

Originally Posted by djab

Its saying

Code:

  Table 'raibilli_secure.zip_states' doesn't exist

This is true. The table is zips_states. (Note the zipS). I fixed this.
However it is still putting 0 in the database for the zip code.

Missing the $data assign for zip. Insert that after you check the POST:

PHP Code:


			
 if ((int)$_POST['zip'] > 0)
  {
    $data['zip'] = $_POST['zip'];
    
    $query = mysql_query("
      SELECT z.city, zs.shortName AS state
      FROM zips z LEFT JOIN zip_states zs ON z.state = zs.id
      WHERE z.zip = '" . sprintf('%05d', (int)$_POST['zip']) . "'
    ");

**djab** · 02-21-2011, 11:45 PM

Thanks that worked!
Everythings good now!
Thanks Everyone!

**mgraphic** · 02-22-2011, 03:21 PM

Quote:

Originally Posted by RonnieTheDodger

Missing the $data assign for zip. Insert that after you check the POST:

PHP Code:


			
 if ((int)$_POST['zip'] > 0)
  {
    $data['zip'] = $_POST['zip'];
 
    $query = mysql_query("
      SELECT z.city, zs.shortName AS state
      FROM zips z LEFT JOIN zip_states zs ON z.state = zs.id
      WHERE z.zip = '" . sprintf('%05d', (int)$_POST['zip']) . "'
    ");

Ooops!

**RonnieTheDodger** · 02-22-2011, 03:40 PM

Quote:

Originally Posted by mgraphic

Ooops!

Hehehe.

I kind of rushed that through however. Probably should be:

PHP Code:


			
if ((int)$_POST['zip'] > 0)

  {

    $data['zip'] = (int)$_POST['zip'];

 

    $query = mysql_query("

      SELECT z.city, zs.shortName AS state

      FROM zips z LEFT JOIN zip_states zs ON z.state = zs.id

      WHERE z.zip = '" . sprintf('%05d', $data['zip']) . "'

    ");