There may be times when you want to work with the WordPress database data directly. This can include accessing custom database tables created by a plugin or theme. To do this you'll need to use SQL to query the data from the MySQL database. Remember the WordPress APIs provide access to all of the WordPress tables and only very occasionally will you need to access the tables directly.

One of the most common methods for working with a WordPress database directly is by using phpMyAdmin.

Backing Up the Database

This assumes you have already installed MySQL Admin and set it up so that you can login to the MySQL Database Server either locally or remotely. Refer to the documentation that comes with the installation package of MySQL Admin for your platform for installation instructions.

1. Open the MySQL Admin client and login as you had previously set up to do.
2. From the icon menu on the left hand side of the client window select Backup.
3. If you have not already created a Backup Project, do this now by clicking on the "New Project" button at the lower part of the window and type in a name for the Backup Project where prompted.
4. Select one or more databases that you want to Backup (in the MySQL Admin client these are called a "Schema" (pl. "Schemata")). Add them to the Backup Content window on the right using the right-pointing arrow button.
5. When you have selected the Schema(ta), you can save the Backup Project. Or you may simply choose to Backup Now using the button on the lower right of the window.
6. A dialogue will come up asking you where to put the Backup. Enter the pathname or browse to the location using the dialogue.
7. Assuming all is correct (and you have write permissions in the directory to which you are writing the Backup), the backup will complete shortly.

Restoring From a Backup

1. Open the MySQL Admin client and login as you had previously set up to do.
2. From the icon menu on the left hand side of the client window select Restore.
3. Click the "Open Backup File" button on the lower right of the window.
4. Type in or browse to the Schema(ta) backup file and select. Click "Open".
5. The Target Schema(ta) will most likely be the "Original Location", or you may choose an alternate location using the drop-down menu.
6. Click the "Start Restore" button on the lower right of the window. The database restore will commence.

Searching through a MySQL database in phpMyAdmin

Begin by accessing phpMyAdmin via cPanel or Plesk.

1. Select a database you want to search (by clicking on the database in the left column while on the phpMyAdmin home page).
2. Once inside the database, a list of tables appear.  Click on the Search tab.
3. Once on the search tab, you can type a list of keywords you wish to search for.  Each keyword is preferably separated by a comma.
4. Select which way you want to search:
   • At least one of the word.
   • All words.
   • The exact phrase.
   • As regular expression.
5. Select which tables you want to search within.  You can select them all, select some of them, or select one.  Hold down the Ctrl key on your keyboard while clicking, to select more than one table.
6. Press the Go button.
7. It will now display the results based on table.
8. Click on Browse next to one of the tables to view the results.

The records matching your search will be displayed on the screen, and you can interact with them in a variety of ways such as copying, editing or deleting.

How to run SQL queries in phpMyAdmin

Queries are Context Sensitive

The SQL tab appears above all pages inside of phpMyAdmin.  This tab is context sensitive, which means that depending on what you're looking at, the target of your SQL queries might be different.

If you are on the home page of phpMyAdmin and do not have any databases selected, if you click on the SQL tab, any queries you run will apply to the overall server (or hosting account if on shared hosting).

If you select a database, any queries run by clicking on the SQL tab will apply to that database.

If you select a table within a database, any queries run by clicking on the SQL tab will apply to that table.

Summary of Steps

Begin by accessing phpMyAdmin via cPanel or Plesk.

1. Navigate to the area your SQL query will apply to.
   • The phpMyAdmin home page if you want the query to apply to the whole server or hosting account.
   • The database you want to run queries against.
   • The table you want to run queries against.
2. Click on the SQL tab.
3. Type in your SQL query.
4. Click on Go to execute the query.

Your SQL query will be executed and the actions you requested will be performed.

How to add tables to a database in phpMyAdmin

Begin by accessing phpMyAdmin via cPanel or Plesk.

1. Select the database you wish to work with.
2. Inside the structure tab, under the list of tables (if any), there will be a place to "Create tables on database."
3. Where is says Name, enter the name of your table.
4. Where it says Number of Columns enter the number of columns you want to configure right now (you can always add more later).
5. Press the Go button to proceed to the next screen.
6. Define the column(s) by entering the following information:
   1. Name the Column.
   2. Select the Type of data the column will hold.  Some common types include:
      • INT = Integer (a number without a decimal point)
      • CHAR = Characters (can hold text up to a specific length)
      • VARCHAR = Variable Length Characters (a text field that is not a fixed width).
      • TEXT = For holding large amounts of text.
      • DATE = Can only hold dates.
      • DATETIME = Can hold both a date and a time.
      • (See the MySQL Data Types article for more options.)
   3. Define the Length/Values if required.  For example, the CHAR type will require you to specify the maximum number of characters allowed.
   4. The other fields are optional.
7. Click on Save when you are finished defining your columns.

A message indicating your table has been created will appear, and your new table will now appear on the list of tables in the database. You can click on the table to see the columns that were created for that table.

How to rename database tables in phpMyAdmin