Skip to content

Instantly share code, notes, and snippets.

@jeffward3283
Last active December 25, 2023 20:19
Show Gist options
  • Save jeffward3283/f62b9869ac1bbae18855 to your computer and use it in GitHub Desktop.
Save jeffward3283/f62b9869ac1bbae18855 to your computer and use it in GitHub Desktop.
WordPress Database Examples
<?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>';
?>
<?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 ");
?>
<?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);
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment