Count all rows in a table
/**
* Counts all rows in a table.
*
* For example: `$total_rows = wpcodebook_sql_count_table_rows( 'postmeta' );`
*
* @see https://wpcodebook.com/wordpress-sql-examples/
* @see https://developer.wordpress.org/reference/classes/wpdb/
* @see https://developer.wordpress.org/reference/classes/wpdb/get_row/
*/
function wpcodebook_sql_count_table_rows( $table_name ) {
global $wpdb;
$query = "SELECT COUNT(*) AS total_rows FROM {$wpdb->prefix}{$table_name}";
$res = $wpdb->get_row( $query );
return ( $res->total_rows ?? false );
}Count all distinct values in a table
/**
* Counts all distinct values in a table.
*
* For example: `$res = wpcodebook_sql_count_distinct( 'postmeta', 'meta_key' );`
*
* @see https://wpcodebook.com/wordpress-sql-examples/
* @see https://developer.wordpress.org/reference/classes/wpdb/
* @see https://developer.wordpress.org/reference/classes/wpdb/get_results/
*/
function wpcodebook_sql_count_distinct( $table_name, $key ) {
global $wpdb;
$query = "SELECT {$key}, COUNT(*) as count FROM {$wpdb->prefix}{$table_name} GROUP BY {$key} ORDER BY count DESC";
$res = $wpdb->get_results( $query );
$res = wp_list_pluck( $res, 'count', $key );
return $res;
}Example output:
Array
(
[_tax_class] => 2759
[_sold_individually] => 2759
[total_sales] => 2759
[_wc_review_count] => 2758
[_wc_average_rating] => 2758
[_stock_status] => 2758
[_download_expiry] => 2758
[_download_limit] => 2758
[_downloadable] => 2758
[_virtual] => 2758
[_stock] => 2757
[_product_version] => 2757
[_product_attributes] => 2601
[_price] => 2577
)Simple select
/**
* Selects rows in a table.
*
* For example: `$res = wpcodebook_sql_select( 'postmeta', 'meta_key', '_billing_%' );`
*
* @see https://wpcodebook.com/wordpress-sql-examples/
* @see https://developer.wordpress.org/reference/classes/wpdb/
* @see https://developer.wordpress.org/reference/classes/wpdb/get_results/
*/
function wpcodebook_sql_select( $table_name, $key, $value ) {
global $wpdb;
$query = "SELECT * FROM {$wpdb->prefix}{$table_name} WHERE {$key} LIKE '{$value}'";
$res = $wpdb->get_results( $query );
return $res;
}Example output:
Array
(
[0] => stdClass Object
(
[meta_id] => 94802
[post_id] => 2878
[meta_key] => _billing_first_name
[meta_value] => John
)
[1] => stdClass Object
(
[meta_id] => 108483
[post_id] => 3289
[meta_key] => _billing_address_1
[meta_value] => 1100 Saint-Catherine St W
)
[2] => stdClass Object
(
[meta_id] => 124698
[post_id] => 3976
[meta_key] => _billing_email
[meta_value] => john@example.com
)
[3] => stdClass Object
(
[meta_id] => 124697
[post_id] => 3976
[meta_key] => _billing_country
[meta_value] => CA
)
)