Make Site Changes Easier by Editing within phpMyAdmin

Posted at August 23, 2014 at 9:18 am by admin

Many people use CMS systems such as WordPress, which store data in sql tables within a database. This is also true of ecommerce solutions which consist of WordPress plugins, and for other systems such as Joomla and Magenta. If you have a store which is part of a blog site then typically the data for an ecommerce plugin will be in a separate database from that which contains the blog.

There are a number of problems that can be eliminated by editing data directly in the database using phpMyAdmin.

1)   You can add information even when it won’t normally ‘stick’.

The data I entered just would not save no matter what I tried; the connections just kept timing out. I went to phpMyAdmin, copied some rows from the product table and edited the names, prices, and descriptions to make new products, and the problem totally disappeared.

2)   You can add a lot of information in less time. Sometimes the interfaces in various ecommerce solutions are great for small stores that are only selling twenty products but are slow when you are adding hundreds or even thousands of products.

3)   Occasionally there are glitches in the plugin interfaces, so that you can’t enter all the data on a particular device without continually changing your font size and scrolling all over the place. As we use more different kinds of phones, tablets and browsers, it becomes hard for the developers to take all of our needs into account, and I have seen this glitch even with one of the most highly rated WordPress content protection plugins. You can get around this by using phpMyAdmin to make your changes directly in the database.

4)   If you become proficient enough you will be able to write sql queries to make many changes at a time, such as increasing all your prices by 10%. Here is an example.

Tip: Keep in mind that in phpMyAdmin you can drag columns next to each other to make things easier. For example drag a column with the prices you are changing right next to the column with the product names.

If you are unfamiliar with phpMyAdmin, I suggest you install a copy of WordPress on your site with a test blog on it. This will allow you to familiarize yourself with phpMyAdmin without having to be constantly in fear that you will mess up your main site. There is one important difference between editing within a CMS such as WordPress and editing directly in the database – there is no undo! That’s right once you save a change to your database you can’t just restore the table by pressing CTRL-z or CMD-z. You can only fix the problem by importing a backup of the table or by importing a backup of the whole database.

Now that you have your test blog in place, let’s get started:

First, build a few posts with some unique text within your blog. Let’s make three posts that all have the text “Sam jumped on the elephant.”  Go ahead and publish the posts.

Now that we have our 3 posts we want to backup our database. The reason for backing up our database even though this is just a test blog is to get in the habit of doing so. With a full backup of your database and an additional backup of the tables you are working in, it is much easier to fix your mistakes. Go to your cpanel and look in the databases section for phpMyAdmin.

 myphpadmin

When you open myPhpAdmin you will see a list of all your databases

 myphpadmin

I happen to know that the database for my test blog is wrdp11, so I will select that database in the list on the left.

You can see now why the next time you add an installation of a cms like WordPress or Drupal, it would be a good idea to give the database a name that you will remember so you can find the correct one. You might even think that it would be a good idea to rename all your present databases with more memorable names. You could do that but there are all kinds of pitfalls. An easier way is to browse through the data of each database until you can figure out what it is being used for and then compile a list or a small spreadsheet.

Click on export at the top. You can just leave the default settings of quick and sql. Click go and save the file.

3

Now let’s save one of the tables. Select the wp_posts table on from the list on the left. (later when we search you will see that this is the table that contains our 3 posts containing the word ‘elephant’).  Again select export, click ‘go’ and save the file.

 4

Now let’s search for our text and replace it. I’m going to look for the phrase ‘Matt jumped on an elephant’ and replace it with ‘Matt Jumped on a trampoline’.  With the name of your database (in this case _wrdp11) selected go up top and click on ‘Search’. Type in the search phrase, change the setting to ‘the exact phrase’ and click ‘select all’ to search all the tables. If you don’t see any results use ‘all the words’ instead of ‘exact phrase’.  (This will often find things that you can’t match with exact phrase due to spacing problems.)

5

I see 3 matches inside the wp_posts table.

6

Click on ‘browse’ next to the listings. Now look in the ‘post_content ‘ column, click on the content in each row, change it and then click on something in another  cell within the table to save your change.

Although this method works for making a few changes, what if you had thousands of them – for example if you were changing all the prices on your store? We can make a lot of changes at once by clicking on ‘Query’ and entering the following:

update wp_posts set post_content = replace(post_content,’Matt jumped on an trampoline’,’Matt  jumped on  a large trampoline. ‘);

7

We just changed each occurrence of  ‘trampoline’ to ‘large trampoline’.

Click Submit Query and look up top. You will either see a green bar telling you how many rows were changed or a message that we have made an error in the query. Let’s look at the syntax.  update always stays the same, the database is the one that we have selected so we don’t need to include it in the query, wp_posts  is the name of the table, set does not change, post_content is the name of the column, it is followed by the old text and the new text.

There are two drawbacks to this method.

1) You must know the table and column that contains the data before you can write the query.

2.) You cannot make any spelling mistakes.

For these reasons it is often easier to search and replace manually if you only have to change a few pieces of data.

Tip: If you are trying to write a query that will increase all your prices by a certain percentage, then look at the example link above under 4).

In this next example we are going to look at a WordPress content protection system called Digital Access Pass which allows sites to sell content by protecting posts and files, so that only people who purchased access can see them. This plugin also allows you to build emails, choose one of them and deliver them on a specific day to people who purchased a particular product. The interface works fine on most computers but for some reason on my laptop I can’t drag the box that says ‘click and drag me’ and which controls the day a message is released. Although I could figure out in which table most of the data is stored by browsing through the table names, I had no clue how to specify which emails the purchasers of each product receive and which day they are sent on.

I’m going to build a short message and add it to one of the test products.

(The window that says ‘click and hold to drag’ will not drag on my laptop but I can view it by making the font display in my browser very small.)

As a first step I create the email then I add it to a single product and give it a day of delivery of 299. Because I know the pricing structure and the other numbers in the site, I’m confident that my search will only find one or two occurrences of that number. If I get more then I will go back, change the number and then search for the new number.

8

A search reveals that there is only 1 occurrence of the number 299.

9

By clicking on ‘browse’ next to the match we can see the content of the other columns in the table for our match and figure out what to change. In this case we would change the product ids in order to add the email to other products, the start date to change the mailing day and the display order as needed.

10

If we click on Copy (under Options, in blue towards the middle of the page) a page opens up that will allow us to make all our changes.

11

When you are done making changes click ‘go’ at the bottom of the page and the new, modified copy of this table row will be added to the database.

___________________________________________________________________

Let’s recap what I did here so that you can do the same thing with other ecommerce plugins or with other content management systems like Joomla or Magenta.

1) I entered some data that I believed to be unique by using the interface provided with the plugin software.

2) I searched for the data using a global database search. If I came up with too many matches I would re-enter a different value and search on that instead

3) Once that I know where the data is I can copy that row. myPhpAdmin will display an interface to let me change some of the data in the row I just copied before adding it to the database. It usually will also auto-increment the index column so I won’t have to edit that.

___________________________________________________________________

In order to work with myPhpAdmin effectively remember to practice on a test blog first, and to always back up both your whole database and the tables you will be working with. The more you learn the more time you can save yourself. There are many tutorials available on the net on writing queries to run in myPhpAdmin.

There is also a wide selection of both physical and cloud based books available on myPhpAdmin on sites like Amazon.com. You can choose from low cost introductory books that will enable you to learn simple tasks to more expensive and comprehensive manuals.

You can leave a response, or trackback from your own site.

Leave a Reply