WordPress Database Examples

wpdb-column-examples.php

<?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>';

?>

wpdb-row-examples.php

<?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 ");	
?>

wpdb-table-examples.php

<?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);
?>