How to create customize mysql query in wordpress instead of using hooks

Posted on by

Hello guys some time we are facing problem for making logical relation which is not possible by using word press predefined functions. In this article I guide how to write query in wordpress.

WordPress defines a class called wpdb, which contains a set of functions used to interact with a database. Its primary purpose is to provide an interface with the WordPress database, but can be used to communicate with any other appropriate database

Using the $wpdb Object

$wpdb is a global variable using the global keyword.Which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php.

// 1st Method - Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object

global $wpdb;
$results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

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.

<?php $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. 

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

SELECT a Row

<?php $wpdb->get_row('query', output_type, row_offset); ?>
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.

Example

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );

INSERT row

Insert a row into a table.

<?php $wpdb->insert( $table, $data, $format ); ?>

table
(string) The name of the table to insert data into.
data
(array) Data to insert (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped).
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. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

Examples

Insert two columns in a row, the first value being a string and the second a number:

$wpdb->insert( 
	'table', 
	array( 
		'column1' => 'value1', 
		'column2' => 123 
	), 
	array( 
		'%s', 
		'%d' 
	) 
);

REPLACE row

Replace a row in a table if it exists or insert a new row in a table if the row did not already exist. 

<?php $wpdb->replace( $table, $data, $format ); ?> 

table
(string) The name of the table to replace data in.
data
(array) Data to replace (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped).
format
(array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

Examples

Replace a row, the first value being the row id, the second a string and the third a number:

$wpdb->replace( 
	'table', 
	array( 
                'indexed_id' => 1,
		'column1' => 'value1', 
		'column2' => 123 
	), 
	array( 
                '%d',
		'%s', 
		'%d' 
	) 
);

UPDATE rows

Update a row in the table. Returns false if errors, or the number of rows affected if successful.

 <?php $wpdb->update( $table, $data, $where, $format = null, $where_format = null ); ?> 

table 
(string) The name of the table to update.
data 
(array) Data to update (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped). This means that if you are using GET or POST data you may need to use stripslashes() to avoid slashes ending up in the database.
where 
(array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be “raw”.
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.

Possible format values: %s as string; %d as integer (whole number) and %f as float.

Return values: This function returns the number of rows updated, or false if there is an error. Keep in mind that if the $data matches what is already in the database, no rows will be updated, so 0 will be returned. Because of this, you should probably check the return with false === $result

Examples

Update a row, where the ID is 1, the value in the first column is a string and the value in the second column is a number: 

$wpdb->update(   	
'table',   	
array(   		
'column1' => 'value1', // string  		
'column2' => 'value2' // integer (number)   	
),   	array( 'ID' => 1 ),   	
array(   		
'%s',	// value1  		
'%d'	// value2  	
),   	
array( '%d' )   );

Attention: %d can’t deal with comma values – if you’re not using full numbers, use string/%s.

DELETE Rows

The delete function was added in WordPress 3.4.0, and can be used to delete rows from a table. The usage is very similar to update and insert. It returns the number of rows updated, or false on error.

Usage

 <?php $wpdb->delete( $table, $where, $where_format = null ); ?>

Parameters

$table
(string) (required) Table name.

Default: None

$where
(array) (required) A named array of WHERE clauses (in column -> value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be ‘raw’.

Default: None

$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. A format is one of ‘%d’, ‘%f’, ‘%s’ (integer, float, string; see below for more information). If omitted, all values in $where will be treated as strings unless otherwise specified in wpdb::$field_types.

Default: null

Examples

// Default usage.
$wpdb->delete( 'table', array( 'ID' => 1 ) );

// Using where formatting.
$wpdb->delete( 'table', array( 'ID' => 1 ), array( '%d' ) );

Source