Certified Wordpress Developer Manual table manipulation

Manual table manipulation
 

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

Begin by accessing phpMyAdmin via cPanel or Plesk.

  1. Click on the database you wish to work on (from list of databases on the phpMyAdmin home screen).
  2. Once inside of the database, select a table by clicking on it.
  3. Once inside the table, click on the Operations tab.
  4. Scroll down to Table options where there is the option to change the name of the table.
  5. Where it says Rename table to, enter the new name for the table.
  6. When you are ready to proceed, click on Go.

The table will be renamed to the new name.

How to drop tables from a database in phpMyAdmin

Begin by accessing phpMyAdmin via cPanel or Plesk.

  1. Select the database you wish to work with (from the list of databases on the left column when on the phpMyAdmin home page).
  2. A list of tables will appear in the left column and in the wider right column.
  3. Check the table(s) you wish to drop (permanently delete).
  4. In the drop down box that initially says "With selected:" select "Drop" from this list.
  5. It will ask you if you really want to drop the table(s).  Check to make sure you selected the tables you really want to delete.
  6. Click on Yes to drop (delete) the table.

At this point, the table has been dropped from the database, including everything that used to be in it.

Adding a column to a database table in phpMyAdmin

Begin by accessing phpMyAdmin via cPanel or Plesk.

  1. Select the database you wish to work with (listed in the left column when you first access phpMyAdmin).
  2. If your table does not exist yet, add a table.
  3. Click on the table you want to work with, which will take you to the list of columns in the table.
  4. Underneath the list of columns, where is says "add 1 column(s)" click the Go button to add 1 column.
  5. Define the column 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.
  6. Click on the Save button when complete.

If successful, a message will appear stating your table was altered successfully and your new column will appear in the list of columns.

 

Modifying table columns in phpMyAdmin

Begin by accessing phpMyAdmin via cPanel or Plesk.
  1. Select the database you want to work with (by clicking on database name in the left column of the phpMyAdmin home page).
  2. Once inside a database, you will see a list of tables.  Click on the table you wish to modify.
  3. Inside the table, you will see a list of columns.  To the right of the column name, you will see a link called "Change" under the Actions.  Click on the Change link for the column you wish to modify.
  4. You can change a variety of things, including the name of the column, length of the field (maximum number of characters allowed), default value, and more.
  5. Once you are ready to proceed, click on Save to save the changes.

The changes you requested will have been changed.

Dropping columns from a database table in phpMyAdmin

It is recommended that you make a backup before you perform any destructive actions, such as dropping database columns. Without a backup, there will be no way to restore anything mistakenly deleted.
 
Begin by accessing phpMyAdmin via cPanel or Plesk.
  1. Select the database you want to work with (in the left column when you first access phpMyAdmin).
  2. Select the table you want to work with.
  3. Place a check mark next to the column(s) you wish to drop (delete permanently).
  4. Click on the Drop button underneath the list of columns.
  5. It will ask you if you really want to do this.  Review what will be dropped to make sure the correct columns are being deleted.  If it is correct, click on the Yes button to drop the columns.  Otherwise click No.

A message stating your SQL query was executed successfully will be displayed and your columns will be gone.

MySQL Variable Types

In a MySQL database, there are three (3) main data types: text, numbers and dates/times.  When you design your database, it is important that you select the appropriate type, since this determines why type of data you can store in that column.  Using the most appropriate type can also increase the database's overall performance.

Text Types

CHAR( ) A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
BLOB A string with a maximum length of 65535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

The ( ) brackets allow you to specify the maximum  number of characters that can be used in the column.

BLOB stands for Binary Large OBject, and can be used to store non-text information that is encoded into text.

Number Types

TINYINT ( ) -128 to 127 normal 0 to 255 UNSIGNED
SMALLINT( ) -32768 to 32767 normal 0 to 65535 UNSIGNED
MEDIUMINT( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED
INT( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED
BIGINT( ) -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED
FLOAT A small number with a floating decimal point.
DOUBLE( , ) A large number with a floating decimal point.
DECIMAL( , ) A DOUBLE stored as a string, allowing for a fixed decimal point.

By default, the integer types will allow a range between a negative number and a positive number, as indicated in the table above.  You can use the UNSIGNED commend, which will instead only allow positive numbers, which start at 0 and count up.

Date/Time Types

DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME HH:MM:SS
YEAR YYYY

Date/Time fields will only accept a valid date or time.