Sort Customer Records by Name 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 sort-customer-records-by-name.php

Customer Apps for Dealing with Product Keys and Email Addresses

Administrator Apps for Dealing with Customer Records

The script sort-customer-records-by-name.php processes no data except 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 view of all the MySQL db records, sorted by name.

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.

Next we do browser sniffing to deal with display quirks. Then we have several JavaScript calculation functions that are part of the algorithm for dealing with fixed positioning. Much as we'd like to claim credit for it, in truth it's actually JDenny who is the CSS and JavaScript genius who came up with this brilliant CSS and JavaScript fixed positioning code and put it on the Web to help those of us that were stumped trying to figure out good fixed positioning codes. Thanks a million JDenny! This has to be the most ingenious DHTML using CSS and JavaScript we've ever seen! (Note: The b.gif URL should be left as is, but you do NOT need a b.gif file: it is a dummy name just to keep the code working.) The CSS for BODY, the mytable id, and the menu id in the CSS styling section are part of this algorithm for fixed positioning as well. The BODY tag's CSS that uses t.gif for the background is using a real file. Make it yourself: it's simply an invisible 1-pixel gif, and this goes a long way to prevent any shimmering of the fixed-position side menu. If you are feeling lazy type in http://www.css-resources.com/t.gif and do Save As from the browser's File menu, since right clicking is dumb—right click WHAT?

Next comes the CSS. We already looked at the fact there is some weird stuff in BODY, the mytable id and the menu id styling. These too help browsers handle fixed positioning.

Next we have the PHP. We echo an HTML table to the screen using a 'table-layout:fixed' style, many uses of the col tag with widths for layout precision, then many uses of the th tag with widths—again for layout precision. Note that four of the columns have CSS classes that have styles like width:213px;max-width:213px;min-width:213px;word-wrap:break-word;text-align:center. What we are accomplishing with all this seemingly CSS overkill is to ensure that all the browsers will display the data in these four columns in a way that works. If we do not deal with this issue, a long email in the email column, for example, will either overlap the name column or get cut off so only part of it is displayed. What we desire is for the data to break at the border of the column and wrap down to the next line REGARDLESS of the fact it may be in the middle of a word. Browsers do not do such a thing without CSS prompting!

Now that we have firmly established labelled columns to put the record data in, it's time to read the MySQL database table and display the data. Since we've already taken care of the MySQL connection in the config.php file at the beginning of the script, we now SELECT everything in the table, ordered by name, using the ORDER BY keyword. We use a WHILE loop and the mysql_fetch_array() function which fetches a row at a time until it runs out of rows. Then we display the data rows, using the PHP functions htmlentities() and stripslashes() for display safety.

You will want to tweak the table name "yourtable" and the field names product4key and product5key to appropriate values for your particular situation.

This script is called sort-customer-records-by-name.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>Sort Customer Records by Name</TITLE>
<meta name="description" content="Sort Customer Records by Name">
<meta name="keywords" content="Sort Customer Records by Name,Customer Records,Sort by Name,find record,javascript, dhtml, DHTML">
<script language="javascript">
mactest=(navigator.userAgent.indexOf("Mac")!=-1) //My browser sniffers
is_chrome = navigator.userAgent.toLowerCase().indexOf('chrome') > -1
Netscape=(navigator.appName.indexOf("Netscape") != -1)
msafari=(navigator.userAgent.indexOf("Safari")!= -1)
wsafari=0; if(!mactest&&msafari){wsafari=1;msafari=0}
is_opera = 0; if(window.opera){is_opera=1}
is_ie_mac = 0; is_ie=0;if(document.all){is_ie=1}
if(is_ie&&mactest){is_ie_mac=1}

function calculateBgX(oElement) {return document.body.scrollLeft - getOffsetLeft(oElement);}

function calculateBgY(oElement) {return document.body.scrollTop - getOffsetTop(oElement);}

function getOffsetTop(oElement) {var iResult= oElement.offsetTop;
while (oElement.offsetParent) {oElement = oElement.offsetParent;iResult += oElement.offsetTop;}
return iResult;}

function getOffsetLeft(oElement) {var iResult= oElement.offsetLeft;
while (oElement.offsetParent) {oElement = oElement.offsetParent;iResult += oElement.offsetLeft;}
return iResult;}

function fix(){if(Netscape||is_opera){e=document.getElementById("mytable");e.style.position="absolute";e.style.marginTop=0+"px";
e=document.getElementById("j");e.style.top=p+"px";}}

</script>
<STYLE TYPE="text/css">
BODY {margin-left:0; margin-right:0; margin-top:0;text-align:left;background-image:url(t.gif);background-attachment:fixed;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: url('b.gif');background-attachment: fixed;background-position: expression((calculateBgX(this))+"px "+(calculateBgY(this))+"px");background-color:#eee;}
#table1 {width:100%;border:1px solid blue;text-align:center}
#menu {background-color:#eee;position:fixed;left:2px;width:106px;padding:5px;border:2px solid blue;position: expression("absolute");top: expression(eval(document.body.scrollTop)+60);}
.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}
.ie3 {width:165px;max-width:165px;min-width:165px;word-wrap:break-word;text-align:left}
</STYLE>
</head>
<body onload="fix()">

<?php

echo "<center><h1>Sort Customer Records by Name</h1></center>";
echo "<div id='mytable'><a name='form1'></a>";
echo "<table id='table1' border='1' width='800' style='table-layout:fixed'><col width='35'><col width='213'><col width='100'><col width='30'><col width='165'><col width='165'><col width='57'><col width='47'>";
echo "<tr><th width='35' align='center'>id</th><th class='ie1'>email</th><th class='ie2'>name</th><th width='30' align='center'>ver.</th><th class='ie3'>product5key</th><th class='ie3'>product4key</th><th width='57' align='center'>bounced</th><th width='47' align='center'>optout</th></tr>";
$sql=mysql_query("SELECT * FROM yourtable ORDER BY name");
while($row = mysql_fetch_array($sql)){
echo "<tr><td>".htmlentities(stripslashes($row['id']), ENT_QUOTES)."</td>";
echo "<td class='ie1'>".htmlentities(stripslashes($row['email']), ENT_QUOTES)."</td>";
echo "<td class='ie2'>".htmlentities(stripslashes($row['name']), ENT_QUOTES)."</td>";
echo "<td>".htmlentities(stripslashes($row['versions']), ENT_QUOTES)."</td>";
echo "<td class='ie3'>".htmlentities(stripslashes($row['product5key']), ENT_QUOTES)."</td>";
echo "<td class='ie3'>".htmlentities(stripslashes($row['product4key']), ENT_QUOTES)."</td>";
echo "<td>".htmlentities(stripslashes($row['bounced']), ENT_QUOTES)."</td>";
echo "<td>".htmlentities(stripslashes($row['optout']), ENT_QUOTES)."</td></tr>";
}
echo "</table><center><BR></center></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™)

Sort Customer Records by Name PHP Script