Working with your database using phpMyAdmin

This recipe will show you some basic commands and operations you may need from time to time.

Tip

I strongly suggest you setup a test Joomla! site and test database to learn these commands. That way if you break something, you won't damage your production site.

In this section we'll look at a few basic commands and scripts you may need from time to time.

Getting ready

You will need the passwords for:

  • FTP
  • cPanel
  • Possibly your database (this can be obtained from configuration.php)

Depending on your hosting you may need your username and password for the database. Some hosts require it once you get into their control panel system and others do not. GoDaddy.com®, for instance, requires you to enter a username and password to interact with your database. Other webhosts do not require it, once you are logged into cPanel.

You will need a copy of your database (see the preceding warning). You can use the previous chapter on creation of a database and the two previous recipes to EXPORT your data and add it to a new database using the IMPORT.

How to do it...

  1. Getting a list of names, username, and their email address from your database.
  2. Open your phpMyAdmin and navigate to your database as in previous steps.

    Here is a screenshot of our database ready to work on.

    How to do it...
  3. Click Query on the toolbar on the top in phpMyAdmin and click the SQL button.

    You will see this screen:

    How to do it...
  4. In the Run SQL query/queries on database _testDB: enter this script by typing it in and clicking Go in the lower right-corner.
    SELECT name, username, email FROM jos_users;
    

    This will yield the following from my test database. You will have different information in yours.

    How to do it...

    This 'queried' the database for this information and provided it quite simply.

  5. Let's look at another query - one that will tell us WHO is the SUPER ADMINISTRATOR in our website. This has multiple implications, such as security and recovery of a lost Super Admin password.

    Return back to this screen by clicking the SQL button again.

    How to do it...
  6. Enter the following script:
    SELECT name, username FROM jos_users where GID=25;
    
  7. The GID of 25 represents the default Super Admin user. Running against our database reveals not one but TWO super administrator users.
    How to do it...

    Tip

    What about other GID's?

    Other user GID numbers can be found by replacing the GID=25 with GID=#. For instance you can review whose who using the above SQL statement with these GID's:

    #=17 'ROOT'

    #=28 'USERS'

    #=29 'Public Frontend'

    #=18 'Registered'

    #=19 'Author'

    #=20 'Editor'

    #=21 'Publisher'

    #=30 'Public Backend'

    #=23 'Manager'

    #=24 'Administrator'

    #=25 'Super Administrator'

  8. Optimize table(s): On a very busy website, you may see performance dropping. This is a quick means to clean up the database. This script will be presented in two forms, command line, and GUI.

    Please make sure you EXPORT your database before attempting this command.

  9. Open your SQL window as you did in previous steps, and type in this command:

    OPTIMIZE TABLE tablename: Where tablename is the name of the table in question.

  10. Here is a real example on the table that stores website content, jos_content.
    How to do it...

    This will then show you a screen similar to the following:

    How to do it...

    The second method allows you to optimize a single table, any number of tables, or all the tables at once.

  11. Click on the DATABASE <name> link at the very top. See the arrow in the following screenshot. This will return you to the starting point for your database. In this example testDB.
    How to do it...

    You should see a list of all the tables as follows:

    How to do it...
  12. Scroll all the way down to the bottom, you'll see this:
    How to do it...
  13. For convenience, the drop down has been opened for discussion.
  14. Now click Check All, this will highlight all the tables.
  15. Pull down the drop-down box.
  16. Click Optimize table.

    You can also optimize just a single table, for instance, jos_content. This is a quick and easy to way to do so without having to remember SQL commands.

How it works...

Running OPTIMIZE TABLE tablename solves this problem. You should run the OPTIMIZE command occasionally on a busy website. 'Optimize' reclaims the space that was consumed by the deleted data.

During the operation of a website, if you have lots of content or other items changing frequently, this can leave gaps in the tables. This means your server will have to work harder, taking longer to deliver information, thus you could experience website slow down.