Database overview

Whether you are a new or veteran web developer, sooner or later you will come across to the concept of database. Some people chose to skip everything about database until they reached a point in which they acknowledged the art of it. The amounts of tools to help you manage databases is uncountable and diverse. Fortunately, there is a couple of tools that can make your life a lot easier especially when it comes to local web development.

You may have encountered many types of Structured Queries Language during your career escalation. This includes MySQL – the most popular database language. Any web development process requires a database and a host to successfully deploy the final product. In other words, missing either of these factors can result in disrupting the development progress.

What can a database do?

Have you ever used WordPress or Blogspot? These two CMS platforms are typical examples because of their familiarity. They simplify the setup procedures for users by automatically creating the databases and integrating them within your projects.

Some non-technical users may not notice the databases’ existence but they are there all the time, sitting behind the curtain just so you know.

A database’s as important as any stage to develop your website, every time you change settings of the theme, create a menu, make a post, add a widget, etc…, the database will record and store all information.

How to control the databases?

In case you’d like to manage databases manually such as relocating, cloning, backup or further development includes queries, there is numerous applications that can help you.

Keep in mind, the possibilities of database administration tools in combination with SQL languages can be vast. However, the core development process is similar.

phpMyAdmin is a great tool to manage MySQL, this is a web application that is available for both commercial host and the virtual local environment. When you subscribe to any host, this application can be found within your CPanel.

However, in some cases, local development may become the business’s first priority before taking off hence the ability to use an offline tool is also a great choice.

In this guide, we will be using a package tool like XAMPP to create a virtual space to host the phpMyAdmin application.

NOTE: You may use similar products to XAMPP like WampServer, MAMP, AMPPS, EasyPHP… for this practice.

Preparation and assumed knowledge

I’m showing 2 examples as they are the typical and popular CMS platforms that are available for local development: WordPress and Sitefinity. Install the necessary resources on your local machine.

If you are a WordPress developer:

• Download and install XAMPP
• Download WordPress
• A code editor other than Notepad

If you are a Sitefinity developer:

• Have the XAMPP installed
• Follow up the Sitefinity training series to have a glimpse of which should you set up.

By the end of this guide, you should grasp the idea to manage databases for your local web project using phpMyAdmin.

Install and configure phpMyAdmin

Activate phpMyAdmin administration tool

You can download for free and install XAMPP on your machine. Like many other web applications, phpMyAdmin needs its own virtual localhost to run. You may activate this virtual localhost as well as the database administration gateway within XAMPP in just two clicks.

Within the Actions column, press Start to run Apache and MySQL modules.

Apache enables localhost gateway and treats your computer as a host server. The server address is usually 127.0.0.1 by default.

MySQL refers to phpMyAdmin to create/modify databases at will.

Create a database

Hop into your browser and put localhost/phpMyAdmin in the address bar. You end up with a dashboard with tons of settings. Take time to get used to it but it’s quite simple.

On the very left sidebar is where the databases locate, hit New to create a new one.

Give your database a sexy name and if you are likely to use Latin characters, select utf8_unicode_ci as it represents for English letters. Otherwise, select a suitable language then hit Create.

You have your first database created but it’s blank and that’s ok.

Now, from a group of developer perspectives, you may work with someone else and some personnel need access to this database. We need to create more users and give them privileges to manage the database.

Create a user to access the database

On the same screen, click on Privileges on top, you will see some sensitive information about the top powerful user named root. This is you the person who has the power to create and remove the admins.

Click Add user account.

Give your teammate a cool username and password, make sure the Host name is set to localhost and this character has all privileges for this database.

If you want them to have the same power as you, go ahead and check everything at the bottom. Otherwise, click Go to finish the creation.

You now completed the database configuration and ready to connect them to your local web development.

Connect the database to Sitefinity

When you first set up Sitefinity CMS, select MySQL option. Using MySQL database may be tricky when you had both XAMPP and IIS installed on the same machine. These two softwares use the same default local port and causes XAMPP to crash.

You can leave the Server blank or put in localhost, I left it blank because it auto-detects the server as you enabled Apache.

Port number by default should be 3306.

Username and password of the person who has access to this database.

Lastly, the Database name that you want your Sitefinity project to look up for.

When you’re done, Sitefinity will lay off numerous tables into the database, that’s when you know you have successfully linked the database to your Sitefinity project.

Connect the database to WordPress

Set the database at the beginning

Assuming you knew how to set up WordPress with XAMPP (just copy wordpress folder into htdocs directory). At some point, WordPress will prompt you to connect to the database. Fill in the blanks with your credentials that you created earlier in this guide.

Table Prefix allows you to combine many projects into one which is sucked. This is a bad practice in terms of file size, leave this as default. Keep a single project in a single database helps you organize better.

When you move to the next screen and do it right, WordPress will inform you that the connection was established.

Set the database after the installation

Oops, I accidentally mess the database up, now my WordPress project crashes and I want to change to another database and start over.

There are many practices around databases including swapping, replacing, updating, backup and so on but the ideal solution for local WordPress development is taking control over your database configuration file.

Locate  \\xampp\htdocs\wordpress\wp-config.php and open it with any text editor and you will be able to determine which and how you are going to use the databases and change it at your own sake.

Keep an eye on the 3 strings: DB_NAME, DB_USER, and DB_PASSWORD.

Back to the case above, should you really need to remove WordPress and reinstall or just simply adjust the database instead?

This is also the reason you should not ever use 1 database for multi-projects.

Conclusion

Database management is fun but it’s even more fun when you get your foot wet. You have an opportunity to encounter many different practices such as creating, integrating, removing, changing, updating… also enhance a comprehensive skill of using phpMyAdmin to manage databases, especially for local web development.

As I mentioned, there are many distinct combinations regarding SQL and its associated applications. These two CMS platforms above are examples to help you start your first steps in database management. You can pick your own tools as you feel comfortable with and use a similar technique to support your projects.

By now you should have known how to:

• Deploy phpMyAdmin application locally to manage databases for local web development.
• Set up a Sitefinity project with phpMyAdmin database management process.
• Set up a WordPress project with phpMyAdmin database management process.

If you know any similar local web application and struggle with the database configuration please let me know.