SOLVING DATABASE TABLE PREFIX ISSUE IN WORDPRESS
When we create WordPress plugins then we generally need to create new tables for storing some data. As plugin should generally work on any WordPress site so it is important to keep in mind that the queries must work on all the sites successfully. WordPress database contains each and every information of wordpress. By default in wordpress, we have wp_ database table prefix i.e we usually create tables like wp_storage, wp_datatable etc. But this makes easier for spammers and hackers to attack a site by targeting the default prefix wp_.
Therefore for security reasons,we should change our database table prefix which we can do easily by changing it in wp_config file. Open the wp_config file from the root directory and change the table prefix line from wp_ to anything else like wp_f264963_ and save it.
It means we can have different database table prefix for different sites. When we hard-code the table prefix to wp_ then the query like “SELECT * FROM wp_datatable” cannot work here. In this case our plugin does not work. Therefore to solve this kind of situation we should use table prefix by generating it dynamically so that even if the site in which our plugin is installed has different table prefix, our plugin should not fail. For this purpose, we can use class wpdb.
WordPress has a database class wpdb which we use to get the data from the database. We can use the object $wpdb of this class simply by using keyword global:
global $wpdb;
This wpdb class stores much of the useful information about the database which we can use in our code like table prefix. There is a property of this class prefix which stores the database table prefix of the site.
For example: Let us take an example in which we are generating a query to get all the products with particular id. Suppose wp_kwhg2727_ to be our table prefix for the site:
global $wpdb;
$result = $wpdb->get_results($wpdb->prepare("SELECT * FROM wp_products where id=%d",$id));
Here, we have $id variable containing id of the product on the basis of which we are fetching the products. But this query will not work as the name of the table will be different because of the table prefix.
So In this case, we should generate the query by using table prefix dynamically:
global $wpdb;
$products_table = $wpdb->prefix . 'products';
$result = $wpdb->get_results($wpdb->prepare("SELECT * FROM $products_table where id=%d",$id));
Here,
$wpdb: prefix is giving the table prefix of the site. So generating table name dynamically through this will help to keep the query correct even on many sites with different table prefixes.