WordPress Database tools

  • WordPress Database Backup – On-demand backup of your WordPress database. Back up your blog today.
  • WP-Supercache – A modification of WP-Cache that produces static html files.
  • DB-cache – another caching plugin which claims your site will work much faster and will use less disk space for cached files.
  • WP-DBManager – Lets you to optimize database, repair database, backup database, restore database, delete backup database , drop/empty tables and run selected queries.
Advertisements

WordPress Database Functions

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

Connecting to a Microsoft Access Database

 Use the OLE DB .NET data provider.

 The solution creates and opens a connection to a Microsoft Access database using the OLE DB .NET data provider. Information about the connection is displayed.

Code:
using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectAccessDatabase
{
    class Program
    {
      static void Main(string[] args)
      {
          string oledbConnectString =
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            @"C:\Documents and Settings\bill\My Documents\" +
           "Northwind 2007.accdb;";
         using (OleDbConnection connection =new OleDbConnection
(oledbConnectString))
        {
          connection.Open(  );

     // Output some connection and database information.
        Console.WriteLine("Connection State:{0}",connection.State);
        Console.WriteLine("OLE DB Provider:{0}",connection.Provider);
        Console.WriteLine("Server Version:{0}",connection.ServerVersion);
        }
        Console.WriteLine("\nPress any key to continue.");
        Console.ReadKey(  );
      }
   }
}