PHP webdeveloper and frontend developer




Running database queries in WordPress

Category : Web development, MySQL, Wordpress · by Feb 24th, 2013

WordPress has a built-in object class for dealing with database queries. It’s called wpdb and it’s found in the includes/wp-db.php file. When you’re running queries you should always use this class to execute them. To use this class you first need to define $wpdb as a global variable before you can use it. Just place this line of code before every $wpdb function:

 Within your wpdb functions you should use the prepare() function. This secures your query, preventing SQL injections.

Functions for running database queries

$wpdb->query – any query function

The $wpdb->query method is used mainly for SELECT and DELETE statements but can actually be used for any sql statement

 As you can see, we use the wp->custom_table to reference a custom table in our query. 

$wpdb->get_var – return single variable

The get_var() function is used for retrieving a single variable from the database

 This will return the number of posts.

$wpdb->get_row – return single row

To retrieve an entire row you’ll use the get_row() function. By default the row is returned as an object, but you can add the ARRAY_A or ARRAY_N parameter at the end of your function. The ARRAY_A parameter will return an associative array and the ARRAY_N parameter will return a numerically index array

$wpdb->get_results – return multiple rows

To retrieve multiple rows from a table you’ll use the $wpdb->get_results() function.

 This query will retun all the published posts as an object.

$wpdb->insert – Insert query

The are specific wordpress wpdb functions for an insert or update sql. The basic usage of the insert() function

 An example of an insert sql query:

 You first need to set your table and then pass your field values in an array. No need to use the prepare function here. I’ll explain why in the update statement query.

$wpdb->update – update query

We do a update query in a similar fashion. We first set our table, then pass our field values in an array and then set our where clause variable.

 The where variable is an array of field names and values for the where clause.  In this example we’ll use a unique ID in our where clause.

Both the insert() and update() do not need to use the prepare() function because both these functions use the prepare function internally.  

 

Useful database functions

$wpdb->num_rows – number of rows returned

We use the num_rows() function to return the number of rows returned by a sql query.

 $wpdb->print_error() – display sql errors

 When dealing with custom functions it can be hard to debug what’s wrong with your sql. The print_error() function will display the errors of your sql to the page

 The show_errors() function must be called before executing a sql query. The print_error() function directly after running the query.

$wpdb->last_query – print last query executed

The last_query() function will print the last sql query executed. This can be useful to test why a query isn’t working as expected.

SHARE :

(6) comments

satyendra
6 years ago · Reply

i want know how to insert multiple value like
insert table(id,name)values(1,a)(2,b)

Steven Dobbelaere
6 years ago · Reply

@satyendra as mentioned you just insert an array of values

$wpdb->insert( $wpdb->custom_table, array(“firstname” => $firstname, “lastname” => $lastname ));

Rohit
5 years ago · Reply

Dear , thanks for the information.

 

I am struggling to find out , how to put the code in page. Code isnot getting executed. I have installed php-exec plugin also but no help !!

 

Kindly help to suggest where shoudl i make changes to execute above code in pages. Idea is to display data fetched from tables on pages.

MidriX
4 years ago · Reply

Add your code in a functions.php file which lives in your theme folder

hgfgh
3 years ago · Reply

I want how to upload multiple value select form database

Syed Qasim
9 months ago · Reply

Can you please let us know that how can we upload multiple values wpdb?

Leave a Reply

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


s