Email and Name Display PHP Script

The purpose of this script is to display the email and name fields of all the records in a MySQL database, ignoring all other fields (e.g., the ID field). The reason you might want to display these two fields is so you can copy them into a spreadsheet for marketing purposes such as bulk email campaigns sent to your customer list which is stored in a MySQL database online on your host's server.

There are two ways to do the copying. The easiest is also the sloppiest: Ctrl A selects all the text on the page and Crl C copies it. This includes all fields and the side menu as well as the page title and the column titles. When you paste all this into a spreadsheet like Excel by using Ctrl V, you will get two columns of records with title debris for the top couple of cells as well as menu debris at the very end of the table where the last filled cells are. Delete all this debris and you are ready to go. (E.g., to MailChimp?)

The other way to copy gets you no debris to delete. Select the top email with left click while holding down Shift, and keep holding these as you scroll to the bottom of the records and then move rightwards to the name field so that now all emails and names are highlighted. Release the mouse button and Shift key and press Ctrl C to copy and in the spreadsheet use Ctrl V to paste. The reason not to do this instead of the Ctrl A method always is if you have huge databases. Do you really want to hold a key and mouse button as you scroll to the bottom of 2,653,857 records?!

Note that the script below will look into the optout and bounced fields of each record, and if it finds a 1 instead of a 0 in either field, the record will not be displayed. 1 and 0 are a standard way to indicate that something did or did not occur.

Note also that some records may have several email addresses separated by commas. Since in your mailings you can only send emails to one email address at a time, we displayed one row of data (email and name) for each of the email addresses found in the email field. This simulates creating records on the fly that conveniently have only one email address each.

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 email-and-name-display.php

Customer Apps for Dealing with Product Keys and Email Addresses

Administrator Apps for Dealing with Customer Records

The script email-and-name-display.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 emails and names of your MySQL db records, sorted by id in descending order, so that the most recent records are at the top, since id numbers always increment upwards when new records are added, as long as id is an auto-incrementing primary field.

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 used to be at http://dev.jdenny.co.uk/css/ie_fixed.html 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 two of the columns have CSS classes that have styles like width:500px;max-width:500px;min-width:500px;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 two 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! But we've made huge columns so no email or name will overshoot its boundaries.

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 id in descending order, using the ORDER BY keyword with the DESC 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.

As noted, some records may have several email addresses separated by commas. Since in your mailings you can only send emails to one email at a time, we displayed one row of data (email and name) for each of the email addresses found in the email field. If we found optout or bounced 1 values we did not display the record. If they were both 0 values we checked for a comma in the email field: if (($row['bounced']==0) && ($row['optout']==0) && (strpos($row['email'],',')===false)). We displayed the record as is if there was no comma. The strpos() function returns the position of the first occurrence of a string inside another string, or FALSE if the string is not found, so in this case the email field either does or does not contain a comma, and an evaluation of FALSE leads to displaying the record as is. ELSE we display it several times after getting the email addresses separated so we can use a different email for each row we display.

To accomplish this we use the explode() function to turn the string in the email field to two or more $a array values. Then we use the foreach() function to loop through each separated-out email address string array value, applying each to the row display: {$a=explode(',', $row['email']);
foreach($a as $b){
echo "<tr><td class='ie1'>".htmlentities(stripslashes($b), ENT_QUOTES)."</td>";
echo "<td class='ie2'>".htmlentities(stripslashes($row['name']), ENT_QUOTES)."</td></tr>";}}

You will want to tweak the table name "yourMySQLtable" to an appropriate value for your particular situation.

Once you see your MySQL table data onscreen, remember to press ctrl a to select everything for putting the names and emails into Excel or some other spreadsheet.

This script is called email-and-name-display.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>';}
?>

<html>
<head>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<TITLE>Email and Name Display</TITLE>
<meta name="description" content="Email and Name Display">
<meta name="keywords" content="Email and Name Display,View customer record,View record,php,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:500px;max-width:500px;min-width:500px;word-wrap:break-word;text-align:left}
.ie2 {width:350px;max-width:350px;min-width:350px;word-wrap:break-word;text-align:left}
</STYLE>
</head>
<body onload="fix()">

<?php

echo "<center><h1>Email and Name Display (ctrl a to select for Excel)</h1></center>";
echo "<div id='mytable'><a name='form1'></a>";
echo "<table id='table1' border='1' width='850' style='table-layout:fixed'><col width='500'><col width='350'>";
echo "<tr><th class='ie1'>email</th><th class='ie2'>name</th>";
$sql=mysql_query("SELECT * FROM yourMySQLtable ORDER BY id DESC");
while($row = mysql_fetch_array($sql)){
if (($row['bounced']==0) && ($row['optout']==0) && (strpos($row['email'],',')===false))
{echo "<tr><td class='ie1'>".htmlentities(stripslashes($row['email']), ENT_QUOTES)."</td>";
echo "<td class='ie2'>".htmlentities(stripslashes($row['name']), ENT_QUOTES)."</td></tr>";}else
{if (($row['bounced']==0) && ($row['optout']==0))
{$a=explode(',', $row['email']);
foreach($a as $b){
echo "<tr><td class='ie1'>".htmlentities(stripslashes($b), ENT_QUOTES)."</td>";
echo "<td class='ie2'>".htmlentities(stripslashes($row['name']), 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™)

Email and Name Display PHP Script