Posted in WordPress

Using wpdb in WordPress

Using wpdb in WordPress Posted on January 10, 2016Leave a comment

In this article, I will show you how to get started with the wpdb class, how to update/retrieve data from WordPress original tables and how to interact with your custom tables.

WordPress defines a class calledwpdb, which contains a set of functions used to interact with a database. Because this function is built into WordPress, there is no need to open a separate database connection.

Because methods in the wpdb class should not be called directly. We have to use the global $wpdb object instead! WordPress database has 12 (11 before vesion 4.4) tables by default. To interact with one of them, you can use this syntax: $wpdb->table_name, here are the name of tables:

  • posts.
  • comments.
  • links.
  • options.
  • postmeta.
  • terms.
  • term_taxonomy.
  • term_relationships.
  • termmeta.
  • commentmeta.
  • users.
  • usermeta.

To interact with custom tables. You can use this syntax:

Ok, so now, Let’s get into wpdb methods:

SELECT a Variable

The get_var function returns a single variable from the database. It returns NULL if no result is found

For example: Retrieve the number of posts.

SELECT a Row

To retrieve an entire row from a query, use get_row. The function can return the row as an object, an associative array, or as a numerically indexed array.

  • $query: (string) The query you wish to run.
  • $output_type: One of three pre-defined constants. Defaults to OBJECT.
    • OBJECT - result will be output as an object.
    • ARRAY_A - result will be output as an associative array.
    • ARRAY_N - result will be output as a numerically indexed array.
  • $row_offset: (integer) The desired row (0 being the first). Defaults to 0.

For example: Get all publish posts.

SELECT a Column

To SELECT a column, use get_col. This function outputs a one dimensional array.

For example: Get all user login names.

SELECT Generic Results

Generic, multiple row results can be pulled from the database with get_results. The function returns the entire query result as an array.

  • $query: (string) The query you wish to run.
  • $output_type: Defaults to OBJECT. See SELECT a Row for more information.

For example: Get ID, post title, post excerpt from posts.

Running General Queries

The query function allows you to execute any SQL query on the WordPress database.

For example: I have a custom table named wp_test, this table has two columns (ID and value), I want to delete a row that has ID equal 3.

Protect Queries Against SQL Injection Attacks

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. To do that, use preparefuncion.

  • $query: (string) The SQL query you wish to execute, with placeholders.
  • $value_parameter: (int|string|array) The value to substitute into the placeholder.

For example:

INSERT row

  • $table: (string) The name of the table to insert data into.
  • $data: (array) Data to insert (in column => value pairs).
  • $format: (array|string) (optional) An array of formats to be mapped to each of the values in $data.

For example:

UPDATE rows

  • $table: (string) The name of the table to update.
  • $data: (array) Data to update (in column => value pairs).
  • $where: (array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs.
  • $format: (array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.
  • $where_format: (array|string) (optional) An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where.

For example:

DELETE Rows

  • $table: (string) (required) Table name.
  • $where: (array) (required) A named array of WHERE clauses (in column -> value pairs). Multiple clauses will be joined with ANDs.
  • $where_format: (string/array) (optional) An array of formats to be mapped to each of the values in $where. If a string, that format will be used for all of the items in $where.

For example:

Very simple but important to know, thanks for your reading!

Leave a Reply

Your email address will not be published. Required fields are marked *