This recipe will show you some basic commands and operations you may need from time to time.
In this section we'll look at a few basic commands and scripts you may need from time to time.
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.
- Getting a list of names, username, and their email address from your database.
- Open your phpMyAdmin and navigate to your database as in previous steps.
Here is a screenshot of our database ready to work on.
- Click Query on the toolbar on the top in phpMyAdmin and click the SQL button.
You will see this screen:
- 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.
This 'queried' the database for this information and provided it quite simply.
- 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.
- Enter the following script:
SELECT name, username FROM jos_users where GID=25; - The GID of 25 represents the default Super Admin user. Running against our database reveals not one but TWO super administrator users.
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'
- 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.
- 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. - Here is a real example on the table that stores website content, jos_content.
This will then show you a screen similar to the following:
The second method allows you to optimize a single table, any number of tables, or all the tables at once.
- 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.
You should see a list of all the tables as follows:
- Scroll all the way down to the bottom, you'll see this:
- For convenience, the drop down has been opened for discussion.
- Now click Check All, this will highlight all the tables.
- Pull down the drop-down box.
- 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.
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.