MySQL Point

Normalization is almost universally applied to relational databases such as MySQL in order to optimize tables for general-purpose querying and to rid them of certain undesirable characteristics that could lead to a loss of data integrity. Doing so tends to promote better accuracy of queries, but it also leads to queries that take a little more work to develop, as the data may be spread amongst several tables. In today’s article, we’ll learn how to fetch data from multiple tables by using joins.

Two Syntaxes

There are two accepted syntax styles for writing joins: ANSI-style joins and theta-style joins. ANSI syntax uses the JOIN and ON keywords, as in the following example:

The JOIN keyword is used to separate the names of the tables being joined, and the ON clause contains the relation showing which column is being used as the join key.
(more…)

Logging database transactions is relatively easy within PostgeSQL and MySQL. Options can be set within the configuration files (postgresql.conf for PostgreSQL and my.ini for MySQL) to enable and record database INSERTs, UPDATEs and DELETEs. But if a client requires to view this information on a regular basis for auditing purposes, without the need for a database administrator, a different method is needed to record individual field changes. In this article we will look at the use of triggers for recording these transactions.

To store the auditing transaction a table will be defined, which hold information relating to the table and field that triggered the event, the old value stored in the field before the triggered event, the new value that will be stored in the field after the triggered event, the user that made the change, and the date/time of the event. An example of the audit table definition follows. Note the differences with the syntax regarding auto-incrementing values for the primary key and declaring the primary key constraint itself.

(more…)

Introduction

A MySQL database abstraction class, which makes database handling easier and object-oriented.

The class:

[code lang="php"]

class DBI
{
var $server;
var $user;
var $pass;
var $db;
var $link;
var $result = null;

/*
** The constrcutor, terminates the script if the connection fails
** @params:
** string $server - the server IP or hostname
** string $user - the username
** string $pass - the password
** string $db - the name of the database to use
*/

function DBI($server,$user,$pass,$db)
{
$this->server   =  $server;
$this->user  =    $user;
$this->pass  =    $pass;
$this->db      =  $db;
if(!$this->connect())
{
die("<div style=\"text-align:center;border:#a00 solid 1px;padding:4px 1px;margin:0 3px 5px 3px;background:#fdd;color:#a00;font-size:12px;font-family:georgia;letter-spacing:1px;width:90%;\">Server is not reachable ($user@$server)</div>");
}
}

(more...)