10/21/2015 - 8:24 PM

WP CRUD custom DB example

WP CRUD custom DB example

// REF: https://laloah.wordpress.com/2013/06/14/basic-example-of-wordpress-plugin-to-select-update-insert-and-delete-from-database-crud/


init – plugin initialization, where everything is put toghether
list – showing a list of items
update – for updating and deleting items
create – for inserting new items
style-admin.css – stylesheet to use in the admin screens

How to use the code:

Download the files
Unzip to wp-content/plugins folder
Create the table manually on the same wordpress database using the file example-database.sql (you can use the phpmyadmin tool)
Activate the plugin

Customize the code

If yout take a look at the code you’ll see that every function has a prefix “sinetiks” and the name of the table “schools“. This is because you need to create a namespace to avoid duplicated function names.

How to modify the code to manage another table:

Replace “sinetiks” with your company name and “school” with the table name
Replace the columns ID and NAME with your columns
Modify the html forms


1) SELECT some information from a custom table called "mytable"
$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

2) SELECT a Variable, The get_var function returns a single variable from the database. Though only one variable is returned, the entire result of the query is cached for later use. Returns NULL if no result is found.
$wpdb->get_var( 'query', column_offset, row_offset );

(string) The query you wish to run. Setting this parameter to null will return the specified variable from the cached results of the previous query.

(integer) The desired column (0 being the first). Defaults to 0.

(integer) The desired row (0 being the first). Defaults to 0.

Retrieve and display the number of users.

$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users" );
echo "<p>User count is {$user_count}</p>";

Retrieve and display the sum of a Custom Field value.

// set the meta_key to the appropriate custom field meta key
$meta_key = 'miles';
$allmiles = $wpdb->get_var( $wpdb->prepare( 
		SELECT sum(meta_value) 
		FROM $wpdb->postmeta 
		WHERE meta_key = %s
) );
echo "<p>Total miles is {$allmiles}</p>";

Get all the information about Link 10.
$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );

The properties of the $mylink object are the column names of the result from the
SQL query (in this example all the columns from the $wpdb->links table, 
but you could also query for specific columns only).

echo $mylink->link_id; // prints "10"

In contrast, using
$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A );
would result in an associative array
echo $mylink['link_id']; // prints "10"