Tag Archives: sql

Database Management System for Online Ticket Sales

Database Management System (DBMS) of online ticket sale for the stage play “Kusa-Paba” at the University of Calgary Theater.

Abstract and Project Description

The University of Calgary Theater has 500 available seats. This particular DBMS is built to allow users to order and purchase tickets online. Tickets sold by agents can also be entered to the system using the agents’ interface. Only a designated set of seats are available for sale. Some are as for reserved guests and others are designated as House Seats hence they are not available for sale. The designated seats for sale are categorized for different price levels; $50.00, $30.00 and $20.00 (Figure 1). The price levels are further broken down for adults and children.

Continue reading Database Management System for Online Ticket Sales

How to fix WP Error Establishing a Database Connection

The Error Establishing a Database Connection error is a very common issue on WordPress. This issue is caused by a connection failure between the WordPress CMS and the SQL database. Here is a simple guide to fix the problem.

A typical error message displayed on site.
A typical error message displayed on site.

Finding the cause

This can be caused by several issues. If you are getting the same error, “Error Establishing a Database Connection”, on the main front page as well as the wp-admin section, then the problem is most likely the credentials. This is the most common reason for this issue. However, if the error message on the wp-admin section is different from that of the front page, then it is most likely caused by a database corruption. I will explain several methods to fix the problem.

Fix wp-config file

From the back-end of your server, open the wp-config.php file in an editor. If you are using cPanel, then you can do this from file manager. If you have your own server, you can access the file either using FTP or remote desktop connection. Check if the following parameters are setup properly in wp-config.php.

define('DB_NAME', 'database_name');
define('DB_USER', 'database_username');
define('DB_PASSWORD', 'database_password');
define('DB_HOST', 'localhost');

The information in wp-config.php file should match the corresponding current database user account. These accounts should be listed under under MySQL Databases section of your server. On cPanel it is located under Databases sub section.

MySQL in cPanel
MySQL in cPanel

Typically the database name is composed of your username_databaseName. All the current databases and users will be listed within the MySQL admin page (example shown below).

List of current databases and users.
List of current databases and users.

If the exact same username appears in the MySQL current list as it is in the wp-config, then it may be the password. You cannot check what password is used for each user account. But you can change the current password. To do that, under MySQL Users –> Add New User, enter the current username, Then enter a password you prefer. This will update the existing account.

Updating the password for a user.
Updating the password for a user.

Fix corrupted database

As mention above, if the error message on the wp-admin section is different from “Error Establishing a Database Connection”, then it is caused by a database corruption. WordPress comes with a built in repair method in repair.php. First add the following to the top of wp-config.php file.

define('WP_ALLOW_REPAIR', true);

Then run the following file located at, http://domain.com/wp-admin/maint/repair.php. It will provide you with “Repair Database” and “Repair and Optimize Database” options. Choose either one to fix the issue. Once the repair is successful (or not), please delete the WP_ALLOW_REPAIR from the wp-config.php file. With the code in place, anyone one access the repair page without administrative account.

Connection to database

This can be tested by running a simple PHP script. Create a PHP file with the following code such as test.php.

<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

If it provide a feedback with a successful connection, then your database is working. If not, either the database is corrupted or the sever files are damaged. Contact your hosting company for help. If it is your own server, then your best hope is to replace the MySQL files with available backup.

Installing web applications

There are two major platforms used commercially for web development; Microsoft Windows Server and Linux based OS versions such as Red Hat Enterprise. You could also get a fully operational free version of Linux Operating Systems for personal or small business use. Out of the two main platforms, the most popular one among large companies is Microsoft Windows Server. I will demonstrate how to install a Web Application using the Microsoft Web Platform Installer (version 4.6 to be exact).

Web Applications

Web Applications, Scripts or web modules are programs that can be installed on a server to manage web based media. It can be databases such as SQL, or server side languages such as PHP, or web Content Management Systems (CMS) such as WordPress and Drupal. Some of these programs depend on others. Drupal and WordPress depend on PHP database so a server must have the PHP module installed to support them.

Microsoft Web Platform Installer (Web PI)

Web PI is a free software package (yep… it is legally free) developed and distributed by Microsoft. You can download it here. In includes a large repository of free web applications for web development and deployment. To install it, you need a Windows based operating system. It could be a server type OS or a regular desktop OS like Windows XP, 7, 8, etc. Check their website for supported systems.

I will use the Web PI 4.6 installed on Windows Server 2012 Datacenter OS for my examples. But the process is the same/similar regardless of the Operating System. Please note that this article explains the Server Administer side installation process. The client side installation process, which you usually get through a web hosting company, has a completely different installation process (on shared hosting).

Installing WordPress Web App

WordPress is the most popular platform for blogging for awhile. Recently full-blown websites have been built on it. The WordPress platform became popular because it has an easy to use user interface and a streamline update system. Naturally, I will use the WordPress Web Application as an example. But you can install any other program or application using Web PI following the same steps.

1) After installing Web PI, select “Products” tab from the top. Install “ISS XX.XX Express” and “ISS Recommended Configuration”. You may have already installed these on your server during the initial setup of the system.

MS Web PI Products Tab. Make sure the key components are installed.
MS Web PI Products Tab. Make sure the key components are installed-Click to enlarge.

If the PHP module is not installed, you will be prompt to install during the install of applications that depends on it. Since we are going to install WordPress, you may also install the PHP module now.

2) Select “Applications” tab and “Add” all the Web Apps you would like to install. You may install more than one at a time. After adding, press “Install”.

MS Web PI Applications Tab. Add for installation.
MS Web PI Applications Tab. Add for installation-Click to enlarge.

In this particular example, I will be installing one application; WordPress for Windows. Other options include variety of CMS such as Drupal, Joomla, Orchard, .NET, etc and web programs such as Moodle, Gallery Server Pro, etc.

3) The PREREQUISITES Window will check for all dependencies. If a dependency such as SQL or PHP is not installed, this will add those to the installation process. Accept for installation. Depend on the speed of your computer components, it will be installed in few seconds to a few minus period.

Prerequisites for WordPress Web Application.
Prerequisites for WordPress Web Application-Click to enlarge.

Application installation process.
Application installation process-Click to enlarge.

4) Once installation is completed, you will be greeted with the CONFIGURE Window. Select the location of install in your system from “Web Site” drop-down. The “application name” will determine the folder in which the installation will be done. In this case I choose “wp” as my application name. Hence the final access will be at http://localhost:80/wp and you may change the root folder of the site later. Keep reading this article for more information on how to do that.

WordPress installation server location setup.
WordPress installation server location setup-Click to enlarge.

5) The next CONFIGURE Window has a form which user should edit. Do not panic! All those “Keys” can be confusing. Just enter either a random set of numbers and letter or something meaningful to you. It does not matter what you enter. I usually use the exact same set of characters for all of the options. Press “Continue”.

Web Application Configuration Window. Type several characters for each option (key).
Web Application Configuration Window. Type several characters for each option (key)-Click to enlarge.

6) Record all the “Password Settings” information under the FINISH Window. You may “Copy to clipboard” and paste it somewhere for saving as well. I usually save in a Text file.

Password Settings for WordPress app Install.
Password Settings for WordPress app Install-Click to enlarge.

Now you may visit the site by selecting “Launch WordPress” or you may simply type the location address in Step 4 on a Internet window. Close the windows by pressing “Finish”. The first thing you will see on the Internet window is a setup page. Each Web App has its’ own setup page. In this particular example, it will be a WordPress CMS setup page. This is the final step!

WordPress CMS setup page.
WordPress CMS setup page-Click to enlarge.

Additional options

We setup the Web App in a folder named “wp” in Step 4. But let’s say your client insists that their website should not have “wp” as prefix (http://example.com/wp). You do not have to reinstall everything. You do not have to relocate the folders on the server either. What you need to do is to go to the domain settings and make “wp” your root folder. Now the http://example.com/wp will be http://example.com/. Another way to do this is through domain registrar options. You can point the domain name to wp folder from there as a root.

As mention before, you may install any of the Web Applications on a single server. They very lightweight programs, that they will not effect the performance of the server (but the traffic flow will). I highly recommend using different folders for each Web Application. This will avoid conflicts between them.

By default, all web applications take Port 80 (HTTP port) over others. However, you may also change this to whatever the port you want. You may ask, why would you want to use non-standard port? One reason would be that you are a developer or a student. If your ISP blocked access to Port 80, then you can bypass this by using a different port and utilizing port forwarding.

Export and import SQL databases

Migrating SQL/MySQL databases from one location to another is not something most of you will do. There are circumstances where migrating a database from the administration side of a server is easier than trying to manually copy data. If you change your web host (or server) after creating databases in the current location or if you wanted to back up your database files manually, then it is essential to know the fundamentals of migration.

Skip Tech Jargon

Background

SQL/MySQL databases are like MS Excel spreadsheets or MS Access files. The only difference is these databases run on a web (or internal) server as oppose to running on a local computer. Fundamentally they are similar in operation, but differ in how they process and store data. The MS Excel is one of many low level database program. The MS Access is step up from Excel with much more advanced functions. The SQL/MySQL databases are much more advanced and extremely flexible than the previous products.

The flexibility of SQL/MySQL can be summarized…

  • They will run most (as far as I know, all) operating systems.
  • The deployment and distribution management is very flexible.
  • They have the ability to run several databases while communicating between them.
  • Easy universal database migration options (no proprietary files).

Migration

For this example, I used phpMyAdmin program installed on a Linux server. This particular example involves in transferring databases of mTouch WP plugin data from one location to another. You can use the exact same steps to migrate SQL/MySQL databases in phpMyAdmin. If you you a different program for database management, it should be similar to phpMyAdmin, but be careful of how you export it.

This is for advanced users ONLY. A mistake can take down your entire website. Use the guide at your own discretion.

1. Make sure your program/plugin/add-on is upto date.

2. Log into your server and then phpMyAdmin.

3. Choose the database location from the left pane of the phpMyAdmin window.

Choose the database location from left pane.
Choose the database location from left pane.

3. Select the database related to your program. It is better if you export and import all databases used by the program. Then go to Export.

Select specific files for export.
Select specific files for export.

4. Choose the export settings appropriately. As long as you will be using the same phpMyAdmin at the new location, it should not matter which format you save export the files.

Choose the configuration for import.
Choose the configuration for import.

5. Log into the new server/location and install the save exact software. It is highly recommended using the exact same software version as the one you are migrating from.

6. Long into the phpMyAdmin (new location) and find the location of the databases on the left pane of phpMyAdmin window.

7. Backup the new databases created by the program by exporting them (same as above). Then upload and install the old files using import pane.

8. Validate the install by going into the front end. If you have done everything right, you should be able to now have the database up and running.

It is as simple as that. Backups are very important so never ever skip backups before and during modifications.

Customizing the editor for coding

A lot of people hate software just because they do not like the look and feel of the user interface. Surprisingly even the knowledgeable “geeks” have strong preferences on the GUI (Graphical User Interface). This often takes away the focus from functionality and versatility of programs and impact negatively on high quality products.

The good news is that most programs have customizing options available for both GUIs and back-end. However, very few users are aware of these hidden gems. In this article we will discuss the customizability of the open source free software Notepad++ (“Notepad Plus Plus”).

Notepad++

Notepad++ is highly customizable and flexible enough to work with almost all programming languages. As soon as you save your file as a .php, .html, .py, .cpp, etc it will pick up all the standard syntax related to each language. In addition you can use this program as a standard text editor.

The initial setup is simple. Download the program from here and follow the on screen instructions.

Select "Themes" during install.
Select Themes during install.
Make sure you select themes (check mark it). If you do not have much experience with the program, I would recommend you select exactly what I have above. I do not have an image of the next window, but if you are unsure, do not select anything on the next window. Just move on as next, next, etc.

Once the installation is completed, go to settings on the top ribbon. Open Style Configurator…

Settings drop down menu.
Settings drop down menu.

In the Style Configurator window you have variety of options. You can have fun with this, but for now, I would select a per-configured set from Select theme drop down.

Styles selection drop down list.
Styles selection drop down list.
Personally I like black background because I find it better on my eyes. I spend hours at a time writing code and most people I talked to also found back background to be very pleasant (as opposed to white).
PHP syntax coloured (automatic) on black background.
PHP syntax coloured (automatic) on black background.
It is my personal preference. The beauty of customization is you can choose a theme and/or color scheme to get your ideal working environment. Since you can always reset these, I entourage you to experiment with it.

HTML-Kit (free version)

Don’t get fooled by the name! This is one of the best free code editors out there. It started as a basic HTML editor and then transformed into what it is today. Like Notepad++, it also have support for almost all languages. However, this is big HOWEVER, it is not open source nor it comes with all the customization features like Notepad++. You cannot change the themes without having to buy their “premium” version. While I have used this extensively in the past, I am tired of limitations in free version. Please try Notepad++ first and if you don’t find it useful, the second best option would be HTML-Kit free version. Why pay for features that already included in FREE Notepad++?

Other Editors

All general text editing software that allow saving files in different formats (.php, .html, etc) can be used for programming. If you are new to coding please find a software that have built in syntax highlighter (eliminate Microsoft Notepad). This will make those first few steps into coding fun.