| When you create a post, page, link, or user, WordPress stores the relevant information in its MySQL database. The normal way to interact with the database is through WordPress' administration panels, or a blogging tool that works through the xmlrpc file, as we explain in Chapter 9. It may also be possible to access the database directly if you've installed WordPress on your own server. WordPress.com blogs don't offer this possibility. If your Web host provides a Control Panel as part of the hosting package you probably have access to both Manage Mysql for creating and removing databases, and to phpMyAdmin for working with databases. Another option is one of the many stand-alone programs or clients that provide a graphic user interface for working with MySQL, such as SQLyog for Windows (www.Webyog.com/sqlyog/index_sqlyogfree.php) or CocoaMySQL-SBG for Mac OS (www.theonline.org/cocoamysql). Although working directly with the database requires a good knowledge of MySQL, just looking is a safe and easy way to learn more about WordPress and what it's doing behind the scenes. If you actually edit the database directly, rather than working through the administration panels, you may make changes that cause your blog to function incorrectly or even not work at all, so act cautiously and always make a backup before changing anything. On the other hand, working directly with the database can save you hours of tedious labor if you need to make changes to large numbers of posts, and even allows you to do some things that are otherwise impossible with only administration panel access. Tips  | This section on working with the database is not intended to be comprehensive; it is just a glimpse into the behind-the-scenes world of WordPress. If you're looking for a good guide to MySQL, check out Larry Ullman's MySQL Second Edition: Visual QuickStart Guide, and PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide.
|
To edit a post with phpMyAdmin 1. | Log in to the Control Panel your Web host provides.
| 2. | Click on the link for phpMyAdmin. The phpMyAdmin Welcome window should appear.
| 3. | Select the correct database from the Database drop-down in the left column (Figure 4). A list of tables in that database is displayed below the name of the database in the left column. Information relating to the table is displayed in the right-hand pane (Figure 5).
Figure 4. Choose the database to work with from the drop-down list. 
Figure 5. Use the icons in the list of tables in the right-hand pane of the phpMyAdmin window to carry out many functions, including editing and deleting. 
| 4. | To see posts on your blog click on the table whose name contains 'posts'. For example, unless you changed the table prefix in the wp-config file when you installed WordPress, the posts table should be named wp_posts. The right-hand pane changes to display information about the fields within the wp_posts table.
| 5. | Click on the Browse button from the buttons across the top of the right-hand pane. A list of posts is displayed (Figure 6).
Figure 6. After selecting a table in the left-hand pane, view and edit its fields in the right-hand pane. Click the Browse button at the top of the window to display a list of posts in the selected table. 
| | | 6. | Click the pencil icon beside any post to edit that post. The selected post opens, with editable fields (Figure 7).
Figure 7. A post open for editing in the phpMyAdmin interface. The body of the post is contained in the post_content field. 
| 7. | Make changes as required.
| | | 8. | When you have finished editing, scroll to the bottom of the window. If necessary select Save from the left-hand drop-down. Select an option from the right-hand drop-down, as explained on the next page and then click the Go button to save your changes (Figure 8) or the Reset button to restore the post to how it was before you changed it:
- Go back to previous page: saves the changes you have made and then displays the list of posts.
- Insert another new row: saves the changes you have made and then opens a new blank row in the database. If you're working with the list of posts this is the same as starting a new post.
- Go back to this page: saves the changes you have made and continues to display the current row.
- Edit next row: saves the changes you have made and opens the next row for editing.
Figure 8. I save the edits by choosing Save from the dropdown and then clicking the Go button. 
|
Tips | Web hosts offer various versions and brands of Control Panels. The Control Panel your hosting service offers may not be exactly the same as we show here. It should however, be broadly similar. Contact your Web host for more information and help with any Control Panel they offer.
|
To allow access to stand-alone clients 1. | Log in to the Control Panel your Web host provides.
| 2. | Click on the link for Manage Mysql. The MySQL Account Maintenance window should appear (Figure 9).
Figure 9. The MySQL Account Maintenance window. 
| 3. | Scroll to the section labelled Access Hosts (Figure 10).
Figure 10. After adding my IP address to the Access Hosts section of the MySQL Account Maintenance window I can now access my blog's database with a standalone client. 
| 4. | Enter your computer's IP address in the Host text box.
| 5. | Click the Add Host button. You should now be able to connect to the database from that IP address with a stand-alone client.
|
Tips | This kind of behind-the-scenes access to the database can raise security concerns; your Web host may not allow it. Contact the Web host for help and further information. If your computer does not have a static IP address or is behind a firewall you may need to contact your computer support or ISP for help and further information.
|
To edit a post with SQLyog 1. | Enable access to the database, as explained in the previous instructions.
| 2. | Open SQLyog. A nagware screen appears if you're using the free version. Click the bottom button to continue (Figure 11) or click one of the other buttons to learn about upgrades. The Connect to MySQL Host window is displayed (Figure 12).
Figure 11. The free version of SQLyog includes a nagware screen, inviting you to upgrade to a paid version. 
Figure 12. The Connection window in SQLyog if you have no saved connections. 
| 3. | Click the New button. Give the connection a name and click the OK button (Figure 13).
Figure 13. Name this connection. 
| | | 4. | The Server panel becomes active. Enter the MySQL Host Address, User Name and Password details to connect to your blog (Figure 14). The information required here is the same as you entered in the wp-config file when you installed your blog. The Database(s) field is optional.
Figure 14. I enter the same connection details I used in the wp-config file when I installed my blog. 
| 5. | Click the Connect button. The connection details are saved, the software connects to the host and displays a browser window.
| | | 6. | Click the plus sign beside the database you wish to work with in the list of databases on the left side of the window. The list of tables in that database is revealed (Figure 15).
Figure 15. The tables belonging to the miraz_vqs database are listed in SQLyog. 
| 7. | To see posts on your blog right-click on the table whose name contains 'posts' and choose View Data from the contextual menu. For example, unless you changed the table prefix in the wp-config file when you installed WordPress, the posts table should be named wp_posts.
| | | 8. | The Table Data pane displays information about the fields within the wp_posts table (Figure 16). Scroll as needed to locate the cell you wish to edit.
Figure 16. The fields for wp_posts are displayed in SQLyog. 
| | | 9. | Click once on the cell you wish to edit (Figure 17). For example, if I wanted to edit the body of my first post I would click the cell where the row whose ID is 1 intersects with the post_content column. The field opens into an editing window (Figure 18).
Figure 17. Click once on a cell... 
Figure 18. ...and it opens into an editing window. 
| 10. | Make changes as required, click the OK button then click another cell. The changed information is sent to the server.
|
Tips | The contents of any edited cells are not updated in the database on the server until you take an action such as selecting a different cell or refreshing the browser display. The real power of a stand-alone client is to be able to search the database and use MySQL queries to carry out operations on large numbers of posts. Such techniques are beyond the scope of this book.
|
To edit a post with CocoaMySQL-SBG 1. | Enable access to the database, as explained in the previous instructions.
| 2. | Open CocoaMySQL-SBG. The New Connection sheet appears.
| 3. | Enter the Host, User, and Password details to connect to your blog. The information required here is the same as you entered in the wp-config file when you installed your blog (Figure 19). The Option settings section is optional.
Figure 19. Enter the same connection details as in the wp-config file. 
| 4. | Click the Connect button. The software connects to the host and displays a browser window.
| 5. | From the Databases pop-up on the left choose the database for your blog (Figure 20). The client connects to the database and displays a list of tables.
Figure 20. Choose a database to display from the pop-up list of databases. 
| 6. | To see posts on your blog click on the table whose name contains 'posts' (Figure 21). For example, unless you changed the table prefix in the wp-config file when you installed WordPress, the posts table should be named wp_posts. The right-hand pane changes to display information about the fields within the wp_posts table.
Figure 21. Select a table to display from the list of tables. 
| 7. | Click on the Content button from the buttons across the top of the right-hand pane (Figure 22). A list of posts is displayed.
Figure 22. The fields for wp_posts are displayed in CocoaMySQL-SBG. 
| 8. | Scroll as needed to locate the cell you wish to edit.
| | | 9. | Double-click any cell to edit it. For example, if I wanted to edit the body of my first post I would click the cell where the row whose ID is 1 intersects with the post_content column.
| 10. | The cell opens into an editing window. Click the Text tab to view text fields.
| 11. | Make changes as required and click the OK button (Figure 23). The editing window closes.
Figure 23. Edit text in the Text tab of the editing window. Then click the OK button. 
| 12. | Click another cell in the browser window. The changed information is sent to the server.
|
Tips | The contents of any edited cells are not updated in the database on the server until you take an action such as selecting a different cell or refreshing the browser display. The real power of a stand-alone client is to be able to search the database and use MySQL queries to carry out operations on large numbers of posts. Such techniques are beyond the scope of this book.
|
|
