Direct Database Manipulation in WordPress
User have different requirements and you can face a requirement that may be you want to work with the WordPress database data directly and changing in it the Web Design Agency London have a good practice in it. When you developed a theme or plugin it may you accessing the custom database tables created. If you have require data from database then or fetch tha data then to do this operation, you’ll need to use SQL to query the data from the MySQL database.
The WordPress APIs provide access to all of the WordPress tables and in the WordPress only very occasionally will you need to access the tables directly. In this current and previous articles database related we use the wp_ prefix for tables, it may be in our database tables use a different prefix as defined in your wp-config.php file when installing WordPress.
For working with a WordPress database directly is by using php-MyAdmin one of the most common methods, as shown in below figure and it shows the default database view using phpMyAdmin.
Simply click the SQL tab across the top to run the SQL statements in phpMyAdmin. So you can run or execute any queries against your WordPress database. This is a good practice that creating your query directly in phpMyAdmin first before moving it over to your PHP scripts.
Because the reasoning behind this is that debugging SQL statements is much faster directly in phpMyAdmin than it is using PHP code in WordPress. If at one time you made a exact query which you want then you can use it in your PHP code and you can be confident the results will be as expected.
In the examples that follow you’ll be using raw SQL queries. Always remember that if you want to run these queries in a theme or plugin, you’ll need to wrap the queries in the WordPress database class. The wp_posts table is one of the most commonly accessed tables. The purpose of this table it stores all posts, pages, custom post types, revisions, and even attachment records. The post_type field is defined the different types of content. Since the version of WordPress 2.9 introduced the ability for developers to define custom post types. It means that additional post_type values may exist in this field of table. You can run this query for view all post revisions in your database:
SELECT * FROM wp_posts WHERE post_type = ‘revision’
Above query returns all records in wp_posts that are of a revision post_type. The above query you can modify for view all post attachments that have been uploaded to WordPress database:
SELECT guid, wp_posts.* FROM wp_posts WHERE post_type = ‘attachment
In the query a guid field use the field guid to be returned in the query. This guid field contains the full URL of the attachment file on the server contain. All the settings saved for your WordPress installation in the wp_options it save. option_name and option_value are saved in this table. The actual field name you call will always be those two names, rather than a specific field based on the option value. Below there are two extremely important records in this table as you can see:
SELECT * FROM wp_options WHERE option_name IN ( ‘siteurl’,’home’ )
Here in this query it is returning the two records, one where option_name is home and another where option_name is
siteurl.This setting that tell WordPress what the domain of your website is. If you ever need to change your website’s domain, you can run a query to update these two values like so:
UPDATE wp_options SET option_value = ‘http://yournewdomain.com’ WHERE option_name IN (‘siteurl’,’home’)
Your website will instantly run under the new domain when once this query runs. Keep in mind that this only updates the website’s domain in WordPress only. Posts and pages in Attachment URLs will also need to be updated to point to the new domain. May be plugins can also store the domain information, first confirm to test in a development environment before updating a production website on the server. You will be redirected to the new one If you access the old domain. Before you changed this if you were logged in then your cookies and session will be invalidated and you will have to log in again in the website.
If you built a new website under a subdomain (for example, http://webagency.intylestyle.co.uk) and are updating the URLs to push the website live so this is a great technique. Other very important fields also contain the wp_options table. If you want to see all active plugins on your website then you can view the active_plugins option_name like that:
SELECT * FROM wp_options WHERE option_name = ‘active_plugins’
All options defined by plugins these are also store in the options table. Some plugins have some tyoe of setting page these plugins activated in WordPress. Generally saved these settings in wp_options so the plugins can retrieve these settings as needed.
By default plugin of wordprss the Akismet plugin stores an option named akismet_spam_count that stores the total number of spam comments. By running the following query you can view this option:
SELECT * FROM wp_options WHERE option_name = ‘akismet_spam_count’
All the users detail contain in the wp_users table. There is facility of user can register on your website then every new users will be created in this table as they join your site. Every type of user detail like e-mail, username and password, website URL, and date registered are stored in the wp_users table. If you want to get all email address of users. By running the following query you can easily do so:
SELECT DISTINCT user_email FROM wp_users //SQL query for fetch the email address of users.
Here are all the users email address now you can easily export all of the e-mail addresses loaded into WordPress! For reset a user’s password this is another common query used in wp_users. There are many couple of different ways, but if you are absolutely locked out of WordPress, you can always reset the password directly in the database. For update the password you need to update the user_pass field from the MySQL command line as you can see below:
UPDATE wp_users SET user_pass = MD5(‘myresetpassword’) WHERE user_login =’admin’ LIMIT 1;
After Running this query it resets the admin password to myresetpassword. Here MD5 is used and wrapped the new password with MD5(). In the WordPress, this converts the password to an MD5 hash it is using since WordPress 2.5. WordPress is built to detect MD5 hash passwords and convert them to phpass encryption instead So Not to worry however it work. Above query will successfully reset your password in WordPress. All comments submitted to your website it is store in the wp_comments table. In this table have the following fields author, website URL, e-mail, IP address, and more.
Below is a query example for displaying comments:
SELECT wc.* FROM wp_posts wp INNER JOIN wp_comments wc ON wp.ID = wc.comment_post_ID WHERE wp.ID = ‘1557’
Above query will returns all comments of the post ID of 1557. In this table another important field in wp_comments is the
user_id field. A user is logged in to your website and posts a comment, then this field will contain his or her user ID. See the following code, if show the all comments left by the user admin:
SELECT wc.* FROM wp_comments wc INNER JOIN wp_users wu ON wc.user_id = wu.ID WHERE wu.user_login = ‘admin’
When writing custom queries to retrieve data directly from the database so this is incredibly useful. To fetch or retrieve all comments for a particular post you could run this query:
SELECT * FROM wp_comments INNER JOIN wp_posts ON wp_comments.comment_post_id = wp_posts.ID WHERE wp_posts.ID = ‘7’
Above query returns all comments for post ID 7. Have you in the above query that you join the wp_comments.comment_post_ID field to the wp_posts.ID field. There is an N:1 relationship between comments and posts so the SQL JOIN is necessary, In the database every post may have many comments but comments apply to only one post. So these two fields are shown in the diagram as the joining fields for these tables of database. See the following example, it is demonstrates how to join the wp_usermeta and wp_users tables together:
SELECT * FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id WHERE wp_users.ID = ‘7’
In the database diagram as you can see wp_users.ID field was joined to the wp_usermeta.user_id field both are joining. It retrieves all of the user information, including user metadata, for user ID 7, It is the default admin account. it extremely easy to determine again by the database diagram how logical INNER JOIN operations can build result sets of related table rows and how tables are joined by index value inside the WordPress database.
You can read some amazing tutorials at http://www.w3schools.com/sql/default.asp, If you are interested in learning more about SQL.