If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Use these tricks to generate IDS SQL scripts
By Lester Knutsen

All good DBAs have some tricks up their sleeves to make their jobs easier. One I like to use is to write an SQL query of the Informix system tables and use the output of that query to generate another SQL script. This trick saves a lot of time (and reduces the possibility of missing a table) when you’re working with a large database with many tables.

Let’s say you need to change all the tables in a database from page-level to row-level locking, or that you need to defer foreign key constraints on all the tables to do a load. You could write an SQL script to do this one table at a time, but with a few hundred tables this task quickly becomes lengthy and tedious. Or, you could write an SQL statement to query the system tables and generate the required SQL script for you. I’ll show you three examples for which this approach is particularly useful. First, when changing the locking mode of all tables in a database and, second, when changing database privileges. The third example shows how to generate a script to enable or disable all the constraints of all the tables in a database. This script can be very helpful when you need to load or change data en masse and constraints are preventing that from being accomplished.

Changing Page-Level Locking

When a table is first created, a locking mode is defined. The two lock modes are page level (all rows on a page are locked when one row is locked) and row level (only one row in use is locked). Page-level locking is the default for performance reasons. However, to maximize concurrence, a DBA will often need to change to row-level locking. You can create a script to first check the lock level of all tables in a database and then, for any tables that use page level locking, to generate the SQL code to alter that locking mode to row level. Listing 1 shows the SQL script that generates the second SQL script, which will change the lock mode of all tables to row-level locking. Listing 2 shows the output that results.

The basic process in all these scripts is to use the system tables combined with text strings in SQL to output a text file with SQL commands. You can create scripts to unload data, alter extent sizes, check tables’ grant privileges, and automate many other DBA tasks.

In Listing 1, the lines output to lockmod.sql without headings create a file with the SQL commands to execute what you need done, without the normal column headings that SQL produces. Note that you’ll use dbaccess to create the output, and dbaccess wraps the output file at 80 characters. Many SQL command lines will be longer then 80 characters, but this wrapping doesn’t cause a problem with the SQL generated — it just affects the way it looks.

Generating Scripts to Revoke Privileges

Another Informix default that I often need to change is public. When a table is created, public is granted select, update, delete, and insert privileges on that table by default. In other words, any user who can access the database can modify all data in that table. Listing 3 shows an SQL script that generates the SQL to revoke all public table privileges on a database.

Because it reads the system tables, this script can be run on any database. However, you can’t revoke privileges granted by someone else, so the script limits itself to tables on which you granted the public privilege. If several developers are creating tables in a database, each developer will need to run this script to revoke public privileges. The script can be modified to grant privileges to specific users or a set of users. Listing 4 shows the output that results from running this script.

Generate Scripts to Enable or Disable Constraints

On a large database with hundreds of tables, it would take a lot of typing to write out all the SQL commands to enable or disable constraints. Listing 5 is a little SQL script that may save you a bit of time and effort. This script will generate the SQL statements to enable or disable all the foreign key constraints in a database.

Script Away

I’ve shared just a few examples of the many scripts you can create using SQL with the system tables. On a large database with many tables, creating scripts by hand is tedious and error prone. Creating scripts using the system tables that store information about all the tables and columns in a database ensures that all tables are included. Check the software section of International Informix User Group Web site for more examples.

Listings

Listing 1. SQL script to generate another SQL script to set row level locking for all database tables.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Generate SQL to set row level locking for all database tables
####################################################
}
output to lockmod.sql — Create SQL script
without headings — Don’t include column headings
select “alter table “, — Text
trim(tabname) , — Table name
” lock mode (row);” — Text
from systables
where tabid > 99 — Don’t get the systables
and tabtype = “T” — Get real tables not views
and locklevel = “P” — Get tables with page level locking
order by tabname

Listing 2. lockmod.sql after the script has been run on the stores_demo database.

alter table call_type lock mode (row);
alter table catalog lock mode (row);
alter table cust_calls lock mode (row);
alter table customer lock mode (row);
alter table items lock mode (row);
alter table log_record lock mode (row);
alter table manufact lock mode (row);
alter table orders lock mode (row);
alter table state lock mode (row);
alter table stock lock mode (row);

Listing 3. SQL script to generate another SQL script to revoke all public privileges.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Revoke all public privileges for all tables
####################################################
}
output to “revokepub.sql”
without headings
select “revoke all on “, — Revoke all privileges
trim(tabname) , — Table name from systables
“from public;”
from systables , systabauth
where systables.tabid = systabauth.tabid
and grantee = “public” — Select table names where public has privileges
and grantor = USER — Only revoke the ones you have privilege to revoke
and systables.tabid > 99 — Don’t revoke the system table privileges

Listing 4. revokepub.sql after the script has been run on the stores7 database.

revoke all on customer from public;
revoke all on orders from public;
revoke all on manufact from public;
revoke all on stock from public;
revoke all on items from public;
revoke all on state from public;
revoke all on call_type from public;
revoke all on cust_calls from public;
revoke all on custview from public;
revoke all on log_record from public;
revoke all on catalog from public;

Listing 5. SQL script to generate SQL script to disable foreign key constraints.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Disable and enable all tables foreign key constraints
####################################################
}
output to ” disable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “disabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;
output to ” enable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “enabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;

Lester Knutsen
is president of Advanced DataTools Corp., an IBM Informix consulting
and training partner specializing in data warehouse development,
database design, performance tuning, and Informix training and support.
He is president of the Washington D.C. Area Informix User Group, a
founding member of the International Informix Users Group, and an IBM
gold consultant.

• 0 Comments

A quick recap

In case you missed it, you probably know how it is, you’re just sitting down to dinner (perhaps in front of your favourite TV show) when the phone rings, Hello, can I speak to the person responsible for your gas/electricity/telephone/bills, please?

This is a real conversation, I promise nothing has been made up.

The double glazing salesman

Salesman

Good evening Sir, It’s Jim from ShinyWhite Windows, can I speak to Mr. Malarkey please?

Malarkey

I’m sorry Jim, I don’t want to waste your time, we haven’t got windows.

Salesman

No windows?

Malarkey

No, we live in a caravan.

Salesman

But caravans have windows don’t they?

Malarkey

Well we did have… but now we just use cling-film. Not the ordinary kitchen stuff mind, you know, that thick stuff that they wrap around palettes and comes on a big roll. We wrap it around the van a few times and when it gets dirty we just replace it.

Salesman

You’re having me on?

Malarkey

No, I’m deadly serious.

Salesman

(Sniggering) But that can’t be very secure… can it?

Malarkey

That’s why we have a big dog (false woof) WOOOOFF!

Salesman

(Silence)

Malarkey

Err, hello…

• 0 Comments

In part 1 of this tutorial, we laid the ground work for the entire system. That included the core functions, and the AJAX handler, which receives + handles the AJAX requests (deja vu?). We also made the login page, which also handles registration and logging out.

In part 2, we’re going to create the user’s main page, the private messaging system, and the settings manager, where uses can change there name, E-mail, and password.

When a user sucessfully logs in, they are redirected to index.php, which will simply greet them and give them a few links. If a guest vists that page, a message telling them to register or login will appear. The index page also manages your settings.

File: index.php

 

<?php
session_start();
//Load up userdata, database connection, functions
include(‘./config.php’);
//Display a guest message
if(!check_login($username, $password))
{
$title = “Guest Page”;
$con = “<h2>Welcome Guest</h2>
<p>Hi there. I see that you’re a guest to this site, in order to get the best experience out of “. SITE_NAME .“, you must be registered. Registering is totally free and requires only a few minutes of your time, we promise! If you’re interested, you can register <a href=’login.php?do=reg’>here</a>.</p><p>If you’re not a guest, and simply haven’t logged in, you may log in <a href=’login.php’>here</a>.”;
}
else
{
//Footer links
$footer = “<p><a href=’index.php’>Home</a> - <a href=’login.php?do=logout’>Logout</a></p>”;
//Welcome message with options
if($_GET[‘do’] == ” || !$_GET[‘do’])
{
$title = $user[‘name’] .“’s Control Panel”;
$con = “Welcome {$user[’name’]} to your very own control panel. Here you can <a href=’pm.php’>view</a> and <a href=’pm.php?do=send’>send</a> messages to other members, and control your <a href=’?do=settings’>settings</a>.<br /><br />”;
//This counts the number of unread PM’s the user has
$unread = count_unread($user[‘username’]);
//Give a message if it’s greater than 0
if($unread > 0)
{
$con .= “<span style=’color:red; font-weight:bold;’>You have $unread unread private message(s)</span>”;
}
}

Don’t save that file yet, we’re not done! Our last part will display the settings page. script.aculo.us makes in place editing easy, but it still could be improved. However, it’s definitly functional, and is very easy to implement. In order to make a field editable, we just put this JavaScript snippet in:

 

new Ajax.InPlaceEditor(element_id, url);

That snippet must be after the element is declared, but that’s no trouble. The password changer also uses AJAX, but no in place editing.

File: index.php

 

else if($_GET[‘do’] == ’settings’)
{
$title = ‘Manage Your Settings’;
$con = “<h2>Personal Settings</h2>
Name: <span id=’name’>{$user[’name’]}</span><br />
E-Mail: <span id=’email’>{$user[’email’]}</span>
<script type=’text/javascript’>
new Ajax.InPlaceEditor(’name’, ‘ajax.php?m=optsave&type=name&id={$user[’id’]}’);
new Ajax.InPlaceEditor(’email’, ‘ajax.php?m=optsave&type=email&id={$user[’id’]}’);
</script>
<h2>Password Control</h2>
<p>In order to edit your password, you must first verify your current one, then enter your new one:</p>
<form name=’password_reset’>
Current Password: <input type=’password’ id=’current_password’ /><br />
New Password: <input type=’password’ id=’new_password’ /><br />
Confirm New Password: <input type=’password’ id=’new_password_confirm’ /><br />
<input type=’button’ value=’change password’ onclick=’password()’ id=’change_password’ /></form>
<div id=’password_status’></div>”;
}
}
?>

You should be able to understand all of this, we’re just sticking a few variables here and there, nothing major. Next we’re going to make our JavaScript and HTML.

File: index.php

 

<html>
<head>
<title><?php echo $title; ?></title>
<!– Include the scriptaculous library –>
<script src=‘../scriptaculous/prototype.js’></script>
<script src=‘../scriptaculous/scriptaculous.js’></script>
<script type=‘text/javascript’>
function password()
{
//Typing skils. . .
if($F(‘new_password_confirm’) !== $F(‘new_password’))
{
alert(“Please make sure both your new passwords match!”);
$(‘new_password’).focus();
return;
}
else if($F(‘new_password_confirm’) == ” || $F(‘new_password’) == ” || $F(‘current_password’) == ”)
{
alert(“Please fill in all fields.”);
$(‘current_password’).focus();
}
else
{
//construct the ajax request
//$F() gets the field with the given id’s value
var opt = {
method:‘post’,
postBody:‘m=pwd&current=’ + $F(‘current_password’) + ‘&new=’ + $F(‘new_password’) + ‘&id=<?php echo $user[’username‘]; ?>’,
onSuccess: function(t) { $(‘password_status’).innerHTML = t.responseText; $(‘change_password’).disabled = false; },
onLoading: function() { $(‘login_button’).value = ‘Working’; $(‘change_password’).disabled = true; }
}
//send request to the ajax.php
new Ajax.Request(‘ajax.php’, opt);
}
}
</script>
</head>
<body>
<?php echo $con, $footer; ?>
</body>
</html>

Hoorah, the settings manager is totally complete. Now we just need to do the private messaging system. Our only use of AJAX there will be to delete messages, becuase it’s totally worthless to have to reload your inbox to delete one lousy message. The top of the script will use the MIN_AUTH_LEVEL constant, becuase we don’t want guests trying to access inboxes! We set the auth level to 1, meaning only users + admins.

File: pm.php

 

<?php
session_start();
define(‘MIN_AUTH_LEVEL’, ‘1′);
include(‘./config.php’);
$footer = “<a href=’index.php’>Home</a> - <a href=’?do=’>Inbox</a> - <a href=’?do=send’>Send New PM</a> - <a href=’login.php?do=logout’>Logout</a>”;

Note how the min auth level is declared before we include the config file, if it’s not, it won’t work. Let’s work on the inbox.

File: pm.php

 

if($_GET[‘do’] == ”)
{
$title = “PM Inbox”;
//Fetch an associative array of the user’s PMs
$pms = listpms($user[‘username’]);
//Notice from send new PM page
if($notice)
{
$con .= “<strong>$notice</strong>”;
}
//Begin the PM inbox
$con .= “<h2>PM Inbox</h2>
<p>Clicking the delete icon permanetly deletes the message! Click on the title to view the full message.</p>
<table border=’1′>
<tr><th>Title</th><th>From</th><th>Delete</th></tr>”;
//For the unpopular, a no PM message so the table looks decent
if(count($pms) == 0)
{
$con .= “<tr><td colspan=\”3\”><strong>Sorry, you have no PM’s.</strong></td></tr>”;
}
else
{
//Cycle through every PM, $v will store an associative array of every PM’s details
foreach($pms as $v)
{
//Delete_pm starts an AJAX request to delete the PM
$con .= “<tr id=\”pm_{$v[’id’]}\”><td><a href=’?do=view&id={$v[’id’]}’>{$v[’title’]}</a></td><td>{$v[’from’]}</td><td align=’center’><a href=’#’ onclick=\”delete_pm(’{$v[’id’]}’, ‘{$v[’title’]}’, false);\”><img src=\”./images/delete.png\” style=\”border:none;\” /></a></tr>”;
}
}
$con .= “</table>”;
}

Nothing earth shattering there either, our handy function gets every PM for us in a nice array that we can easily cycle through. Next we have to handle viewing PM’s. . . oh no!

File: pm.php

 

else if($_GET[‘do’] == ‘view’)
{
$footer = “<a href=’index.php’>Home</a> - <a href=’pm.php’>Inbox</a>”;
//Single array of a PM
$pm = get_pm($_GET[‘id’]);
//get_pm checked ownership of the PM, it’s not the current users
if(!$pm)
{
$con = “You are not authorized to view this PM!”;
}
else
{
//Mark this PM as read
mark_read($pm[‘id’]);
//Display the message from the pm array
//The reply form will toggle up and down using a scriptaculous effect (a pretty one!)
$con = “<h2>{$pm[’title’]}</h2>
<p>From: {$pm[’from’]}<br />
{$pm[’message’]}<br /><a href=’#’ onclick=’reply();’>Reply</a> - <a href=’#’ onclick=\”delete_pm(’{$pm[’id’]}’, ‘{$pm[’title’]}’, true);\”>Delete</a></p>
<div id=’reply’ style=’display:none;’>
<h3>Reply:</h3>
<form method=’post’ action=’?do=send’>
To: <input type=’text’ name=’to’ value=’{$pm[’from’]}’ /><br /><br />
Title: <input type=’text’ name=’title’ value=’Re: {$pm[’title’]}’ /><br /><br />
Message: <br />
<textarea rows=’10′ cols=’50′ name=’message’></textarea><br /><br />
<input type=’submit’ value=’Send!’ name=’send’ /></form>
</div>”;
}
$title = “View PM”;
}

Lastly, we’ve got to be able to send messages, otherwise it’s useless! In this section, we make use of our notice variable.

File: pm.php

 

else if($_GET[‘do’] == ’send’)
{
//We’re sending
if($_POST[’send’])
{
//attempt to send the PM
if(sendpm($user[‘username’], $_POST[‘to’], $_POST[‘title’], $_POST[‘message’]))
{
//Display notice at inbox
$notice = “PM Sent”;
}
else
{
//same as above
$notice = “PM Couldn’t Be Sent!”;
}
//Back to the inbox to see the notice
header(“Location: pm.php”);
}
//Display the form
else
{
$title = “Send PM”;
$con = “<h2>Send New PM</h2>
<form method=’post’ action=’?do=send’>
To: <input type=’text’ name=’to’ /><br /><br />
Title: <input type=’text’ name=’title’ /><br /><br />
Message: <br />
<textarea rows=’10′ cols=’50′ name=’message’></textarea><br /><br />
<input type=’submit’ value=’Send!’ name=’send’ /></form>”;
}
}
?>

Let’s get our last HTML + JavaScript for this part out of the way.

File: pm.php

 

<html>
<head>
<title><?php echo $title; ?></title>
<script src=‘../scriptaculous/prototype.js’></script>
<script src=‘../scriptaculous/scriptaculous.js’></script>
<script type=‘text/javascript’>
//Delete via AJAX
function delete_pm(pmid, pmtitle, redir)
{
if(confirm(“Are you sure you want to delete the message titled “ + pmtitle))
{
var opt = {
method:‘post’,
postBody:‘m=delpm&password=<?php echo $user[’password‘]; ?>&id=’ + pmid + ‘&uid=<?php echo $user[’username‘]; ?>’,
onSuccess: function(t) { handle_delete(t, pmid, redir); }
}
new Ajax.Request(‘ajax.php’, opt);
}
}

//Toggle reply form with a fancy effect
function reply()
{
if(!Element.visible(‘reply’))
{
Effect.SlideDown(‘reply’);
}
else
{
Effect.SlideUp(‘reply’);
}
}

//Either redirect to the inbox, or remove the table row of that PM
function handle_delete(t, pmid, redir)
{
if(t.responseText == “0″)
{
alert(“The PM could not be deleted! This may be a systems error, so please try again.”);
}
else
{
if(redir)
{
window.location = ‘pm.php’;
}
else
{
new Effect.Fade(‘pm_’ + pmid);
}
}
}
</script>
</head>
<body>
<?php echo $con, $footer; ?>
</body>
</html>

That’s the end of part 2. As you can see, most of the code was just determining what needed to be done and sending messages to the user. Becuase of our backend, we really only have to call one or two functions to get what we need, or to protect a page from guests. There’s no more stuff user-end in this tutorial, part 3 will cover only one thing, a simply admin panel to modify user’s data. At the end of part 3, an example will be put up.

Download all the script files in an unique .zip archive here

• 0 Comments

One of the most common request of people learning PHP is that they want to make a member system thing. That’s what we’re going to do, only cooler. I’m going to try and teach you some basic concepts of JavaScript and PHP so that you can take this code and make it a hundred times better by adding new features.

The tutorial isn’t meant to be pretty, except for the JavaScript effects. I don’t use much color, and it’s standard yucky formatting. It’s your job to make it look nice.

The tutorial will cover setting up the database, and making basic front and backends. The features are:

• Setting up the database, backend (Part 1)
• Registering with E-mail verification (Part 1)
• Logging in and out (Part 1)
• Private Messaging (Part 2)
• Changing password, E-mail, and name (Part 2)
• Simple admin area to modify + delete users (Part 3)

Before we start to code, we need to design how our system is going to work. The configuration file will be the core, do-everything file. It connects to the database, includes the necesscary file, gets a users details, and makes sure the user can view whatever page they are at. The included file will store all of our functions, these functions perform things such as logging in, registering, sending PM’s, or modifying user’s details.

We will have global variable, this will be $user. $user will be loaded by the config file, and will store the current users details. If the user is a guest, it will have dummy data. $user is an array and can be used anywhere as long as the config file was included.

Protecting pages is also easy. There is a small permissions system in place, there are 4 levels. -1 = non-activated user, 0 = guest, 1 = user, and 2 = admin. This permission level is stored in $user under the name admin. Our config file will check for a constant called MIN_AUTH_LEVEL, if it exists, it will compare the user’s level to the MIN_AUTH_LEVEL. If the user’s auth level is to low, they get a error, otherwise they see the content. This makes it easy to protect pages from guests and protect the admin panel.

Enough talk, you should get how the system will work, now let’s set up the database. We’re going to require three tables, one for the PM’s, one for the user’s, and one for the activation keys.

 

CREATE TABLE `keys` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`key` varchar(32) NOT NULL DEFAULT ”,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar(255) NOT NULL DEFAULT ‘0′,
`to` varchar(255) NOT NULL DEFAULT ‘0′,
`message` longtext NOT NULL,
`title` varchar(255) NOT NULL DEFAULT ”,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`read` int(1) NOT NULL DEFAULT ‘0′,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT ”,
`password` varchar(32) NOT NULL DEFAULT ”,
`name` varchar(255) NOT NULL DEFAULT ”,
`email` varchar(255) NOT NULL DEFAULT ”,
`ip` varchar(255) NOT NULL DEFAULT ”,
`admin` int(1) NOT NULL DEFAULT ‘0′,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Now that we have the tables set up, we can create our config file. As we said earlier, this file will contain database information, get user information, and check permissions. This file is not meant to be accesed directly, it will be included by other files (like login, index, etc.) You might want to read up on constants and the ternary operator.

File: config.php

 

<?php
//Database
define(‘DB_HOST’, ‘localhost’);
define(‘DB_USER’, ‘root’);
define(‘DB_PASS’, ”);
define(‘DB_NAME’, ‘members’);
mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME);//Site URL + Name
define(‘SITE_URL’, ‘http://site.com’);
define(‘EMAIL_URL’, ’site.com’);
define(‘SITE_NAME’, ‘YourSite’);

//Include functions
if(!@include(“./member-functions.php”))
{
include(“../member-functions.php”);
}

//Userdata
$username = (@$_SESSION[‘username’]) ? @$_SESSION[‘username’] : @$_COOKIE[‘username’];
$password = (@$_SESSION[‘password’]) ? @$_SESSION[‘password’] : @$_COOKIE[‘password’];
$user = userdata($username, $password);

//Activation Checks
if($user[‘admin’] == ‘-1′ && !defined(‘REQUIRE_ACTIVATION’))
{
die(“Sorry, this account has not been activated. Upon registration, we sent an E-mail to {$user[’email’]} containing a link that activates your account.”);
}

//Auth Check
if(defined(‘MIN_AUTH_LEVEL’))
{
if(MIN_AUTH_LEVEL > $user[‘admin’])
{
die(“You are trying to view a page you do not have permission to view! If you think this is an error, try <a href=’”. SITE_URL .“login.php’>logging in</a>. This page may require <a href=’”. SITE_URL .“login.php?do=reg’>registration</a> in order to view.”);
}
}
?>

Yea, that’s a lot of code, but it does just what we said, so you should be able to understand it quite easily. You may be wondering about the userdata() function, no, it’s not a standard PHP function, but it’s one declared in member-functions.php. This function returns an array of the user’s details, which go into $user.

Next we’re going to look at member-functions.php, there is still no UI here, just PHP. It’s a lot of code, even in chunks. I’ve seperated it into three categories: user functions, PM functions, and just general functions. First is the user functions, these will login, logout, register, modify details. The register function needs its own explanation simply becuase it’s a little more complicated than the rest.

File: member-functions.php

 

function register($username, $password, $email, $ip, $name, $admin = 0)
{
if(empty($username) || empty($password) || empty($email) || empty($name) || strlen($password) < 6)
{
$return = ‘0|Please complete <strong>all</strong> fields and ensure your password is atleast six characters in length!’;
}
else
{
$query = mysql_query(“INSERT INTO `users` (`username`, `password`, `name`, `email`, `ip`, `admin`) VALUES (’$username’, ‘”. md5($password) .“‘, ‘$name’, ‘$email’, ‘”. $_SERVER[‘REMOTE_ADDR’] .“‘, ‘-1′)”);
if($query)
{
//last inserted ID
$id = mysql_insert_id();
//Generate MD5 hash key
$key = generate_key(6);
//Put together the key string
$key_string = “key=$key&id=$id”;
//Insert into database
$query = mysql_query(“INSERT INTO `keys` (`user_id` , `key`) VALUES (’$id’, ‘$key’)”);
//Send account activation E-mail
mail($email, ‘Account Activation at’. SITE_NAME, “Please click the following link to activate your account:\n——–\n“. SITE_URL .“activate.php?”. $key_string, “From: staff@”. EMAIL_URL);
$return = ‘1|Thank you for registering at ‘. SITE_NAME .‘! In order for your account to become active, you must verify your E-mail address. We have sent you an E-mail to ‘. $email .‘ containing a link to activate your account. Once clicked, your account will be activated.(’. $key_string .‘)’;
}
else
{
$return = ‘0|An error occured during your registration process, please retry in a minute or two as it could simply be a systems glitch.(’. mysql_error() .‘)’;
}
}
return $return;
}//Checks if a given field in the users table is taken
function is_taken($field, $value)
{
$query = mysql_query(“SELECT `id` FROM `users` WHERE `$field` = ‘$value’”);
if(mysql_num_rows($query) > 0)
{
return true;
}
else
{
return false;
}
}

//Checks user + pass, then logs in
function login($username, $password, $cookie = true)
{
if(check_login($username, $password))
{
$_SESSION[‘username’] = $username;
$_SESSION[‘password’] = $password;
if($cookie)
{
//Sets cookies
setcookie(‘username’, $username, time()+15000);
setcookie(‘password’, $password, time()+15000);
}
return true;
}
else
{
return false;
}
}

//Checks login details
function check_login($username, $password)
{
$query = mysql_query(“SELECT `id` FROM `users` WHERE `username` = ‘$username’ AND `password` = ‘$password’”) or die(mysql_error());
if(mysql_num_rows($query) > 0)
{
return true;
}
else
{
return false;
}
}

//Destroy all sessions and cookies
function logout()
{
session_destroy();
setcookie(‘username’, $username, time()-15000);
setcookie(‘password’, $password, time()-15000);
return true;
}

//Updates a users details in the database
function edit_user($id, $username, $name, $email, $admin)
{
$query = mysql_query(“UPDATE `users` SET `username` = ‘$username’, `name` = ‘$name’, `email` = ‘$email’, `admin` = ‘$admin’ WHERE `id` = ‘$id’”);
if($query)
{
return true;
}
else
{
return false;
}
}

//Remove user from database
function delete_user($id)
{
$query = mysql_query(“DELETE FROM `users` WHERE `id` = ‘$id’”);
if($query)
{
return true;
}
else
{
return false;
}
}

//Gets an array of all user data from the database
function userdata($id, $pass)
{
if(!check_login($id, $pass))
{
//Fill array with guest data
$user = array(‘id’ => ‘0′, ‘username’ => ‘Guest’, ‘password’ => ”, ‘name’ => ‘Guest’, ‘email’ => ”, ‘ip’ => $_SERVER[‘REMOTE_ADDR’], ‘admin’ => ‘0′);
}
else
{
$query = mysql_query(“SELECT `username`, `password`, `name`, `email`, `ip`, `admin`, `id` FROM `users` WHERE `username` = ‘$id’”);
$r = mysql_fetch_row($query);
$user = array(‘id’ => $r[6], ‘username’ => $r[0], ‘password’ => $r[1], ‘name’ => $r[2], ‘email’ => $r[3], ‘ip’ => $r[4], ‘admin’ => $r[5]);
}
return $user;
}

Please, read the comments to understand the code, that’s what they’re there for. You’ll see the generate_key() function later on, it basically generates a unique key for, how complex! Next up is the PM functions. This really only consists of SELECT’s, INSERT’s, and DELETE’s. Nothing to hard here, we just have to remember to maintain ownership of PM’s.

File: member-functions.php (continued)

 

//Inserts PM into database
function sendpm($fid, $tid, $title, $message)
{
if(mysql_query(“INSERT INTO `messages` (`from`, `to`, `message`, `title`) VALUES (’$fid’, ‘$tid’, ‘”. htmlspecialchars($message) .“‘, ‘”. htmlspecialchars($title) .“‘)”))
{
return true;
}
else
{
return false;
}
}//Counts number of unread PM’s
function count_unread($uid)
{
$query = mysql_query(“SELECT COUNT(*) FROM `messages` WHERE `to` = ‘$uid’ AND `read` = ‘0′”) or die(mysql_error());
$r = mysql_fetch_row($query);
return $r[0];
}

//Marks a PM as read
function mark_read($id)
{
$query = mysql_query(“UPDATE `messages` SET `read` = ‘1′ WHERE `id` = ‘$id’”);
}

//Delete’s a PM
function delete_pm($id, $uid, $password)
{
global $user;
if($user[‘password’] == $password)
{
//Make sure to check it’s theirs
$query = mysql_query(“DELETE FROM `messages` WHERE `id` = ‘$id’ AND `to` = ‘$uid’”) or die(mysql_error());
return true;
}
else
{
return false;
}
}

//Fetches a PM, checks owner, returns array
function get_pm($id)
{
global $user;
$query = mysql_query(“SELECT * FROM `messages` WHERE `id` = ‘$id’”);
$pm = mysql_fetch_assoc($query);
if($pm[‘to’] !== $user[‘username’])
{
return false;
}
else
{
return $pm;
}
}

//Get all PM’s for a user
function listpms($uid)
{
$pms = array();
$query = mysql_query(“SELECT * FROM `messages` WHERE `to` = ‘$uid’ ORDER BY `time` DESC”);
while($r = mysql_fetch_assoc($query))
{
$pms[$r[‘id’]] = array(‘id’ => $r[‘id’], ‘to’ => $uid, ‘from’ => $r[‘from’], ‘title’ => $r[‘title’], ‘message’ => $r[‘message’]);
}
return $pms;
}

Last up are a few AJAX-related functions that just done one tiny job. Very easy to understand.

File: member-functions.php (continued)

 

function edit_name($name, $id)
{
$query = mysql_query(“UPDATE `users` SET `name` = ‘$name’ WHERE `id` = ‘$id’”);
return true;
}//Edit only the E-Mail (AJAX)
function edit_email($email, $id)
{
$query = mysql_query(“UPDATE `users` SET `email` = ‘$email’ WHERE `id` = ‘$id’”);
return true;
}

//Edit only the password (AJAX)
function edit_password($password, $id)
{
//check hash
if(strlen($password) !== 32)
{
$password = md5($password);
}
$query = mysql_query(“UPDATE `users` SET `password` = ‘$password’ WHERE `id` = ‘$id’”);
if($query)
{
$_SESSION[‘password’] = $password;
$_COOKIE[‘password’] = $password;
return true;
}
else
{
return false;
}
}

Woops, almost forget our 3 utility/general/whatever functions. These are there to generate the key, list all users, and count the number of entries in a table.

File: member-functions.php

 

//Generates key x long, md5’s it
function generate_key($length)
{
return(md5(substr(str_shuffle(‘qwertyuiopasdfghjklmnbvcxz0987612345′), 0, $length)));
}//Get every user
function list_all_users()
{
$query = mysql_query(“SELECT * FROM `users`”);
$users = array();
while($r = mysql_fetch_assoc($query))
{
$users[$r[‘id’]] = $r;
}
return $users;
}

//Get a count of the entries in a given table
function count_tbl($tbl)
{
$query = mysql_query(“SELECT COUNT(*) FROM `$tbl`”);
$r = mysql_fetch_row($query);
return $r[0];
}

The last part of the backend is the AJAX handler, it receives the requests from the scripts and handles them. Should be easy to get. If you’re wondering what $_POST[’m'] is, it stands for method. Or, what are we going to do? Edit the password, update the user, delete a PM. . . We’ll call our functions created earlier to make everything easy

File: ajax.php

 

<?php
session_start();
//Load up database, functions, user info
include(‘./config.php’);if(@$_POST[‘m’] == ‘login’)
{
if(login($_POST[‘username’], md5($_POST[‘password’])))
{ //success
echo “1″;
}
else
{ //failure hmph
echo “0″;
}
}
//in place editor is GET
else if(@$_GET[‘m’] == ‘optsave’)
{
if($_GET[‘type’] == ‘email’)
{
edit_email($_POST[‘value’], $_GET[‘id’]);
}
else
{
edit_name($_POST[‘value’], $_GET[‘id’]);
}
//AJAX will update field to this value
echo $_POST[‘value’];
}
else if($_POST[‘m’] == ‘pwd’)
{
if(md5($_POST[‘current’]) == $user[‘password’])
{
if(edit_password(md5($_POST[‘new’]), $_POST[‘id’]))
{ //success
echo “Thanks, your password has been updated!”;
}
else
{ //hmm
echo “An error occured when changing your password, please try again.”;
}
}
else
{ //little shady
echo “The password you provided does not match your current password, please try again.”;
}
}
else if($_POST[‘m’] == ‘check’)
{
//Checks username/email in the DB
if(is_taken($_POST[‘type’], $_POST[‘value’]))
{ //sucess
echo “0″;
}
else
{ //failure
echo “1″;
}
}
else if($_POST[‘m’] == ‘reg’)
{
$reg = register($_POST[‘username’], $_POST[‘password’], $_POST[‘email’], $_SERVER[‘REMOTE_ADDR’], $_POST[‘name’]);
echo $reg; //Give the registration message (error/success)
}
else if($_POST[‘m’] == ‘delpm’)
{
if(delete_pm($_POST[‘id’], $_POST[‘uid’], $_POST[‘password’]))
{//sucess
echo “1″;
}
else
{ //failure
echo “0″;
}
}
else if($_POST[‘m’] == ‘deluser’)
{
if($user[‘password’] == $_POST[‘password’] && $user[‘admin’] == ‘2′)
{
if(delete_user($_POST[‘id’]))
{ //sucess
echo “1″;
}
else
{ //failure
echo “0″;
}
}
}

//Edit users
else if($_POST[‘m’] == ‘edituser’)
{
//Ensure user is admin
if($user[‘password’] == $_POST[‘password’] && $user[‘admin’] == ‘2′)
{
if(edit_user($_POST[‘id’], $_POST[‘username’], $_POST[‘name’], $_POST[‘email’], $_POST[‘auth’]))
{ //sucess
echo “1″;
}
else
{ //failure
echo “0″;
}
}
}
?>

Finally, we can start the frontend! We’re going to start with the login, and break it up into two parts. The PHP and JavaScript. First is the JavaScript, which will send out requests to login, and register.
Logging In JavaScript:

File: login.php | JavaScript code

 

function login()
{
if($F(‘password’) == ” || $F(‘username’) == ”)
{
alert(“Please fill in all fields before submitting.”);
return;
}
//AJAX request options
var opt = {
method:‘post’,
postBody:‘m=login&username=’ + $F(‘username’) + ‘&password=’ + $F(‘password’),
onSuccess: function(t) { /* check on success */ checkLogin(t); },
onLoading: function() { /* Disable login button with message */$(‘login_button’).value = ‘Authencating’; $(‘login_button’).disabled = true; }
}
//Send request
new Ajax.Request(‘ajax.php’, opt);
}

function checkLogin(t)
{ //t object is automatically passed
if(t.responseText == “1″)
{
//Show main screen
window.location = ‘index.php’;
}
else
{
//Give a error message, reenable login button
$(‘response’).innerHTML = ‘<strong>Authencation Failed!</strong>’;
Element.show(‘response’);
$(‘login_button’).value = ‘Login’;
$(‘login_button’).disabled = false;
}
}

We’re sending the request to our AJAX handler, and having the function checkLogin() handle the results of that. Either show us the main page, or give an error. The next part is a little more complicated, because we do the same thing, and add something that goes and checks if a username/email is already existant. If it is, the box will be highlighted red!

 

function check_value(var_n, field_n)
{
var opt = {
method:‘post’,
postBody:‘m=check&type=’ + var_n + ‘&value=’ + $F(field_n),
onSuccess: function (t) { update_check(t, field_n); },
onLoading: function() {}
}
new Ajax.Request(‘ajax.php’, opt);
}

function update_check(t, field_n)
{
if(t.responseText == “0″)
{
//The name already exists, show message
$(field_n + ‘_check’).innerHTML = ‘<img src=”./images/error-small.png” /> That already exists!’;
$(field_n).style.border = ‘1px red solid’;
$(field_n).focus();
}
else
{
//Good to go!
$(field_n + ‘_check’).innerHTML = ”;
$(field_n).style.border = ‘1px green solid’;
}
}
function register()
{
//Basic error checking
if($F(‘email’) == ” || $F(‘name’) == ” || $F(‘confirm_password’) == ” || $F(‘password’) == ” || $F(‘username’) == ”)
{
alert(“Please fill in all fields.”);
return;
}
else if($F(‘password’) !== $F(‘confirm_password’))
{
alert(“Please ensure your passwords match!”);
$F(‘confirm_password’).focus();
}
else
{
//Construct the AJAX object, add all form fields in the register_form to the paramaters
var opt = {
method:‘post’,
postBody:‘m=reg&’ + Form.serialize(‘register_form’),
onSuccess: function(t) { $(‘register_button’).value = “register”; $(‘register_button’).disabled = false; handle_register(t); },
onLoading: function<