Tag: wordpress database

WpDataTables plugin- Understanding it in and out

Do you own a WordPress website? Do you want to include a table price list in your WordPress site? If your answer to both these questions is a “Yes” then you’ve landed on the right post. Here, I’ll be making you familiar with one of the best WordPress plugins that work as the best option for incorporating a table price list in WordPress powered websites. Backed by an easy-to-use interface, wpDataTables is the perfect solution for a number of “dirty” technical jobs such as: embedding the HTML Tags to the text, converting the table types and many more. The best part of using wpDataTables plugin is that you can do all this without the need for performing any complicated coding.

 

WpDataTables plugin- Understanding it in and out
WpDataTables plugin- Understanding it in and out

 

Why there’s a need to add tables into a WordPress website?

Well, there are a number of reasons backing the importance of adding tables to the WordPress posts and pages. One of the best reasons is the ability of a table to add an incredible amount of content in an easy-to-interpret and legible format. Whether you’re inclined on adding a table regarding the different business locations, a list of all the upcoming events along with dates and locations or a directory containing details about different employees; wpDataTables is a plugin that allows you to convert your ideas into reality.

WpDataTables plugin- A look at its history

Published in the year 2013, WpDataTables was introduced as a small plugin for WordPress enriched websites, WpDataTables has evolved into a fully-featured plugin. Being constantly improved by a lot of WordPress designers and developers, WpDataTable plugin has played a vital role in the creation of re-stylable, responsive and fully-functional tables, to be included within WordPress posts and pages. With innovative features being added to WpDataTables plugin with each passing month or two; WordPress designers and developers can now enjoy a fuller experience of adding tables that are more functional and customizable. The company behind WpDataTables plugin is planning to introduce new features in the plugin including eye-catchy skins, a WYSIWYG table constructor and pricing tables.

What’s wpDataTables plugin best suited for?

Currently being used on over 1000 WordPress websites, wpDataTables plugin is a perfect solution for a range of jobs including the ones mentioned below:

  • Incorporation of any kind of tables that are responsive in nature
  • Incorporation of catalogs and other kinds of tables that are fully editable from the front-end
  • Incorporation of searchable price list tables and catalogs
  • Ajax-based rendering(on the front-end) of large datasets comprising of thousands of records.

WpDataTables- A brief on its working

Adding responsive data tables into a WordPress post/page has become really simple. All thanks to the superb WpDataTable plugin. All you need to do is install it into your site and you’ll be able to add a new table, select and upload data source, followed by inserting the shortcode(generated by WpDataTables plugin) anywhere across the entire website. And that’s not all. You can gain an easy access to a large number of features including grouped rows, fixed headers, charts as well as numerous customization options such as color, fonts and translations.

And now, some simple steps on using WpDataTables plugin

Step 1- After the download and installation of WpDataTables plugin, start off with creating a new table

As per this step, choose the option ‘Add a new table’ in the WP Admin menu and set the table parameters including: table name, table data source(Excel file, MySQL, CSV file etc.). In addition to this, you’ll also need to define extra table parameters such as: whether you intend to display the advanced filters below each column,  whether you want to show buttons for exporting data to CSV, what is the count of rows that you want to be displayed on one table page, whether you want the table to be a conventional one or responsive, and many others. Once you’re done with selecting values for all parameters, click on “Save table” button.

Step 2- Set up the column properties

Although this is an optional step, but I recommend you to follow it without fail. Here, you need to define a unique name for each column. This name would be used in place of the original column header. In addition to this, you’ll also be required to set the column type for handling data as a number, string or date. You can also choose to filter each column by a text input, data ranges, selectbox dropdowns, number ranges etc. Last, but definitely not the least, you can also change the display order of the columns by performing simple drag and drop function. Hiding the columns is also possible here. Once you’re done with making changes to the columns, click on “Save columns” button.

Step 3-Publish the table

In order to publish the newly created table, all you need to do is simply copy and paste the table shortcode that’s available in the admin page header part to the WordPress post or page. After you’re done with this, save the setting and the table will start appearing on the WordPress website’s front-end.
So, with the successful execution of the aforementioned third step, you’re done with incorporating a table into your WordPress post/page.

What makes WpDataTables plugin a right fit for thousands of WordPress driven websites?

Unlike a wide range of WordPress plugins that are used for creating editable responsive tables, WpDataTables plugin is considered as one of the high-rated plugins that are being actively maintained and continuously enhanced by a huge population of trained WordPress designers and developers. Here’s a look at some of the best features of WpDataTables plugin that have impressed one and all:
Can fit any requirement- Quite dissimilar to many WordPress plugins which are restricted to just offering the flexibility of adding tables into posts and pages, WpDataTables plugin is best suited for equipping WordPress posts and pages with catalogs, bugtrackers, front-end DB management, pricing tables and many more.
Is highly editable– With WpDataTables plugin, you gain the flexibility of editing the MySQL data directly from the website’s front-end, without the need for doing any complex coding.
Has a flexible and easy-to-customize design- WpDataTables plugin has been tested on a large collection of WordPress themes. That means, on installing this plugin into your site, you need not worry about the distortion of your site’s original theme/design.
Full responsive- WpDataTables plugins works as the best tool for creating fully responsive tables that comprise of collapsable rows. And the best part is that you can create such tables with a simple click of the mouse.
Lifetime technical support and free updates- Seamless technical support and timely updates are the two major concerns of every WordPress website owner who has chosen to install a particular plugin in his/her site. With WpDataTables plugin, you need not worry about both these scenarios because the plugin offers you free lifetime updates along with a round-the-clock technical support for a wide range of issues regarding the installation and use of the plugin. It is interesting to note that everyone who has chosen to download and install WpDataTables plugin has displayed utmost satisfaction regarding the use of plugin for creating flawless tables, catalogs, flow charts etc. WpDataTables plugin has already received excellent reviews from WordPress site owners across the globe.

Wrapping it all up

With all that being said about the performance of WpDataTables plugin, its time for you to choose the same for your WordPress website. I’m sure, post installation of WpDataTables plugin, you’d definitely be able to meet all your customized needs that have been bothering you since a long time.

Delete users by date wordpress Mysql

spam users which was registered. I used following Mysql commands for removing he wordpress users. query for Delete users by date wordpress Mysql

 

Some days before I enabled the my site registration as subscribers. In last seven days there are four thousand subscribers has been registered on my site. After reviewing the users and there server request logs I got to know about that was spam users which was registered to my site. WordPress is always easy target for spammers and hackers. I tried to use some code for remove the wordpress users but then later I used following Mysql commands for removing he wordpress users.

Delete users by date wordpress Mysql

 

When you create or register the user in wordpress that time some tables are filled by wordpress. Some entries will insert into wp_usermeta table and wp_users table. If you want to delete the bulk users then you need to delete user entries from wp_usermeta table first. You can use following SQL command for deleting the user meta entries from wp_usermeta table.


DELETE wp_usermeta FROM wp_usermeta, wp_users WHERE wp_users.user_registered > '2013-11' AND wp_users.ID = wp_usermeta.user_id;

After deleting the user meta entries from table then remove users from wp_users table using following command


DELETE FROM `wp_users` WHERE `user_registered` > '2013-11';

Note: Before deleting the users from wordpress database, take the Database backup. Take the worpdress backup and restore on local box. Make sure your DB backup file is perfect.

Do not delete users from wp_users at the first. First delete user meta from wp_usermeta then execute the next command.

 

wordpress delete old post revisions improve site performance

wordpress tutorial, wordpress delete old post revisions improve site performance. We given sql commands and suggested some plugin for delete post revisions.

WordPress has auto save functionality. After some time wordpress creates the revision in database tables automatically. It is really useful. If something happens like powercut or browser crashes then you do not loose the your post data.

wordpress delete old post revisions improve site performance

SQL for delete post rivisions.



DELETE wp,wt,wpm FROM wp_posts wp LEFT JOIN wp_term_relationships wt ON (wp.ID = wt.object_id)
LEFT JOIN wp_postmeta wpm ON (wp.ID = wpm.post_id) WHERE wp.post_type = 'revision'

 
If you want to disable post revision system permanently, you can add following line in wp_config.php file.

define('WP_POST_REVISIONS', false);

 

But it has some bad issues also. If you have 5 to 10 revision per post and 200 posts then you will have at least 1500 to 2000 entries in table. Most important part is all revisions are unuseful. Each additional revision increase the size of your database thus making the processing time slower and slower.

But do not disable to auto save functionality and revision. you need to clean your db after certain time.

For cleaning the database you can use the following plugins.

  • 1. Optimize Database after Deleting Revisions –

Optimize Database after Deleting Revisions


This plugin is a ‘One Click’ WordPress Database Cleaner / Optimizer.

Main Features
Deletes redundant revisions of posts and pages (you optionally can keep an ‘x’-amount of the most recent revisions)
Deletes trashed posts, pages and comments (optional)
Deletes spammed comments (optional)
Deletes ‘orphan postmeta items’
Optimizes the database tables (optionally you can exclude certain tables from optimization)
Creates a log file of the optimizations (optional)
Optimization can be scheduled to automatically run once hourly, twice daily, once daily or once weekly at a specific time (optional)
‘Optimize DB (1 click)’ link in the admin bar (optional)
Settings
You can find the settings page in the WP Admin Panel » Settings » Optimize DB Options.

Starting the Optimization
You can start the Optimization in the WP Admin Panel » Tools » Optimize Database. Note: if you use the Scheduler the Optimization will run automatically!

  1. 2. Revision Removal – http://wordpress.org/plugins/revision-removal/

Revision Post is the new features of WordPress ver 2.6 and above. Every time you made a change to your post or page, it will automatic save add a revision, just like a draft. As many as you made change, the more revision you will have in your database. It will not only increase your database size, but also increase your load time as the server need more time to access the database.

Revision Manager is your choice to remove all the revisions quickly to help you to increase the speed of implementation of the SQL statement, and increase the speed of your server load.

wordpress delete old post revisions improve site performance
wordpress delete old post revisions improve site performance

optimize wordpress database using phpmyadmin

Sometimes our wordpress tables became so big and we need to worry about wordpress database and table size. Big table always take extra time to execute the query. For this issue we need to keep our wordpress tables clean and do optimization time to time.

optimize wordpress database using phpmyadmin

Optimize wordpress database using phpmyadmin. For optimization, We used 2 tools which are reliable. We used wp-optimize wordpress plugin and PhpMyAdmin tool.

WP-Optimize is a wordpress database cleanup and optimization tool. It doesn’t require PhpMyAdmin to optimize your database tables. It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.

optimize wordpress database using phpmyadmin
optimize wordpress database using phpmyadmin

Second tool I used which is PhpMyAdmin. Using know which tables are over flowing and over size. In wp-optimize panel you will which tables are needed the optimization. Just open your phpmyadmin and select your wordpress database. Click on”Check tables having overhead” link, that will select the overhead tables from your wordpress database. Than just select the Optimize table option.

optimize wordpress database using phpmyadmin
optimize wordpress database using phpmyadmin

This action will just execute the following query.


optimize  table wp_posts, wp_comments;

if you want to optimize another tables then just use the above query. OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

For MyISAM tables, OPTIMIZE TABLE works as follows:

  1. If the table has deleted or split rows, repair the table.
  2. If the index pages are not sorted, sort them.
  3. If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

If you having any issues or question about optimizing table then please do write to me.