Setting up a SQL database and using phpMyAdminMarch 19th, 2012 @ 10:15 am
We already covered the basics of SQL databases in another post, but it’s also important to know how to create one and become familiar the tools you need to access and use SQL databases so you aren’t intimidated when the time comes to use a program like phpMyAdmin when working with a database.
phpMyadmin is one of the most commonly used SQL management programs around and it’s a part of almost every web host’s backend. It is, as per the title, written in PHP like many web scripts are and it serves as one of the main interfaces needed to manage a SQL database. It is open source software which means that the community has access to the inner workings of the program and it is published under an open license, so you won’t need to pay anything to use it.
Creating a SQL database
Most web hosts use H-Sphere as a backend GUI because of its user friendliness and flexibility, so we will go through the process of creating a database using the basic tools in H-Sphere. If you use something else, chances are the process is very similar.
There should be a section among all of the menus in H-Sphere called Databases. Here you’ll find a few of the main functions for managing mySQL databases. To create one, click on MySQL Database Wizard. After that you’ll have the option to create a new database.
Put in a name for the database and hit the button and an empty SQL database is created. Next you need to assign a user to it so you can sign in an as that user and gain access to its entries. Use an existing username or create a new one and assign it to the database with administrative privileges, remember the password and other information because any programs that rely on this new database will need it. Once the user is assigned you’re all set, the database is ready to be populated.
For a detailed step-by-step, refer to our flash tutorials section.
Now that you’ve created a SQL database, you can click on phpMyAdmin from the H-Sphere main menu and find your database by its name on the left. You can select it and perform a variety of actions from the GUI- here are some of the most common ones:
Importing and Exporting .sql files
If you already have some tables and information saved as a .sql database file you can upload it with phpMyAdmin easily and have it on your database in a matter of seconds. Just click on Import on the top menu and you can browse for a .sql file to upload. Conversely, you can save your current database as a .sql file as a backup for later use, or if you are migrating to a new server.
If you want to change a value or make a quick edit to your database, it’s as easy as going to the Structure tab and navigating the table until you find what you need, it’s a bit like an excel table with the ability to have nested entries. If you want to add a table or rename the database just go to Operations and fill out the respective forms.
One of the most basic functions one would need when managing a MySQL database is the ability to run queries. Queries are pieces of code that you can input into phpMyAdmin to do things like update, create, and delete entries and easy make changes to your database quickly without fiddling with a GUI. Just click on the Query tab and put your code into the text box and hit Go to run it. If you don’t know the proper syntax or aren’t sure what you’re doing, it’s best to stay away from queries until you do.