WordPress Database Operation function Detailed

Through this $wpdb object, we can do any operation to the WordPress database, including build table, query, delete, update and so on. Note that if you want to use $wpdb in a custom function, you must first make it global (global $wpdb;).

The following is a detailed description of WordPress database operation function:

1. Function query ($query)

This function is the most basic database operation function, $query for SQL statements, submitted to the database execution, the results are divided into two cases:

1). If it is "Insert|delete|update|replace", returns the number of rows affected, in the case of "insert|replace", the function uses $THIS->INSERT_ID to record the newly inserted ID.

2). In the case of "select", the function uses $this->last_result to record the query to the result set, returning the number of records rows to the query.

If an error occurs, False is returned.

Instance:

<?php
$wpdb->query (
"
UPDATE $wpdb->posts
SET post_parent = 7
WHERE ID = Post_status = ' static '
"
);
?>

2. Function Escape ($data)

Use backslashes to reference data, which means using magic quotes.

Instance:

<?php
$name = $wpdb->escape ($name);
$email = $wpdb->escape ($email);
$wpdb->query ("INSERT into Myusers (ID, name, email) VALUES (NULL, ' $name ', ' $email ')");
?>

3. Function Insert ($table, $data, $format = null)

This is the Insert record function, the first parameter is an array of fields for the table, the second is an array of data, and the third is used to specify the data type for each value in $data. Insert Data returns 1, otherwise 0.

Instance:

<?  PHP
$wpdb->insert (
' Table ',
Array
' Column1 ' = ' value1 ',
' Column2 ' = 123
),
Array
'%s ',
'%d '
)
);
?>

4. Function Update ($table, $data, $where, $format = null, $where _format = null)

This is the update record function, the first parameter is the field array of the table, the second is the data array, the third is the condition array, and the fourth and fifth are used to specify the data type for each value in $data and $where. Updated to 1, otherwise 0.

Instance:

<?php
$wpdb->update (
' Table ',
Array
' Column1 ' = ' value1 ',//String
' Column2 ' = ' value2 '//Integer (number)
),
Array (' ID ' = 1),
Array
'%s ',//value1
'%d '//value2
),
Array ('%d ')
);
?>

5. Function Get_var ($query = null, $x = 0, $y = 0)

This function returns only one value, and the default is the No. 0 row, the No. 0 column. If $query is not empty, the query is executed first, and if $query is empty, it is selected from the cache, and then the value of row y of column x is returned.

Instance:

<?  PHP
$user _count = $wpdb->get_var ($wpdb->prepare ("SELECT count (*) from $wpdb->users;"));
echo "<p>user count is {$user _count}</p>";
?>

6. Function Get_row ($query = null, $output = OBJECT, $y = 0)

Returns a row, $output specifies the type returned, either array_a (associative array), array_n (numeric array), or object. The $y specifies the first few lines.

Instance:

By default, the value of $output is object, so use the following:

<?php
$mylink = $wpdb->get_row ("select * from $wpdb->links WHERE link_id = 10");
Echo $mylink->link_id; Prints "10"
?>

If the value of $output is array_a, then use the following:

<?php
$mylink = $wpdb->get_row ("select * from $wpdb->links WHERE link_id = ten", array_a);
echo $mylink [' link_id ']; Prints "10"
?>

If the value of $output is Array_n, then use the following:

<?php
$mylink = $wpdb->get_row ("select * from $wpdb->links WHERE link_id = ten", array_n);
echo $mylink [1]; Prints "10"
?>

7. Function Get_col ($query = null, $x = 0)

Returns a column $x the specified column.

Instance:

<?  PHP
$names = $wpdb->get_col ("Select name, email from myusers", 0)
foreach ($names as $name) {
Echo $name;
}
?>

8. Function Get_results ($query = null, $output = OBJECT)

Returns the result set of the query, which is allowed to be returned in Array_a, Array_n, or object three.

Instance:

<?php
$fivesdrafts = $wpdb->get_results (
"
SELECT ID, Post_title
From $wpdb->posts
WHERE post_status = ' draft '
and Post_author = 5
"
);

foreach ($fivesdrafts as $fivesdraft)
{
Echo $fivesdraft->post_title;
}
?>

9, Function get_col_info ($info _type = ' name ', $col _offset =-1)

Returns the field information.

Instance:

<?php
$col _name = $wpdb->get_col_info (' name ', 0);
echo $col _name;
?>

10. function prepare ($query = null)

This is a variadic function, that is, the parameter number of a function is indeterminate. The $query is a SQL statement that can contain placeholders like%s and%d, and% of all other non-placeholders should be replaced with a percent of zero. Because SQL statements may contain special characters such as single quotes and double quotation marks, it can lead to errors or security issues if they are submitted directly to the database without processing. To do this, we can preprocess The SQL statement through the PREPARE function. In fact, the function is very simple to use, just like the sprintf () and vsprintf () functions in the C language.

Usage:

<?php $sql = $wpdb->prepare (' query ' [, Value_parameter, Value_parameter ...]); ?>

Instance:

 
 <?php   
 $metakey     =  "Harriet ' adages"; 
 $metavalue   =  "WordPress ' data   base interface is like Sunday morning:easy. "; 
 
 $wpdb->query (  $wpdb->prepare (  
      
         INSERT into  $wpdb->postmeta 
         (post_id, Meta_key, Meta_value) 
     &NB Sp   VALUES ( %d, %s, %s ) 
     ",   
     10,  
      $metakey,   
      $metavalue   
)  ); 
? 
 

Of course, there are some infrequently used functions , which are not described in detail here. There are also two constants: Savequeries and Wp_debug. Set the Savequeries constant to True to save all queries executed in the database and their stop time to the $this->queries array, which can be used later when debugging, and set the Wp_debug constant to True, You can output errors. However, these two constant switches are not open by default, and we can turn them on in the wp_config.php file when testing.

WordPress Database Operation function Detailed