MySQL stored procedure’s come in really handy at times. I like to let the database do the work for me sometimes.

For example, in an e-commerce system you may need to calculate a tax. For our example today though, we will use the tables we made in my previous article on the XREF table. We are going to focus this tutorial on getting the appropriate tables in place to preform our history logging.

To start, we are going to create a module’s table so that our history table can be one table instead of many and we are going to create a users table. We are going to keep record of parent and child relationships of the deleted record. So we will add two more tables: history_children and history_parents along w/ our history table.

Let’s create our modules table:

CREATE TABLE `test_db`.`modules` ( `id` INT NOT NULL AUTO_INCREMENT , `code` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `description` VARCHAR(128) NULL , `history_data_description` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) , UNIQUE INDEX `code_UNIQUE` (`code` ASC) , UNIQUE INDEX `name_UNIQUE` (`name` ASC) ) ENGINE = InnoDB;

Notice the data_description field. We will use this for our history table, to know what data we are keeping in our history table.

Let’s create the users tables:

CREATE TABLE `test_db`.`users` ( `id` INT NOT NULL AUTO_INCREMENT , `login` VARCHAR(24) NOT NULL , `first_name` VARCHAR(24) NOT NULL , `last_name` VARCHAR(24) NOT NULL , `password` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) , UNIQUE INDEX `login_UNIQUE` (`login` ASC) ) ENGINE = InnoDB;

Now let’s create the rest of the tables:

CREATE TABLE `test_db`.`history` ( `id` INT NOT NULL AUTO_INCREMENT , `module_id` INT NOT NULL , `old_id` INT NOT NULL , `data`...

The XREF, or cross reference table, is a database table that links records together. These tables are very good for normalization in your database. I almost always use an xref when I need an many to many relationship.

Usually XREF tables have only two columns with no Primary Key. That’s right, the two columns together make them unique. They are both Foreign Keys to other tables. A real life example of this would be in any standard CRM. Let’s go over this example right now.

Many CRM’s could have a table to store notes, and a table to store accounts. If you have one user managing many accounts, they may need to put the same note for all accounts they are managing. To connect these notes to the accounts we will use an xref table.

Create a schema called ‘test_db’. Now create our accounts and notes table:

CREATE TABLE `test_db`.`accounts` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `phone` VARCHAR(24) NULL , `fax` VARCHAR(24) NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) ) ENGINE = InnoDB; CREATE TABLE `test_db`.`notes` ( `id` INT NOT NULL AUTO_INCREMENT , `subject` VARCHAR(45) NOT NULL , `body` VARCHAR(256) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) ) ENGINE = InnoDB;

These tables should be created first, so that the ID’s in them actually exist when we create the foreign keys in our XREF table. We are going to name the XREF table account_notes_xref. This is preference, but really good practice, as it is a single account’s note’s. The notes will be displayed on the account so the account owns the notes.

You could also get away with account_notes, but for sake of the title, we will add xref to it. This also makes things easier to spot when looking through the database. So let’s make the table:

CREATE TABLE `test_db`.`account_notes_xref` ( `account_id` INT NOT NULL , `note_id` INT NOT NULL , PRIMARY KEY (`account_id`, `note_id`) , INDEX `acx_account_id` (`account_id` ASC) , INDEX `acx_note_id` (`note_id` ASC) , CONSTRAINT `acx_account_id` FOREIGN KEY (`account_id` ) REFERENCES `test_db`.`accounts` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `acx_note_id`...

In Part I of this series, we started the process of creating user polls for a business web site. Part I gave the layout of the data layer and began the construction of the class file. 

In this part we will continue with adding methods to the class file that will enable the administrator to set the variable values and read from the database tables.

The setPollId($iPollId) method sets the value for the _iPollId variable.  Since the constructor method calls the setPollId($iPollId) method, we must define that method as well:

function setPollId($iPollId) { // set the _iPollId variable for the class if (is_int($iPollId)) { $this->_iPollId = $iPollId; } }

The setAnswerId($iAnswerId) method sets the value for the _iAnswerId variable.

function setAnswerId($iAnswerId) { // set the _iAnswerId variable for the class if (is_int($iAnswerId)) { $this->_iAnswerId = $iAnswerId; } }

The class will also contain several methods for reading from and writing to the two database tables.

The getPollsCount method returns the number of polls in the poll table.

function getPollsCount($iStatus=false) { // get polls count for paging // set sql filter $iStatus ? $sFilter .= " AND status=1" : $sFilter .= ""; $sql = "SELECT count(poll_id) AS poll_cnt FROM devdrive_polls WHERE deleted=0".$sFilter; if (DB::isError($iCnt = $this->_oConn->getOne($sql))) { catchExc($iCnt->getMessage()); return false; } return $iCnt; }

The getPolls method returns all the poll data from the poll table into an associative array and sorts it by the sort key.

function getPolls($sSort, $iPage=0) {// get polls list $sql = "SELECT poll_id, poll_vote_cnt, poll_question, status, created_dt, modified_dt FROM devdrive_polls...

User surveys can be an extremely effective marketing tool for any business web site. Visitors can make their voices heard on what products, services and information they would like the site to provide. Site owners can then adjust their marketing plans and promotions around the results of these polls.

In order to build a basic user poll, we must first create the database tables that will hold the poll question and its various responses.

Create the MySQL Polls Table

First, we must build a database table to hold the information on each specific poll. The table will contain:

a primary key ID number, the number of total votes in the poll, the poll question, the poll’s status (active/inactive), whether the poll was deleted, the date/time a poll was created, the date/time a poll was modified, and the date/time a poll was deleted.

Now, run the following MySQL script to create the polls table:

CREATE TABLE devdrive_polls ( poll_id int(10) NOT NULL auto_increment, poll_vote_cnt int(10) NOT NULL default '0', poll_question text NOT NULL, status int(1) NOT NULL default '1', deleted int(1) NOT NULL default '0', deleted_dt datetime NULL default NULL, created_dt datetime NOT NULL default '0000-00-00 00:00:00', modified_dt datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (poll_id) ) TYPE=MyISAM;

Create the MySQL Poll Answers Table

You must also build a table to hold the answers for each poll. Most user polls offer between two and five answers. The choices can vary from a simple “yes/no” to a variety of flavors, colors or genres from which users can select.

The poll answers table contains:

a primary key ID, a foreign key ID (tied to the poll table primary key), the poll answer, and the number of total votes for that answer

The following MySQL script should be run to create the poll answers table:

CREATE TABLE devdrive_polls_answers ( poll_answer_id int(10) NOT NULL auto_increment, poll_id int(10) NOT NULL default '0', poll_answer text NOT NULL, poll_answer_cnt int(10) NOT NULL default '0', PRIMARY KEY (poll_answer_id), KEY poll_id_rel (poll_id) ) TYPE=MyISAM;

Create the PHP Polls Class

The class.polls.php file will act as the business layer between the information in the database tables and the results displayed to the presentation layer.

The use of the class will also reduce much of the traffic between the displayed web page and the database, which will allow the application to run faster.

The class will contain three variables:

the poll ID, the poll answer ID, and the database connection string.

Here is the sample code to build the class and instantiate the variables:

<?php require_once("DB.php"); //import the PEAR::DB class class polls { // open the class definition var $_iPollId; // unique...

You’re may be wondering what exactly CRUD is.

CRUD simply stands for Create, Read, Update and Delete and it is the one of the fundamental principles of programming logic that can be expanded and applied to larger projects.

For example, let’s imagine we’re creating a social network and we like to have the ability for users to create accounts, edit and update information for those accounts and also delete said accounts; that is CRUD at work.

Now let’s get started using PHP & MySQL, to create an app that can store video game titles.

Create the Database

First let’s create a database named mygames to store our data. I’ll be using phpMyAdmin to take care of that but you can use any MySQL database management interface.

Refrain from using spaces or special characters when making names, also to ensure multi-language support change the database collation to utf-8_unicode_ci (case-insensitive).

Now that we have created the database we need to create a table that will have all the necessary fields to input and store our data. We’ll name the table videogames and give this table 7 fields.

The first field will be called id, this will be our unique identifier and will help us know which video game we’re editing, adding or deleting. The type is int(11), which basically means an integer with a length of 11 digits. The last thing we have to do for our id field is to make sure that Auto Increment (A_I) is checked, this function is self-explanatory.

Our second field is called title type will be varchar(255) meaning a string that won’t be longer than 255 characters. Also set the collation of this field to utf-8_unicode_ci.

The next field will be release_date, the only thing we’ll do for this field is set the type to date.

The fourth field, publisher will be a varchar(50) and the collation will be utf- 8_unicode_ci.

Field number five will be called system and this too will be varchar(50) and collation will be utf-8_unicode_ci.

The next field is rating where the type is int(1) and the default value will be 0.

Our final field is called num_players and the type will be int(2) with a default value of 1.

Connect to our Database

Our database is ready to properly store and sort information so just add some entries to your videogames table to give it some data to work with.

Next we need to create a folder for our project so in your local/remote server folder create a folder called mygames. In this folder make another folder called includes, and using the text editor of your choice create a blank PHP file in this folder and name it db.php.

Using this db.php file we will establish a connection between our PHP code and the database we just created. There are a couple of ways we can do this but the most easiest and reusable method in my mind is using a PDO (PHP Data Object). Using this method we can use the same file over and over again, needing only to change a couple things.

This is going to be the structure of our db.php file:

<?php $dbinfo = 'mysql:dbname=mygames;host=localhost'; $user = 'root'; $pass = 'root'; //If you need to change database information, just change values above. $db = new PDO($dbinfo, $user, $pass); $db->exec('SET CHARACTER SET utf8');

The first 3 lines are variables that store our database information (username, password etc.). These values are user specific, so they will change in your case whether you’re using XAMP, WAMP, MAMP or such the like.

The fourth variable $db is our connection to the database; our code will be using this variable to do everything in our code. The last line ensures that we can support all languages by setting the character set to utf8. Now we can go to our index.php file and start adding some code that will communicate with the database.

This is our PHP code block for the index.php page:

<?php require_once 'includes/filter-wrapper.php'; require_once 'includes/db.php'; $sql = $db->query('SELECT id, title, release_date, publisher, rating, system, num_players FROM videogames ORDER BY title ASC'); $results = $sql->fetchAll(PDO::FETCH_OBJ); ?>

The first line includes a filter wrapper, which I’ll explain more of what it does in part 2 of this tutorial. Depending on what version of PHP you have running on your server you may or may not need this but let’s keep it in there for now.

The second line includes our db.php file that we created earlier, so now on our index page is now connected with our database.

The third line is our query that we make to the database; we’re selecting all the entries in the database by their respective fields and sorting them by their title in alphabetical order.

Now that we have them selected we need to get them out of the database and store it somehow so we can display it on our index page.

Our last line of code takes care of that. Using the variable $results, we fetch all the entries from the database and they are stored in that variable. Think of the results variable receiving a box with all the entries from our database in there. The entries are stored in an array, which gives us the freedom to just display one by its unique identifier (id) or to just display them all.

In part two we’ll create the html needed to display our data and we’ll tackle editing, adding and deleting entries all within our app.

...

There are times when we are so anxious to jump into creating something new that we forget to cover the basics. For web developers it is important, actually vital, to have the ability to test your projects locally before they go live.

While much of the web relies on a Linux, Apache, MySQL, PHP stack, or LAMP, to power applications and dynamic websites, not everyone codes in a Linux environment.

Those who prefer Windows can still replicate how their code will work by using WAMP to replicate a live web environment right on their own computer with the only difference being substituting the Linux operating system in the stack for Windows.

Obtain a copy of WAMP

Before you do anything you need to download WAMP from http://www.wampserver.com/en/download.php. You will have the  option to choose wither a 32 or 64-bit environment depending upon your computer’s configuration. In addition to Apache, PHP and MySQL this download includes phpMyAdmin and SQLBuddy to better help you manage your databases through web based GUI rather than having to use a terminal interface.

Install the software

Once you have downloaded and uncompressed your WAMP executable you will need to go ahead and launch it to start the installation process.

When you click next you will be asked to accept the license agreement. Since it is a GPL license you are free to do just about anything with it so you can go ahead and accept.

The next step requires you to select the folder where you would like to install your WAMP server. The default will be c:\wamp however you can change this to install the server into any directory or partition you choose.

Chose where to install the software

After you choose your directory you will have the option to setup icons. Once you decide on this click Next and then confirm the installation settings again by clicking Install.

Once the installation runs its course you will be asked to choose your default browser. Internet Explorer is the default choice but you can navigate your way to any other browser of your choosing.

NB – if your Windows firewall pops up at this point make sure to grant Apache access.

The next decision you will have to make is to set the PHP mail parameters. Many people leave this set to the defaults when setting up a testing server on their local computer. If you wish to configure it to connect to your SMTP server you may do so here but unless you plan on testing email capabilities the default entries can be left and all you need to do is click Next.

Setting the SMTP server (optional)

Congratulations, WAMP is now installed on your local computer.

Testing the installation

Now that everything has been installed let’s test everything out.

Using one of the icons you created, or Start –> All Programs –> WampServer –> start WampServer, you can launch the management console. Once opened, it will appear in the lower right hand corner of your screen.

The WAMP management console

If WAMP is not started go ahead and click Start All Services. If you are not sure whether or not WAMP is running, look for the small green W icon in your toolbar. If it is red, WAMP services are stopped, green means everything is running while orange means some services are running.

Now we want to test to see if everything was installed correctly. In the WAMP management console, click on Localhost. If you see the following screen pop up in your browser then everything is working!

WAMP services are running

Configuring phpMyAdmin

The last step is to set a password for phpMyAdmin. Many people forgo this step because the server is not going to be live so they don’t see the need to protect the database if it is only sample data. You can decide what practice to follow.

In order to change the password you will need to navigate to your phpMyAdmin file, located at C:/wamp/apps/phpmyadmin3.x.x and open the config.inc.php file for editing.

Once opened, navigate to the lines that read,

$cfg['Servers'][$i]['user'] = 'root'; $cfg['Servers'][$i]['password'] = 'xxxxx'; $cfg['Servers'][$i]['AllowNoPassword'] = true;

Where the example reads xxxxx insert your password. Under AllowNoPassword, change the value to false.

What do I do next?

Now that everything is installed and working you can upload test websites to your root or even install third-party web applications like WordPress, Moodle, Drupal, etc. to this location. You now have a sandbox in which to work on your development projects! Have fun with it.

...