wpdb::get_col() │ public │ WP 0.71

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
	...
)
*/

// 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
)
*/

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' );

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
	}
}

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
	}
}