WordPress WPDB Custom Queries For Custom Theme Or Plugin Development

With custom queries you can make any data reading and/or manipulation you want. Instantly a world of new possibilities open up. Query is a term used to describe the act of selecting, inserting, or updating data in a database. In WordPress, queries are used to access data from your MySQL database. WordPress is written using PHP and MySQL.

Each time you are viewing a WordPress page, there are MySQL queries running in the background to fetch the data from database. This data is then used to dynamically generate HTML for your browser. When users create, edit, or delete anything from WordPress, there are database queries that convert user input into instructions which are then executed by running database queries.

Why Use WordPress Custom Queries?

The basic functionalities in WordPress are fine for most simple needs, but what would you do if you want to implement some specific needs? Are you writing a plugin maybe? Then you should learn how you can use SQL queries in WordPress right now! The official references can be found in the WordPress Codex (Custom Queries and the WPDB class).

WordPress Column Queries:

<?php
////////////////////////
# COLUMN EXAMPLES
////////////////////////
	////////////////////////
	# Add Column (after)
	////////////////////////
	global $wpdb;
	$sql = "ALTER TABLE $wpdb->new_table ADD COLUMN `column_name` INT(1) NOT NULL AFTER `after_this_column`";
		
	////////////////////////
	# Delete Column
	////////////////////////
	global $wpdb;
	$sql = "ALTER TABLE $wpdb->new_table DROP column_name";

	////////////////////////
	# List Column names
	////////////////////////
	global $wpdb;
	$db_name = DB_NAME;
	$table_name = "$wpdb->new_table";
	$results = $wpdb->get_results(" SELECT COLUMN_NAME AS name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$db_name' AND TABLE_NAME = '$table_name'; ");
	$column_names = array();
	if(!empty($results)){
		foreach($results as $result){
			$column_names[] = $result->name;
		}
	}
	echo '<pre>';
	print_r($column_names);
	echo '</pre>';

?>

WordPress Row Queries:

<?php
////////////////////////
# ROW EXAMPLES
////////////////////////
	////////////////////////
	# Insert Row
	////////////////////////
	global $wpdb;
	$wpdb->insert( $wpdb->new_table, array('first_name' => 'Lars'), array('id' => 123) );
	$insert_id = $wpdb->insert_id;
	
	# - OR -
	global $wpdb;
	$wpdb->query(" INSERT INTO $wpdb->new_table (id) VALUES ('123') ");
	
	
	////////////////////////
	# Update Row
	////////////////////////
	global $wpdb;
	$wpdb->update( $wpdb->new_table, array('first_name' => 'Lars'), array('id' => 123) );

	# - OR -
	global $wpdb;
	$wpdb->query(" UPDATE $wpdb->new_table SET first_name = 'Lars' WHERE 1=1 AND ID = '123' ");
	

	////////////////////////
	# Delete Row
	////////////////////////
	global $wpdb;
	$wpdb->delete( $wpdb->table_name, array('id' => 123) );

	# - OR -
	global $wpdb;
	$wpdb->query(" DELETE FROM $wpdb->new_table WHERE 1=1 AND ID = '123' ");
	
	
	////////////////////////
	# Get Results
	////////////////////////
	global $wpdb;
	$results = $wpdb->get_results(" SELECT * FROM $wpdb->new_table WHERE 1=1 AND first_name = 'Lars' ");
	if(!empty($results)){
		foreach($results as $result){
			echo '<pre>';
			print_r($result);
			echo '</pre>';
		}
	}
	
	
	////////////////////////
	# Get Row
	////////////////////////
	global $wpdb;
	$result = $wpdb->get_row(" SELECT * FROM $wpdb->new_table WHERE 1=1 AND id = '123' ");
	
	
	////////////////////////
	# Count Rows
	////////////////////////
	global $wpdb;
	$count = $wpdb->get_var(" SELECT COUNT(*) FROM $wpdb->new_table WHERE 1=1 ");	
?>

WordPress Table Queries:

<?php
////////////////////////
# TABLE EXAMPLES
////////////////////////
	////////////////////////
	# Create Table
	////////////////////////
	global $wpdb;
	$sql = "
		CREATE TABLE IF NOT EXISTS {$wpdb->base_prefix}new_table (
			id INT NOT NULL AUTO_INCREMENT,
			first_name varchar(15) DEFAULT '' NOT NULL,
			last_name varchar(15) DEFAULT '' NOT NULL,
			`col` varchar(16) DEFAULT '' NOT NULL,
			date timestamp DEFAULT CURRENT_TIMESTAMP,
			last_update timestamp DEFAULT '0000-00-00 00:00:00',
			extra longtext DEFAULT '' NOT NULL,
			PRIMARY KEY(id)
		) ENGINE=MEMORY;
	";
	$wpdb->query($sql);
		
	///////////////////////////////////////
	# Add New Table to the $wpdb Object
	///////////////////////////////////////
	add_action( 'init', 'wpdb_add_new_table' );
	function wpdb_add_new_table() {
		global $wpdb;
		$wpdb->new_table = "{$wpdb->base_prefix}new_table";
	}
	
	////////////////////////
	# Empty Table
	////////////////////////
	global $wpdb;
	$sql = " TRUNCATE TABLE $wpdb->new_table ";
	$wpdb->query($sql);
	
	////////////////////////
	# Drop Table
	////////////////////////
	global $wpdb;
	$sql = " DROP TABLE $wpdb->new_table ";
	$wpdb->query($sql);
?>

Conclusion:

WordPress is not limited to creating simple websites, as it’s rapidly moving to a full-fledged application framework. Extending WordPress via custom post types and custom taxonomies should be our main priority. However, when we need finer control of our data, it’s reassuring to know that WordPress itself provides various functions and classes like wpdb for developers to utilize. This is what makes WordPress a mature solution.