WordPress Database Functions

db

dbThe WordPress database class is quite extensive and provides a range of methods to work effectively with the database and thereby use the WordPress standard.

You can find the class in /wp-includes/wp-db.php where the individual methods are documented.
I show the most important ones and give some small examples. It is important to work with these opportunities to ensure the safety of Plugins.
For the following four methods I created some syntax examples.

insert($table, $data, $format) — insert a row into a table via arrays.
update($table, $data, $where, $format, $where_format) — update a row in a table via arrays.
get_var($query, $x, $y) — retrieve a single variable from the database.
query($query) — perform a MySQL database query with current connection
get_results($query, $output) — retrieve SQL result set from database… one or more rows.
escape($data) — Escapes content for insertion into the database using addslashes(), for security

Also interesting are the methods below.

set_prefix($prefix) — used to set table prefix for WordPress tables, can be used to override prefix at any time
prepare($query) — safely prepares an SQL query for execution with sprintf()-like syntax.
get_row($query, $output, $y) — retrieve a single row from the database.
get_col($query, $x) — retrieve a single column from the database in array format.
/**
* insert
*/
$wpdb->insert( $wpdb->posts, array( ‘post_title’ => $mytitle ) );

$wpdb->insert( $wpdb->options, array(
‘option_name’,
‘new_option_key’,
‘option_value’ => ‘New Option Value’,
‘autoload’ => ‘yes’ )
);

/**
* update
*/
$wpdb->update( $wpdb->posts, array( ‘post_title’ => $mytitle ),
array( ‘ID’ => $myid )
);

$wpdb->update( $wpdb->options,
array( ‘option_value’ => ‘New Option Value’ ),
array( ‘option_name’ => ‘new_option_value’ )
);

/**
* get_var
*/
$post_id = $wpdb->get_var(
$wpdb->prepare( “SELECT post_id FROM
$wpdb->postmeta WHERE
post_id = %d AND
meta_key = ‘enclosure’ AND
meta_value LIKE (%s)”, $post_ID, $url . ‘&’ )
);

$content = $wpdb->get_var(
$wpdb->prepare(“SELECT post_content FROM ” .
“$wpdb->posts WHERE ” .
“post_title = %s AND ” .
“ID = %d”, $title, $id )
);

/**
* query
*/
$wpdb->query( “DELETE FROM $wpdb->options WHERE option_name = ‘$name'” );

$wpdb->query( “UPDATE $wpdb->posts SET post_title = ‘$mytitle’ WHERE ID = $myid” );

/**
* query and escape
*/
$mytitle = $wpdb->escape( $mytitle );
$myid = absint( $myid );
$wpdb->query( “UPDATE $wpdb->posts SET post_title = ‘$mytitle’ WHERE ID = $myid” );

/**
* get_results
*/
$type = $wpdb->get_results( “SELECT post_type FROM ” .
“$wpdb->posts WHERE ID=$id” );