WP CRUD custom DB example
<?php
// REF: https://laloah.wordpress.com/2013/06/14/basic-example-of-wordpress-plugin-to-select-update-insert-and-delete-from-database-crud/
Files:
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
WORDPRESS DATABASE CLASS REFERENCE:
http://codex.wordpress.org/Class_Reference/wpdb
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 );
query
(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.
column_offset
(integer) The desired column (0 being the first). Defaults to 0.
row_offset
(integer) The desired row (0 being the first). Defaults to 0.
Examples
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
",
$meta_key
) );
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"