PhpMyAdmin is a popular open-source application used to remotely manage and modify MySQL databases and is particularly useful for use on websites as it can be placed within a secure directory on a remote web server and accessed via a specific URL. This allows you to easily and quickly access your data to make changes, to download backups or perform a range of other useful operations.
e.g.
https://www.roughguidetophp.com/phpMyAdmin
An example of a PHPMyAdmin installation on a secure server (https). It’s also a wise move to password protect the phpMyAdmin directory using an .htaccess file to prevent anyone else from gaining access, although that’s outwith the scope of this tutorial for now. Also, you might want to consider naming the folder something other than phpMyAdmin, and perhaps place it within another folder in your root structure, to stop people guessing the location. This application will form the gateway to your entire database - Keep it safe, keep it hidden!
To install PHPMyAdmin, you will first need to download the appropriate file from the PHPMyAdmin website. Go for the “Latest stable version” and choose the zip file (or whatever compression suits you - zip files are the easiest to open on most Windows machines)
Once you have downloaded the file, and unzipped (or uncompressed) it into a folder on your own computer, you will need to copy this folder to the appropriate location. If you are running your own local server, you can copy this folder anywhere within the root of your webspace. If you don’t know how to run your own server and are using webspace hosted by a web hosting provider, you don’t need to worry about this.
For the rest of you, who are using a web hosting provider, simply upload this folder somewhere within the root of your public web directory, since you will need to access this folder remotely using your website URL
Uploading PhpMyAdmin to your remote host's server
Note : Some hosting providers are kind enough to provide access to PHPMyAdmin as default, or they might have some other database tool installed. You should check to see if this is the case first, as this will make the whole process easier. Take a look in your web host’s help files, or browse around in your control panel for a “databases” link. There’s sometimes a link to PHPMyAdmin from there
If your hosting provider isn’t using PHPMyAdmin, but has some other software available, you can try and use that, but I can’t guarantee it will have all the features available which I’ll outline below. You can always try and install PHPMyAdmin anyway, it won’t conflict with any existing database management software the hosting company might have installed.
Once you’ve installed PHPMyAdmin, you’re going to need to change a couple of values within a configuration file. Don’t worry though, there’s not much needs done. Here’s what you’ll need to do. This step won’t apply if you’re using a pre-installed version of PHPMyAdmin.
- Locate the file config.sample.inc.php - Copy or rename this file to config.inc.php and open it in a text editor. If you are editing this file on a remote server, copy it to your own computer before editing, and then re-upload it once you’re done
- Change these values…
$cfg['blowfish_secret'] = 'WhateverYouLike'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */ ... $cfg['Servers'][$i]['auth_type'] = 'cookie'; ... $cfg['Servers'][$i]['host'] = 'localhost'; // Change this to whatever value is appropriate
The host value should probably not need to be changed, since “localhost” is usually the connection method most database installations use, especially if you’re working on your own local machine. If your website host has specified you should use a different address (like an IP address, e.g. 163.63.77.3) here, then change it to the appropriate value.
The blowfish_secret value is important, as PHPMyAdmin encrypts your login data using this random string of characters (with “Blowfish” encryption, if you were wondering!). It can be whatever you want. Set the auth_type to “cookie” too, if it’s not already, as this will tell PHPMyAdmin to use the most secure method to login.
There are 3 different types of login method available, config, http and cookie. “Config” is the least secure as it means you have to store your username and password in the configuration file. “Cookie” has the advantage that you can “log out” of your PHPMyAdmin session once you’re done, for extra security.
Once you’ve copied PHPMyAdmin to the appropriate location and set up the configuration file, open up the relevant address in your browser, which would be something like
http://localhost/phpMyAdmin
If you’re using it on your local machine or, if you’ve uploaded it to a webserver, in a custom named directory
http://www.mywebsite.com/a_folder/phpManagementTool
You should then see a screen something like this
The login screen for PHPMyAdmin
The password and username you use here is dependent on either the values you’ve set when you installed the MySQL server on your local machine, or the values your hosting company gave you when you set up your account with them. Either way, enter the appropriate details here to gain access to the administration interface.
Upon logging into PHPMyAdmin, you’ll be presented with a screen that looks a little like this
The main PHPMyAdmin screen with Database creation box
The highlighted box is where you create any new databases from. The hierarchy of MySQL is such that databases contain tables, and tables contain records - so a database is a “top level” object. In the next step, we’ll see how to create tables within this database, for now, let’s create a database called “mydatabase”
Note: Some hosting companies will automatically create a database for you, limit your use of databases to only 1 per user, or perhaps have a different system in the control panel for creating databases. If this is the case, you should see the appropriate database listed in the database list on the left. You can then skip this step and move onto the next one to create a table
After creating a database, you will be presented with the following display, and a new database in the left-hand panel
After creating a database, you will see this
Once you have created the database, click on the database name in the left-hand panel. This will take you to the screen where you can view all the tables that exist within the database. At the moment, though, we don’t have any tables contained within the database, so let’s create one now. In the appropriate box (highlighted), type in the name of the table you wish to create, and the number of fields (columns) this table will hold. Let’s call our table “mytable”
Creating a table in our new database
After clicking “Save”, you will be taken to the next screen
On the next screen, you will be given the choice of what to call each of your fields in your new table. Think of each of these options as the vertical columns that will form your table. Each new row, or line in your table will contain a record, and we’ll look at how to add them in just a minute.
Defining fields in our new table
Here, we’re adding in three fields - “id”, “first_name” and “last_name”. Don’t worry about the specific meaning of all the options just yet, you can simply fill in the details and click “Save” for now. As you progress through the tutorials you’ll gain a better understanding of what it all means! Once you’ve saved your table definition, you’ll be presented with the following screen
After creating a table, you will see its Structure
- 1 - This is a “column” of the table, representing the field you’ve just defined, “first_name”. The options in this row represent the various operations MySQL can carry out on table columns, including defining any indexes
- 2 - Indexes aren’t important at this stage, but I want you to take note of this area of the table structure page, as we’ll be referring to it in a later tutorial when we work on table indexes
This screen shows you the structure of the table, and you can see the column names you’ve just added, along with a few new options for each of the columns.
Now it’s time to add in some records, or data, to our table.
Look to the left of the screen, you should see a panel contianing the new table you’ve created. This panel serves two purposes. It either shows a list of all the databases you have created, or a list of all the tables contained within a database, once a database has been selected. By clicking on the name of the table, you can return to the table structure screen. By clicking on the small square icon, you can see all the data contained in the table. But there’s no data in the table yet (try clicking on it, you’ll simply be taken back to the structure screen with a message MySQL returned an empty result set (i.e. zero rows))
Let’s add some data now. Click on the “Insert” tab at the top of the screen, as highlighted below
Inserting data into a table
You’ll then be taken to the following screen, where we can define the data we wish to add in to our table. For each record entry we fill in, a new row will be added to the table. Let’s fill in some values now
Inserting data into our new table
Fill in the values and click “Save”, or “Go” and you’ll be returned to the table structure screen. Now, we can click on the small icon to the left of the table name to see the results of our query
To access the table data
We’ll then see…
Viewing the data contained in a table
From this page, you can view all records contained within a table. If you look at the top of the page, you can see a box SQL query:. This box tells you the SQL query that was executed to display the information shown below. For a standard view of the data in a table, this defaults to a simple SELECT * FROM mytable query (we’ll look at queries shortly, in a future tutorial). But, it’s possible to generate your own custom queries, and this will become important in later tutorials when we look at more complex MySQL functionality. Let’s look now and see how we can generate our own custom queries.
Look at the top of your screen. You’ll see a row of tabs. This is the same row in which we clicked “Insert” earlier. Look a couple left of that tab, and you’ll see a tab marked SQL. Click on that now, you should then see the following screen
The custom SQL code screen
The box already contains a very simple SQL query
SELECT * FROM mytable WHERE 1
Which simply selects all values from the table. You can type in whatever SQL code you want into this box though, and it will be executed on the currently selected database. For now, we’ll just leave it at that, but to learn more about SQL queries, and to see more examples of code you can type into this box, read on to learn about the SELECT statement, and other useful keywords.
Hopefully you’ve now become reasonably adept at using PHPMyAdmin to access and modify your database information. Throughout the rest of the tutorials I’ll be referring to snippets of MySQL code which can either be run via PHP through the MySQL extensions (with functions like mysql_query() ) or directly through PHPMyAdmin via the SQL tab.
Feel free to experiment with this application too, there’s a vast number of options which perform a variety of operations on the databases and tables contained within. Most options should be reasonably self-explanatory, but if you’re in doubt about what anything does, make sure to check up in an online reference before clicking on it! (Eventually, I hope to create another tutorial outlining the more advanced features of PHPMyAdmin)
(Be especially careful with the Empty and Drop tabs at the top of the page. They will erase all data in a table, and delete a table/database respectively and are in big, bold red writing for a good reason!)
September 22nd, 2008 at 4:42 pm
Thank you very much for the tuto because it helped me a lot. I have been struggling to find a solution to my problem with phpmyadmin which did not want to work because the config.inc.php could not be set properly unless your tuto was applied. I mean use config.samples.inc.pht as a model for the config.inc.php.
Thank you again. God Bless you!