database | Web development blog, news and tutorials

Home > Tags > database

Page 1

Automate WordPress Installation and Development WorkFlow

Tools WordPress Aug 30, 2012 2 comments

Do you work on multiple sites at a time? Then no doubt you would love to improve your speed and your overall development workflow. Think of what it takes to set up WordPress the old school way:

Downloading the latest version. Extracting the downloaded zip file. Creating a new database. Creating wp-config.php file.

Developing WordPress sites has never been easier with WAMP and Simple WordPress Auto Installer. The Auto Installer script is now available that allows you to add a WordPress install within the WampServer together with a database, all in one shot, thereby speeding up your local WordPress development.

To make this happen, you will need to download WampServer and the Simple WordPress Auto Installer, both of which are open source and available as free downloads. You will need to install the WampServer first and ensure that it is working. This is usually straight-forward and quick.

Upon downloading the Simple WordPress Auto Installer, you will notice 3 files: installer.php, license and readme.md. To install, simply drop a copy of the “installer.php” file into the root directory of WampServer designated as “www”.

When you open the “installer.php” file, you will see these lines of code:

array( 'label' => 'Latest version', 'url' => 'http://wordpress.org/latest.zip', 'description' => 'Download the latest stable English version' ), array( 'label' => 'Last nightly', 'url' => 'http://wordpress.org/nightly-builds/wordpress-latest.zip', 'description' => 'Download the latest beta version'

You can configure these settings to change the WordPress version as well as the build to your own preferences.

You can also add more languages by including an array like the one below:

array( 'label' => 'French', 'url' => 'http://fr.wordpress.org/wordpress-'.$last_v_doted.'-fr_FR.zip', 'description' => 'Download the latest stable French version' ),

Other global settings you can configure include the default username, database, password and CSS parameters.

Fire up your WampServer and navigate to the installer file we just uploaded using the link below:

http://localhost/installer.php

The Auto Installer will then allow you to configure your local site settings that include; the folder where you want WordPress to go, the database server (which by default should be localhost), the DB name, DB username and DB user password. From here click `GO` and

the script will then download the latest copy of WordPress, extract the files to the directory you named and will create a new database.

Set your site’s name, username and password and you are ready to roll.

Whenever you need to start a new development project, you will simply fire up the installer using the above link and configure the settings for the new site as we have done. You will no longer need to download WordPress again, set up a database in phpMyAdmin or make any other configuration settings. Everything has been done for you.

...

Cali says: Even easier : install the WAMP package EasyPHP (www.easyphp.org) and install le module 'Wordpress' (www.easyphp.org/modules.php)....

Create your own CRUD app with PHP & MySQL (Part 2)

PHP Jun 28, 2012 8 comments

Picking up from where we left off, we need to start by creating some HTML to display the data we’ve stored in the $results variable. You can always modify this to suite your project. I’ll be using a table structure.

<!DOCTYPE html> <html> <head> <meta charset="utf-8"/> <link href="styles/theme.css" rel="stylesheet"/> <title>PHP & MySQL</title>\ </head> <body> <div id="wrapper"> <table> <thead> <th>Title</th> <th>Release Date</th> <th>Publisher</th> <th>System</th> <th>Rating</th> <th>Number Of Players</th> <th>Tools</th> </thead> <tbody> <?php foreach($results as $entry): ?> <tr> <td><?php echo $entry->title; ?></td> <td><?php echo $entry->release_date; ?></td> <td><?php echo $entry->publisher; ?></td> <td><?php...

Marty Lavender says: Your code had a lot of errors. All of which I have been able to fix on my own. I cannot however figure out how to resolve this...

MySQL – Deletion History Part 1

MySQL Jan 10, 2012 Add comment

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 table for MySQL

MySQL Dec 19, 2011 4 comments

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`...

Disqus_eric says: "I almost always use an xref when I need an many to many relationship" This implies you can do a many-to-many w/out an xref. ...

Hot deals

See all deals →

Create an HTML5 Browser Game from Scratch - only $19!

WordPress Shortcodes Plugin + 3 Premium WP Themes - only $19!

Beautiful Solomon Font Family (12 fonts) - only $9!

Mighty Mac Bundle - 5 Incredible Mac Apps for only $12!

PHP User Survey: Setting Variable Values and Reading from Tables

PHP Nov 29, 2011 Add comment

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...

Create Your Own CRUD App with MySQL and PHP

MySQL PHP Nov 1, 2011 5 comments

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.

...

Rodrigo de Almeida Rodriguez says: Hi! great tutorial! See http://crudin.smarc.com.br/en It´s a framework that generates in real time a complete CRUD system All...

Common Mistakes to Avoid When Coding in PHP

PHP Oct 19, 2011 15 comments

Despite the high expectations placed on them at times, developers are human. They were the last time we checked anyways.

As humans, we are bound to make mistakes from time to time. And simple, common mistakes often slip past our filters the more comfortable we become with something.

Think about it, when you first started writing code you most likely checked every line to make sure things were perfect. As you grow more comfortable with the process, little things often get overlooked and mistakes are made.

But knowing what these common mistakes are and how to avoid them can really help speed up the development process and keep our clients smiling.

Below you will see some of the more common mistakes that are made with PHP, even by advanced developers…

Poor Housekeeping

People can get lazy and code can get messy.

To keep it organized you can use things like comments and indents. I know, these are basic best practices but think to yourself, when was the last time you hacked up your code without commenting?

I thought so.

How about breaking your code into modules based on function? Many agree that as a rule of thumb your function should not exceed one page on your screen unless it is necessary.

Another good housekeeping practice is to backup all of your files before you upload changes. Sure you may be in a hurry, but the time it takes to make a quick backup is far less than having to go back and undo a disaster.

Forgetting Your Punctuation

One of the best things about PHP is that you don’t need expensive software to write code in. Any text editor will do.

Unfortunately, a basic text editor won’t tell you if something isn’t right.

One of the most common, and basic, mistakes made when coding in PHP is to either forget or misplace a quote, brace or semi-colon causing a syntax error. Before you try to run anything, make sure that every:

[ has a ] ( has a ) { has a }

Now check to make sure that all string keys are enclosed with matching quotes. Remember, “ does not match with ‘.

While you are at it, double check the semi-colons to make sure they aren’t missing or misplaced.

Forgetting to Validate Input

By now you should know that user provided data cannot be trusted. Allowing this from your users is one way that cross-site scripting, buffer overflows and injection flaws can all be used to attack your site. Unfortunately, it is also one of the most common mistakes people make when coding in PHP.

In the following lines of code, notice that the three variables are not validated:

$birthdate = $_GET['birthdate']; <br> $birthmonth = $_GET['birthmonth']; <br> $birthyear = $_GET['birthyear']; <br>

By adding the following lines of code we use preg_match to perform a regular expression match against the input. In our birthdate and birthmonth variables it is checking to verify that a one or two digit number between zero and nine was entered. For birthyear, it needs to be a four digit number between zero and nine:

if (!preg_match("/^[0-9]{1,2}$/", $birthdate)) die("That is not a valid date, please check that again."); <br> if (!preg_match("/^[0-9]{1,2}$/", $birthmonth)) die("That is not a valid month, please check that again."); <br> if (!preg_match("/^[0-9]{4}$/", $birthyear)) die("That is not a valid year, please check that again."); <br>

We are able to make sure that the proper type of characters are input by the users are actually numerals and only numerals that we expect to be entered. Anything else results in an error being thrown back to the user.

So I call on our readers to share with us some of the most simple mistakes we have made over the years. And don’t worry, we’re all human here.

...