wpdb::get_col()publicWP 0.71

Retrieves one column from the database.

Executes a SQL query and returns the column from the SQL result. If the SQL result contains more than one column, the column specified is returned. If $query is null, the specified column from the previous SQL result is returned.

Method of the class: wpdb{}

No Hooks.

Return

Array. Database query result. Array indexed from 0 by SQL result row number.

Usage

global $wpdb;
$wpdb->get_col( $query, $x );
$query(string|null)
SQL query.
Default: previous query
$x(int)
Column to return. Indexed from 0.

Examples

0

#1 An example of how the method works and what it returns

Let's say we have such a request to get all the revisions:

// let's specify one field in the query
$revision_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_type = 'revision'" );

// specify all fields in the query
$revision_ids = $wpdb->get_col( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision'" );

/*
In both cases the same array will be returned.
When all fields are specified, get_col gets only the first one, which is the ID

Array(
	[0] => 106
	[1] => 101
	[2] => 102
	[3] => 103
	...
)
*/
0

#2 Using without a $query parameter

// first let's query
$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision' LIMIT 10" );

// now let's get the data of this query
$revision_ids = $wpdb->get_col( null );
$revision_names = $wpdb->get_col( null, 5 ); // 5 - post_title

/*
Array
(
	[0] => 9949
	[1] => 9957
	[2] => 10125
	[3] => 10154
	[4] => 10221
	[5] => 10235
	[6] => 10319
	[7] => 10496
	[8] => 10532
	[9] => 10568
)
Array
(
	[0] => wp_tempnam
	[1] => Page templates for post types in WP 4.7
	[2] => Privacy Policy
	[3] => walker_nav_menu_start_el
	[4] => Pagination output
	[5] => enter_title_here
	[6] => Smart Custom Fields - a simple meta-fields plugin
	[7] => register_post_type
	[8] => About site
	[9] => REST API in WordPress
)
*/
0

#3 NULL instead of an empty string

This method returns NULL as field value if the value is an empty (string) (I checked with WP 5.1.1). But if we need to get the original data (empty string), we can use this substitution get_col()

$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision'" );

$ids = wp_list_pluck( $wpdb->last_result, 'ID' );
0

#4 Selecting a column from the query results

For this example, let's pretend we have a blog about cars. Each post describes some car (for example, a 1969 Ford Mustang). For each post there are 3 custom fields: manufacturer, model, and year. This example will display the titles of the posts filtered by manufacturer (ford) and sorted by model and year.

get_col is used here to get an array of all post IDs that satisfy certain criteria and are sorted in the right order. Then we use the foreach loop to output the headers according to the IDs we have:

<?php
$meta_key1 = 'model';
$meta_key2 = 'year';
$meta_key3 = 'manufacturer';
$meta_key3_value = 'Ford';

$postids = $wpdb->get_col( $wpdb->prepare("
SELECT      key3.post_id
FROM        $wpdb->postmeta key3
INNER JOIN  $wpdb->postmeta key1
			on key1.post_id = key3.post_id
			and key1.meta_key = %s
INNER JOIN  $wpdb->postmeta key2
			on key2.post_id = key3.post_id
			and key2.meta_key = %s
WHERE       key3.meta_key = %s
			and key3.meta_value = %s
ORDER BY    key1.meta_value, key2.meta_value",$meta_key1, $meta_key2, $meta_key3, $meta_key3_value) );

if( $postids ){

	echo 'List of ' . $meta_key3_value . '(s), sorted by ' . $meta_key1 . ', ' . $meta_key2;

	foreach( $postids as $id ){
		$post = get_post( $id );
		setup_postdata( $post );
		?>
		<p>
			<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>
		</p>
		<?php
	}
}
0

#5 List of posts that have a certain custom field (Color)

But they are sorted by the value of another custom field (Display_Order).

<?php
$meta_key1 = 'Color';
$meta_key2 = 'Display_Order';

$postids = $wpdb->get_col($wpdb->prepare("
SELECT      key1.post_id
FROM        $wpdb->postmeta key1
INNER JOIN  $wpdb->postmeta key2
			on key2.post_id = key1.post_id
			and key2.meta_key = %s
WHERE       key1.meta_key = %s
ORDER BY    key2.meta_value+(0) ASC",
		 $meta_key2,$meta_key1));

if( $postids ){
	echo 'List of '. $meta_key1  . ' posts, sorted by ' . $meta_key2 ;

	foreach ($postids as $id) {
		$post = get_post( $id );
		setup_postdata( $post );
		?>
		<p>
			<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>
		</p>
		<?php
	}
}

Changelog

Since 0.71 Introduced.

wpdb::get_col() code WP 6.5.3

public function get_col( $query = null, $x = 0 ) {
	if ( $query ) {
		if ( $this->check_current_query && $this->check_safe_collation( $query ) ) {
			$this->check_current_query = false;
		}

		$this->query( $query );
	}

	$new_array = array();
	// Extract the column values.
	if ( $this->last_result ) {
		for ( $i = 0, $j = count( $this->last_result ); $i < $j; $i++ ) {
			$new_array[ $i ] = $this->get_var( null, $x, $i );
		}
	}
	return $new_array;
}