Add Customer Records PHP Script

The scripts in the two link groups below are Customer Apps for Dealing with Product Keys and Email Addresses, and Administrator Apps for Dealing with Customer Records.

In our ecommerce world, products are sold by many different methods. Amongst these are getting out demos in various ways and when people try them, some of them are very pleased and they buy what's called a key. This unlocks the full feature set of the product when they enter it. There is a need to give the product users limited access to perform a few functions such as changing their emails, retrieving their keys from our database when they misplace them, etc. There is also a need for administrative functions to manage customer records. One needs to sort them, edit them, delete them, add them, view them, search them, register to be an administrator, login as administrator, etc.

If an ecommerce company does not have any of the applications below, it is forced to perform them the 20th century ways—by hand. This means paying for people to answer phones, write emails, keep paper files of customers, etc. The 21st century way is to let software perform these tasks, let websites and videos explain the product features, and let ecommerce close the sale and send the product.

Feel free to use these free Customer Records Management scripts in your business. Note: we know they work well for us (they are well tested), but we assume no liability for how they work in your situation. Similarly, we added lots of security measures such as extensive input filtering, but we make no claims and assume no liability for how securely they work in your situation.

The best security measure to take when using the administrative part of a system like this (meaning the Administrator Apps for Dealing with Customer Records in which category the script below resides, not the Customer Apps for Dealing with Product Keys and Email Addresses) is do not have any links ANYWHERE that link to the URLs of any of the admin files on the server, so neither hackers nor Google finds them. Then use the admin CMS yourself but do not even let your momma use it. Don't even save the link to the admin login as a Favorite, just to be secure. Just stick the login username and password in Roboform and make them impossible to guess. Then use Roboform to logon. The Customer Apps are included online and linked to as part of the product web pages that make life easier for everyone. Their security is mainly handled via extreme input filtering. The Admin Apps like the one below are hidden, unlinked to, and well protected with security measures, password hashes and salts, etc.

This script is called add-customer-records.php

Customer Apps for Dealing with Product Keys and Email Addresses

Administrator Apps for Dealing with Customer Records

The script add-customer-records.php processes both input data from added records, and also session data, which it merely checks out to ensure that the administrator using the script started a session in the login script and defined the proper session variables. The PHP script below gives the administrator a chance to add new records to the db table.

The script begins with checking that the session id is set, sending users to register-with-captcha-for-customer-records-management.php from checkid-in-customer-records-management.php if it is not, but not before unsetting session variables and destroying the session. The session variable $_SESSION['username'] must be set and 6 characters or more, and the session variables $_SESSION['sessionid'] and $_SESSION['userid'] must also be set or the administrator gets a message "Please login." and is sent to the login script login-to-customer-records-management.php which is where the session variables get loaded. The config.php file is included after the defined constant _NODIRECTACCESS gets defined. This gets checked on in the configuration file, and if it is not defined in that file, access to the file is denied. The config.php file uses the defined() function to check whether a given named constant exists. The various scripts that use config.php all use the define() function to define a named constant named '_NODIRECTACCESS' just prior to including config.php. This protects against anyone using the config.php file without first naming that constant with the define() function—a wise security precaution.

Now comes the JavaScript function validate(), which validates the data, even though the script will do PHP validation as well. Why both? For user convenience! Bad data gets a message and sending the cursor to the input box where the goof is, to await your fix. If all validating was PHP only, you'd have to restart the form if there were any goofs, which would elicit crocodile tears from your users—in this case, the administrators.

Then the JavaScript search() method is used in a lot of input validation functions which use regular expression searches that limit both the size of the input string and the characters it contains. Note that is input is bad, after the alert message to warn the user, the focus() method is used before the return false so the cursor goes to the element with the goof. Returning false halts the submitting.

We use both JavaScript and PHP validation to filter input from the user since the cardinal rule for user input is: NEVER TRUST IT. If you want to trust it, simply ensure that it will be safe for putting into your MySQL tables as well as displaying on your web pages. By far the best method here is to use the JavaScript for the users' (the administrator) benefit and the PHP for security. If JavaScript is turned off (in which case our scripts won't even work), the PHP validation scripts are your last line of defense to keep things safe. On the other hand, the JavaScript allows the user to get a user-friendly response to unacceptable or wrong input in fields. Rather than making the user restart the form when he goofs, good JavaScript validation scripts use the focus() method to put the cursor back on the field where the goof occured as well as alerting the user to his error. PHP-only validation forces form restart, which is maddening to users.

We use /^[A-Za-z0-9\-\_\@\.\,]{6,255}$/ types of regular expressions to force the data to conform to the needs of the data fields, with the first part showing the acceptable characters and the second part forcing the length—in this case—to be 6 to 255 characters.

Next we have the PHP. There is a hidden field named "answer" in the entry form with a 1 as its value, so if the form gets submitted, the POSTed value of answer will be this 1, proving the form was sent. Next, 7 values get POSTed from the form that are the data entries. We trim these off to their allowable length. If you think 255 is a lot for email and product key fields and 99 is too much for a name field, remember that there can be more than one of these, separated by commas. (Only the administrator can use the record editing and record adding apps and insert several email addresses or names or keys.) We use the PHP substr() function for the trimming. We use the strlen() function to get the length of data and send the administrator back to the form if the length is too short. JavaScript validation scripts have already pushed the administrator to give long enough entries, so a restart means JavaScript was turned off.

After defining some regular expression patterns, we use the strip_tags() function to dump any tags. Next we use the preg_replace() function to filter the input using the regular expression patterns. Note that we stick an empty string into either product key data that is unset or has a length of 0. This is to avoid any chance of NULL values being stuck in the MySQL database table. Finally, we use the PHP function mysql_real_escape_string() to escape any iffy input data like quotes, etc.

Now let us get all this well-filtered data into the db table with the INSERT statement. Note that we use NULL for the id field, since id is an auto-incrementing primary field. This is the MySQL-recommended way to inspire the id field to auto-increment. It won't put NULL in your id field—honest!

Then we use mysql_affected_rows() to see if our update worked. The mysql_affected_rows() function works on INSERT, UPDATE, REPLACE or DELETE, but not SELECT.

The reason you see:
}}}}}}}
}else{
is because of all those else conditionals in the input filtering. If our script gets as far as this }else{ then the db was added to, but if it doesn't, it's because of the
if($A=="1"){ conditional not being satisfied. In other words, the form has not been submitted. So we want this next section to be run now since it is the input form, which will not run, because of this }else{ if the form gets submitted.

The add-customer-records.php file is this file on this page—so the form action is to reload the page. We use the JavaScript validate() function as the onsubmit event. We keep the input lengths under the limit by use of the maxlength attribute. There's a link to the navigation page with the anchor text: Return to Customer Records Management.

If the adding works, the administrator sees "The record adding was successful. If you want to add another record, click OK. If you want to return to the administrator menu, click Cancel."

You will want to tweak the table name "product" and the field names product4key and product5key to appropriate values for your particular situation. By the way, if the reason for the bounced and optout fields is not apparent, we'll enlighten: They are for the administrator only, to keep track of optout and bounced email responses. He enters 1 in those fields when he gets optout and bounced email responses. Then, in phpMyAdmin, he can use the Export feature and the CSV setting to get the table stuck into Excel where he can sort on those fields and do emailings only to users with 0 in those fields.

This script is called add-customer-records.php

<?php
include_once"checkid-in-customer-records-management.php";
$U=$_SESSION['username'];
define('_NODIRECTACCESS', TRUE);
include_once"includes/config.php";
if (!isset($_SESSION['userid']) || !isset($_SESSION['username']) || $_SESSION['username']<>$U || !isset($U) || $U=="" || strlen($U)<6 || !isset($_SESSION['sessionid'])){echo '<script language="javascript">alert("Please login."); window.location = "login-to-customer-records-management.php";</script>';}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<TITLE>Add Customer Records</TITLE>
<meta name="description" content="Add Customer Records">
<meta name="keywords" content="Add Customer Records,Customer Records,add record,add new record,javascript, dhtml, DHTML">
<STYLE TYPE="text/css">
BODY {margin-left:0; margin-right:0; margin-top:0;text-align:left;background-color:#ccc;}
p, li, td {font:13px Verdana; color:black;text-align:left}
h1 {font:bold 28px Verdana; color:black;text-align:center}
h2 {font:bold 24px Verdana;text-align:center}
h3 {font:bold 15px Verdana;}
#mytable {margin:26px 0 0 138px;width:850px;padding:10px;border:2px solid blue;background-color:#eee;}
#table1 {width:800px;border:1px solid blue;text-align:center;table-layout:fixed}
#menu {background-color:#eee;position:fixed;left:2px;width:106px;padding:5px;border:2px solid blue; position:absolute;top:80px;}
.ie1 {width:213px;max-width:213px;min-width:213px;word-wrap:break-word;text-align:center}
.ie2 {width:100px;max-width:100px;min-width:100px;word-wrap:break-word;text-align:center}
.k {text-align:right}
</STYLE>
<script type="text/javascript">

function validate(){

var ck_email = /^[A-Za-z0-9\-\_\@\.\,]{6,255}$/;
if (document.formpw.email.value.search(ck_email)==-1)
{alert("Please enter 6 to 255 letters, numbers, at signs, hyphens, periods, underscores, and commas for email.");document.formpw.email.focus();return false;}

var ck_name = /^[A-Za-z0-9 \-\,\_\.]{6,99}$/;
if (document.formpw.name.value.search(ck_name)==-1)
{alert("Please enter 6 to 99 letters, spaces, numbers, hyphens, periods, underscores, and commas for name.");document.formpw.name.focus();return false;}

var ck_versions = /^[4-5\,]{1,5}$/;
if (document.formpw.versions.value.search(ck_versions)==-1)
{alert("Please only enter 1 to 5 numbers or commas for versions.");document.formpw.versions.focus();return false;}

var ck_product5key = /^[0-9\,\-]{19,255}$/;var z=document.formpw.product5key.value.toString();
if (document.formpw.product5key.value.search(ck_product5key)==-1 && z.length>0)
{alert("Please only enter 19 to 255 numbers, hyphens, and commas for keys.");document.formpw.product5key.focus();return false;}

var ck_product4key = /^[0-9\,\-]{19,255}$/;var z=document.formpw.product4key.value.toString();
if (document.formpw.product4key.value.search(ck_product4key)==-1 && z.length>0)
{alert("Please only enter 19 to 255 numbers, hyphens, and commas for keys.");document.formpw.product4key.focus();return false;}

var ck_bounced = /^[0-1]{1,1}$/;
if (document.formpw.bounced.value.search(ck_bounced)==-1)
{alert("Please only enter 0 or 1 for bounced.");document.formpw.bounced.focus();return false;}

var ck_optout = /^[0-1]{1,1}$/;
if (document.formpw.optout.value.search(ck_optout)==-1)
{alert("Please only enter 0 or 1 for optout.");document.formpw.optout.focus();return false;}

return true;}

</script>
</head>
<body bgcolor="white">

<?php

$A=$_POST['answer'];

if($A=="1"){

$E=$_POST['email'];
$N=$_POST['name'];
$N=trim($N);
$V=$_POST['versions'];
$E5=$_POST['product5key'];
$E4=$_POST['product4key'];
$B=$_POST['bounced'];
$O=$_POST['optout'];

$E=substr($E,0,255);
$N=substr($N,0,99);
$V=substr($V,0,5);
$E5=substr($E5,0,255);
$E4=substr($E4,0,255);
$B=substr($B,0,1);
$O=substr($O,0,1);

if (strlen($E)<6) {echo '<script language="javascript">alert("Please enter 6 to 255 characters for email."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($N)<6) {echo '<script language="javascript">alert("Please enter 6 to 99 characters for name."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($V)<1) {echo '<script language="javascript">alert("Please enter 1 to 5 characters for versions."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($E5)<19 && strlen($E4)<19) {echo '<script language="javascript">alert("Please enter 19 to 255 characters for product5key."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($E4)<19 && strlen($E5)<19) {echo '<script language="javascript">alert("Please enter 19 to 255 characters for product4key."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($B)<1) {echo '<script language="javascript">alert("Please enter 0 or 1 for bounced."); window.location = "add-customer-records.php";</script>';
}else{
if (strlen($O)<1) {echo '<script language="javascript">alert("Please enter 0 or 1 for optout."); window.location = "add-customer-records.php";</script>';
}else{
$pattern1 = '/[^A-Za-z0-9\\-\\_\\@\\.\\,]/i';
$pattern2 = '/[^4-5\\,]/i';
$pattern3 = '/[^A-Za-z0-9 \\-\\,\\_\\.]/i';
$pattern4 = '/[^0-9\\,\\-]/i';
$pattern5 = '/[^0-1]/';
$replacement = '';
$E=strip_tags($E);
$N=strip_tags($N);
$V=strip_tags($V);
$E5=strip_tags($E5);
$E4=strip_tags($E4);
$B=strip_tags($B);
$O=strip_tags($O);
$E=preg_replace($pattern1, $replacement, $E);
$N=preg_replace($pattern3, $replacement, $N);
$V=preg_replace($pattern2, $replacement, $V);
$E5=preg_replace($pattern4, $replacement, $E5);
if(!isset($E5)||strlen($E5)==0){$E5="";}
$E4=preg_replace($pattern4, $replacement, $E4);
if(!isset($E4)||strlen($E4)==0){$E4="";}
$B=preg_replace($pattern5, $replacement, $B);
$O=preg_replace($pattern5, $replacement, $O);
$E=mysql_real_escape_string($E);
$N=mysql_real_escape_string($N);
$V=mysql_real_escape_string($V);
$E5=mysql_real_escape_string($E5);
$E4=mysql_real_escape_string($E4);
$B=mysql_real_escape_string($B);
$O=mysql_real_escape_string($O);
$sql="INSERT INTO product (id,email,name,versions,product5key,product4key,bounced,optout)
VALUES (NULL, '$E', '$N', '$V', '$E5', '$E4', '$B', '$O')";
$result=mysql_query($sql) or die('Error ,adding failed');

$rc = mysql_affected_rows();
if ($rc>0){echo '<script language="javascript">var answer = confirm ("The record adding was successful. If you want to add another record, click OK. If you want to return to the administrator menu, click Cancel.");if (answer){window.location = "add-customer-records.php";}else{window.location = "customer-records-management.php";} </script>';}
mysql_close();
}}}}}}}

}else{

echo "<center><h1>Add Customer Records</h1></center>";

echo "<div id='mytable'><form id='form2' name='formpw' method='post' action='add-customer-records.php' onsubmit='return validate()'>
<table style='padding:10px;margin-left:20px;background-color:#eee' width='600' border='1' align='center' cellpadding='0' cellspacing='1'>
<tr><td><input type='hidden' name='answer' value='1'></td>
</tr>
<tr><td class='k'><label for='Email'><b>Email: </b></td><td><input type='text' name='email' size='99' maxlength='255' value=''></label></td></tr>
<tr><td class='k'><label for='Name'><b>Name: </b></td><td><input type='text' name='name' size='99' maxlength='99' value=''></label></td></tr>
<tr><td class='k'><label for='User Versions'><b>Versions: </b></td><td><input type='text' name='versions' size='5' maxlength='5' value=''></label></td></tr>
<tr><td class='k'><label for='product5key'><b>product5key: </b></td><td><input type='text' name='product5key' size='99' maxlength='255' value=''></label></td></tr>
<tr><td class='k'><label for='product4key'><b>product4key: </b></td><td><input type='text' name='product4key' size='99' maxlength='255' value=''></label></td></tr>
<tr><td class='k'><label for='Bounced'><b>Bounced: </b></td><td><input type='text' name='bounced' size='1' maxlength='1' value=''></label></td></tr>
<tr><td class='k'><label for='Optout'><b>Optout: </b></td><td><input type='text' name='optout' size='1' maxlength='1' value=''></label></td></tr>
<tr>
<td align='center' colspan='2'><input type='submit' name='Submit' value='Add Record'></td>
</tr>
</table></form></div>";

mysql_close();

}
?>

<div id='menu'>
<b><a HREF="customer-records-management.php">Return to Customer Records Management</a></b>
</div>

</body>
</html>

Free Personal Status Boards (PSB™)

Add Customer Records PHP Script