|
|
Posted by Steve on 03/24/07 18:06
i really should get a full night's sleep before posting. so you just want to
store the name and email information. well, there are not hundreds of
examples on the net...there are thousands. start with mysql. here's a sample
table:
CREATE TABLE people
(
Id INT AUTO_INCREMENT PRIMARY KEY
,
FirstName VARCHAR(30) NOT NULL
,
MiddleName VARCHAR(30) NULL
,
LastName VARCHAR(50) NOT NULL
,
UserName VARCHAR(51) NULL
,
Password VARCHAR(50) NOT NULL
,
Phone VARCHAR(50) NULL
,
Email VARCHAR(255) NOT NULL
,
Expired NUMERIC NULL DEFAULT 1
,
Passport VARCHAR(40) NULL DEFAULT ''
,
Stamp TIMESTAMP NULL DEFAULT NOW()
ON UPDATE NOW()
);
notice, the table integrates some security tie-ins?
========== here's an abstract db class. this one uses mysql. you'll see it
used in subsequent code posted below.
<?
class db
{
static private $_instance = null;
static private $_lastStatement = '';
private function __clone(){}
private function __construct(){}
static function connect($server, $user, $password, $catalog = null)
{
try
{
mysql_connect($server, $user, $password);
if (!is_null($catalog)){ mysql_select_db($catalog); }
} catch (exception $ex) {
print "<pre>\r\n" . $ex->getMessage() . "\r\n" .
' in file ' . $ex->getFile() . "\r\n" .
' on line ' . $ex->getLine() . "\r\n" .
'</pre>';
return false;
}
return true;
}
static function getInstance()
{
if (is_null(self::$_instance)){ self::$_instance = new db(); }
return self::$_instance;
}
static function getLastStatement(){ return self::$_lastStatement; }
static function decode($string)
{
$translation = get_html_translation_table(HTML_ENTITIES);
$translation = array_flip($translation);
$string = strtr($string, $translation);
return $string;
}
static function describe($table)
{
$columns = array();
$records = self::execute('DESCRIBE ' . $table);
foreach ($records as $record)
{
foreach ($record as $column => $property)
{
if ($column == 'FIELD'){ continue; }
$columns[strtoupper($record['FIELD'])][$column] = $property;
}
}
return $columns;
}
static function encode($string)
{
$translation = get_html_translation_table(HTML_ENTITIES);
$string = strtr($string, $translation);
return $string;
}
static function execute($sql, $decode = false, $returnNewId = false)
{
self::$_lastStatement = $sql;
$array = array();
$key = 0;
$records = mysql_query($sql);
$fieldCount = @mysql_num_fields($records);
$translation = get_html_translation_table(HTML_ENTITIES);
$translation = array_flip($translation);
while ($row = @mysql_fetch_array($records, MYSQL_NUM))
{
for ($i = 0; $i < $fieldCount; $i++)
{
$value = $row[$i];
if ($decode){ $value = strtr($value, $translation); }
$array[$key][strtoupper(@mysql_field_name($records, $i))] = $value;
}
$key++;
}
if ($returnNewId)
{
$array = array();
$array[0]['ID'] = mysql_insert_id();
}
@mysql_free_result($records);
return $array;
}
static function prepare($string, $encode = false)
{
if ($encode){ $string = self::encode($string); }
$string = stripslashes(str_replace("'", "''", $string));
return $string;
}
}
?>
========== here's php to validate (most) email addresses (fix the
text-wrapping)
function isEmail($email)
{
if (!$email){ return false; }
$pattern =
"/^((\"[^\"]*?\")|([^\(\)\<\>\@\,\;\:\\\"\[\]\s\*\/]+))@(\[((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){3}|((([a-zA-Z0-9\-]+)\.)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\])$/si";
return preg_match($pattern, $email);
}
========== here's a script to maintain 'people'.
<?
$pageTitle = 'People';
$fullHeader = false;
$securityEnabled = true;
require_once 'relative.path.php';
require_once $relativePath . 'site.cfg.php';
$add = isset($_REQUEST['add']);
$back = isset($_POST['back']);
$confirm = isset($_POST['confirm']);
$delete = isset($_REQUEST['delete']);
$edit = isset($_REQUEST['edit']);
$action = $add ? 'add'
: 'edit';
$method = isset($_POST['method']) ? $_POST['method']
: '';
$errors = array();
$id = isset($_REQUEST['id']) ?
$_REQUEST['id'] : 0;
$personFirstName = isset($_REQUEST['personFirstName']) ?
$_REQUEST['personFirstName'] : '';
$personMiddleName = isset($_REQUEST['personMiddleName']) ?
$_REQUEST['personMiddleName'] : '';
$personLastName = isset($_REQUEST['personLastName']) ?
$_REQUEST['personLastName'] : '';
$personUserName = isset($_REQUEST['personUserName']) ?
$_REQUEST['personUserName'] : '';
$personExpirePassword = isset($_REQUEST['personExpirePassword']) ? 1 : 0;
$personPassword = isset($_REQUEST['personPassword']) ?
$_REQUEST['personPassword'] : '';
$personRePassword = isset($_REQUEST['personRePassword']) ?
$_REQUEST['personRePassword'] : '';
$personEmail = isset($_REQUEST['personEmail']) ?
$_REQUEST['personEmail'] : '';
$personPhoto = isset($_REQUEST['personPhoto']) ?
$_REQUEST['personPhoto'] : '';
$setSiteAccess = isset($_REQUEST['setSiteAccess']) ?
$_REQUEST['setSiteAccess'] : false;
$copyUserPermissions = isset($_REQUEST['copyUserPermissions']) ?
$_REQUEST['copyUserPermissions'] : false;
$passwordMismatch = strtolower($personPassword) !=
strtolower($personRePassword);
if ($back)
{
$add = false;
$delete = false;
$edit = false;
$method = '';
}
if ($confirm)
{
$add = false;
$delete = false;
$edit = false;
$method = '';
$sql = "
DELETE
FROM people
WHERE Id = '" . db::prepare($id) . "'
";
db::execute($sql);
header('location:' . $_SERVER['PHP_SELF']);
exit;
}
if (!($delete || $confirm) && $method == 'put')
{
$sql = "
SELECT COUNT(*) PersonExists
FROM people
WHERE Id != '" .
db::prepare($id) . "'
AND LOWER(UserName) = LOWER('" .
db::prepare($personUserName) . "')
";
unset($records);
$records = db::execute($sql);
$personExists = $records[0]['PERSONEXISTS'] ? true : false;
if (!$personFirstName)
{
$errors['personFirstName'] = 'FIRST NAME is required and cannot be
blank.';
}
if (!$personLastName)
{
$errors['personLastName'] = 'LAST NAME is required and cannot be
blank.';
}
if (!$personPassword)
{
$errors['personPassword'] = 'PASSWORD is required and cannot be
blank.';
}
if ($personPassword && ($personPassword != $personRePassword))
{
$errors['personPassword'] = 'The PASSWORD does not match the PASSWORD
CONFIRMATION.';
}
if ($personExists)
{
$errors['personUserName'] = 'A person with this USER NAME is already
being used.';
}
if (!$personEmail || !isEmail($personEmail))
{
$errors['personEmail'] = 'Invalid EMAIL ADDRESS.';
}
if (!count($errors))
{
if ($action == 'add')
{
$sql = "
INSERT INTO people
(
FirstName ,
MiddleName ,
LastName ,
UserName ,
Password ,
Email ,
Expired
)
VALUES
(
'" . db::prepare($personFirstName) . "' ,
'" . db::prepare($personMiddleName) . "' ,
'" . db::prepare($personLastName) . "' ,
'" . db::prepare($personUserName) . "' ,
'" . db::prepare($personPassword) . "' ,
'" . strtolower(db::prepare($personEmail)) . "' ,
'" . db::prepare($personExpirePassword) . "'
)
";
} else {
$sql = "
UPDATE people
SET FirstName = '" . db::prepare($personFirstName)
.. "' ,
MiddleName = '" . db::prepare($personMiddleName)
.. "' ,
LastName = '" . db::prepare($personLastName)
.. "' ,
UserName = '" . db::prepare($personUserName)
.. "' ,
Password = '" . db::prepare($personPassword)
.. "' ,
Email = '" .
strtolower(db::prepare($personEmail)) . "' ,
Expired = '" .
db::prepare($personExpirePassword) . "' ,
Passport = ''
WHERE Id = '" . $id . "'
";
}
db::execute($sql);
header('location:' . $_SERVER['PHP_SELF']);
exit;
}
}
require_once $site->includeDirectory . 'head.inc.php';
?>
<br>
<div class="bullet" style="background:white no-repeat url('<?=
$site->imagesDirectory ?>bullet.jpg'); color:black; font-size:12pt;
height:50px; padding-top:8px; padding-left:50px;">
<?= $pageTitle ?>
</div>
<hr>
<br>
<?
if ($add || $edit)
{
?>
<script language="javascript">
var skipValidation = false;
function validate()
{
if (skipValidation){ return true; }
var warning = new String();
var el = record.personFirstName;
if (warning.length == 0 && trim(el.value) == '')
{
warning = "FIRST NAME is required.";
}
var el = record.personLastName;
if (warning.length == 0 && trim(el.value) == '')
{
warning = "LAST NAME is required.";
}
var el = record.personUserName;
if (warning.length == 0 && trim(el.value) == '')
{
warning = "USER NAME is required.";
}
var el = record.personEmail;
if (warning.length != 0 && !isEmail(el.value))
{
warning = "Invalid EMAIL.";
}
if (warning.length)
{
alert(warning);
el.focus()
el.select();
return false;
}
return true;
}
</script>
<?
if (!count($errors))
{
$sql = "
SELECT Id ,
FirstName ,
MiddleName ,
LastName ,
UserName ,
Password ,
Email ,
Expired ,
Passport
FROM people
WHERE id = '" . db::prepare($id) . "'
";
unset($records);
$records = db::execute($sql);
$id = $records[0]['ID'];
$personFirstName = $records[0]['FIRSTNAME'];
$personMiddleName = $records[0]['MIDDLENAME'];
$personLastName = $records[0]['LASTNAME'];
$personUserName = $records[0]['USERNAME'];
$personPassword = $records[0]['PASSWORD'];
$personRePassword = $records[0]['PASSWORD'];
$personEmail = $records[0]['EMAIL'];
$personExpirePassword = $records[0]['EXPIRED'];
$personPassport = $records[0]['PASSPORT'];
}
if (count($errors))
{
$displayedErrors = array_unique(array_values($errors));
?>
<div style="color:#660000; font-size:10pt; font-weight:bold;">
ERROR
</div>
<hr style="background-color:#660000; color:#660000;">
<ol>
<?= '<li style="color:#660000; font-size:8pt;">' . implode('<li
style="color:#660000; font-size:8pt;">' . "\r\n", $displayedErrors) ?>
</ol>
<hr style="background-color:#660000; color:#660000;">
<br>
<br>
<?
}
if ($delete)
{
?>
<div style="color:#CC0000; font-size:10pt; font-weight:500;
margin-bottom:20px;">
Are you sure you want to delete this record?
<br>
If so, click the "Confirm" button below. Otherwise, click the "Back"
button below to return.
</div>
<?
}
?>
<form name="record" method="post" onsubmit="return validate();">
<table style="width:400px;">
<tr>
<td class="label" style="width:150px;">
First Name
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personFirstName']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personFirstName"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personFirstName ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Middle Name
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personMiddleName']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personMiddleName"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personMiddleName ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Last Name
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personLastName']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personLastName"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personLastName ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
User Name
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personUserName']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personUserName"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personUserName ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Password
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personPassword']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personPassword"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personPassword ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Password Confirmation
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personRePassword']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personRePassword"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personRePassword ?>"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Email
<span style="color:#660000; font-size:10pt;"><?=
(isset($errors['personEmail']) ? '*' : '') ?></span>
</td>
<td colspan="2">
<input class="value"
name="personEmail"
maxlength="255"
type="text"
autocomplete="off"
value="<?= $personEmail ?>"
style="text-transform:lowercase;"
>
</td>
</tr>
<tr>
<td class="label" style="width:150px;">
Password Expired
</td>
<td colspan="2" style="text-align:left;">
<input name="personExpirePassword"
type="checkbox"
<?= ($personExpirePassword ? 'checked' : '') ?>
>
</td>
</tr>
<tr><td colspan="3"> </td></tr>
<tr><td colspan="3"> </td></tr>
</table>
<?
if ($delete)
{
?>
<input name="confirm" type="submit" style="cursor:'hand';
width:100px;" value="Confirm " onclick="skipValidation=true;">
<?
} else {
?>
<input name="<?= $action ?>" type="submit" style="cursor:'hand';
width:100px;" value="Save ">
<input name="delete" type="submit" style="cursor:'hand';
width:100px;" value="Delete " onclick="skipValidation=true;">
<?
}
?>
<input name="back" type="submit" style="cursor:'hand';
width:100px;" value="Back " onclick="skipValidation=true;">
<input name="id" type="hidden" value="<?= $id ?>">
<input name="method" type="hidden" value="put">
</form>
<script language="javascript">
record.personFirstName.focus();
record.personFirstName.select();
</script>
<?
echo $sessionFooter;
exit;
}
$lastSort = isset($_REQUEST['lastSort']) ?
$_REQUEST['lastSort'] : '';
$sort = isset($_REQUEST['sort']) ? $_REQUEST['sort']
: 'LASTNAME';
$sortDirection = isset($_REQUEST['sortDirection']) ?
$_REQUEST['sortDirection'] : 'ASC';
if ($sort == $lastSort){ $sortDirection = $sortDirection == 'ASC' ? 'DESC' :
'ASC'; }
$lastSort = $sort;
$group = $_REQUEST['group'];
$page = $_REQUEST['page'];
$pages = $_REQUEST['pages'];
$recordsPerPage = $_REQUEST['recordsPerPage'];
if (!is_numeric($group)){ $group = 0; }
if (!is_numeric($page)){ $page = 1; }
if (!is_numeric($pages)){ $pages = 5; }
if (!is_numeric($recordsPerPage)){ $recordsPerPage = 10; }
$findIn = isset($_REQUEST['findIn']) ? $_REQUEST['findIn']
: '';
$find = isset($_REQUEST['find']) ? $_REQUEST['find']
: '';
$sql = "
SELECT COUNT(*) RecordCount
FROM people
";
if ($findIn && $find)
{
$sql .= "
WHERE UPPER(" . $findIn . ") LIKE UPPER('%" .
db::prepare($find) . "%')
";
}
unset($records);
$records = db::execute($sql);
$recordCount = $records[0]['RECORDCOUNT'];
$currentPage = $page;
$group = floor((($page - 1) * $recordsPerPage) / ($recordsPerPage *
$pages));
$navigation = array();
$page = $group * $pages + 1;
$lastPage = $page + $pages;
$maxPages = ceil($recordCount / $recordsPerPage);
$range = ($currentPage * $recordsPerPage) - $recordsPerPage;
if ($group)
{
$navigation[] = '<a class="navigation" href="?page=' . ($page - 1) .
'&sortDirection=' . $sortDirection . '&sort=' . $sort . '"
title="Previous"><<</a>';
}
for ($index = 0; $index < $pages; $index++)
{
$navigation[] = '<a class="navigation" href="?page=' . $page .
'&sortDirection=' . $sortDirection . '&sort=' . $sort . '" title="Page ' .
$page . '">' . $page. '</a>';
if ($page == $maxPages){ break; }
$page++;
}
if ($lastPage < $maxPages)
{
$navigation[] = '<a class="navigation" href="?page=' . $page .
'&sortDirection=' . $sortDirection . '&sort=' . $sort . '"
title="Next">>></a>';
}
$navigation[] = '<span style="color:#666666; padding-left:25px;
font-size:7.25pt;">[Page ' . $currentPage . ' of ' . $maxPages . ']</span>';
?>
<div style="float:right; margin-right:5px;">
<select name="findIn" class="input">
<option value="" <?= (!$findIn ? 'selected' :
'' ) ?>></option>
<option value="LASTNAME" <?= ($findIn == 'LASTNAME' ? 'selected' :
'' ) ?>>Last Name</option>
<option value="FIRSTNAME" <?= ($findIn == 'FIRSTNAME' ? 'selected' :
'' ) ?>>First Name</option>
<option value="MIDDLENAME" <?= ($findIn == 'MIDDLENAME' ? 'selected' :
'' ) ?>>Middle Name</option>
<option value="EMAIL" <?= ($findIn == 'EMAIL' ? 'selected' :
'' ) ?>>Email</option>
</select>
<input name="find" class="input" type="text" value="<?= $find ?>">
<input type="button" value="Find ..." onclick="return navigate('<?=
$_SERVER['PHP_SELF'] ?>', '1');">
</div>
<br clear="all">
<br>
<table style="width:730px;">
<th style="background-color:#336699; border-bottom:1px solid
lightsteelblue; padding:5px; text-align:center; width:100px;">
<a
class="menuItem"
href="<?= $_SERVER['PHP_SELF'] ?>?add=1"
style="background-color:#336699; border:none; color:white;
font-weight:600; text-decoration:underline;"
>Add Person</a>
</th>
<th
style="border-bottom:1px solid lightsteelblue; border-right:1px solid
lightsteelblue; cursor:pointer; font-size:8pt;"
title="Click to sort"
onclick="document.location='<?= $_SERVER['PHP_SELF'] ?>?lastSort=<?=
$lastSort ?>&sortDirection=<?= $sortDirection ?>&sort=LASTNAME'"
>Last Name</th>
<th
style="border-bottom:1px solid lightsteelblue; border-right:1px solid
lightsteelblue; cursor:pointer; font-size:8pt;"
title="Click to sort"
onclick="document.location='<?= $_SERVER['PHP_SELF'] ?>?lastSort=<?=
$lastSort ?>&sortDirection=<?= $sortDirection ?>&sort=FIRSTNAME'"
>First Name</th>
<th
style="border-bottom:1px solid lightsteelblue; border-right:1px solid
lightsteelblue; cursor:pointer; font-size:8pt;"
title="Click to sort"
onclick="document.location='<?= $_SERVER['PHP_SELF'] ?>?lastSort=<?=
$lastSort ?>&sortDirection=<?= $sortDirection ?>&sort=MIDDLENAME'"
>Middle Name</th>
<th
style="border-bottom:1px solid lightsteelblue; border-right:1px solid
lightsteelblue; cursor:pointer; font-size:8pt;"
title="Click to sort"
onclick="document.location='<?= $_SERVER['PHP_SELF'] ?>?lastSort=<?=
$lastSort ?>&sortDirection=<?= $sortDirection ?>&sort=EMAIL'"
>Email</th>
<?
$sql = "
SELECT Id ,
FirstName ,
MiddleName ,
LastName ,
UserName ,
Email
FROM people
";
if ($findIn && $find)
{
$sql .= "
WHERE UPPER(" . $findIn . ") LIKE UPPER('%" .
db::prepare($find) . "%')
";
}
$sql .= "
ORDER BY " . $sort . " " . $sortDirection . "
LIMIT " . $range . ", " . $recordsPerPage . "
";
unset($records);
$records = db::execute($sql);
foreach ($records as $record)
{
$color = $color == 'white' ? '#EEEEEE' : 'white';
$description = trim($record['LASTNAME'] . ', ' . $record['FIRSTNAME'] . '
' . substr($record['MIDDLENAME'], 0, 1));
?>
<tr>
<td style="background-color:<?= $color ?>; font-size:8pt;
text-align:right;">
<a
class="menuItem"
href="<?= $_SERVER['PHP_SELF']?>?edit=1&id=<?= $record['ID'] ?>"
style="background-color:<?= $color ?>; font-size:7.25pt;
font-weight:normal; text-decoration:underline; white-space:nowrap;"
title="EDIT [ <?= $description ?> ]"
>Edit ▷</a>
<br>
<a
class="menuItem"
href="<?= $_SERVER['PHP_SELF']?>?edit=1&delete=1&id=<?=
$record['ID'] ?>"
style="background-color:<?= $color ?>; font-size:7.25pt;
font-weight:normal; text-decoration:underline; white-space:nowrap;"
title="DELETE [ <?= $description ?> ]"
>Delete ▷</a>
</td>
<td style="background-color:<?= $color ?>; width:150px;
text-align:left;"><?= $record['LASTNAME'] ?></td>
<td style="background-color:<?= $color ?>; font-size:8pt;
text-align:left;"><?= $record['FIRSTNAME'] ?></td>
<td style="background-color:<?= $color ?>; font-size:8pt;
text-align:left;"><?= $record['MIDDLENAME'] ?></td>
<td style="background-color:<?= $color ?>; font-size:8pt;"><?=
$record['EMAIL'] ?></td>
</tr>
<?
}
?>
</table>
<br>
<hr>
<br>
<?
if (!count($records))
{
echo 'There are currently no records to display.';
} else {
?>
<div style="float:right; margin-right:15px;">
<?
echo implode("\r\n", $navigation);
?>
</div>
<br clear="all">
<br>
<?
}
echo $sessionFooter;
?>
[Back to original message]
|