Dev Notes

Software Development Resources by David Egan.

Get Column Names from a WordPress Database Table


MySQL, PHP, WordPress
David Egan

Get a comma-separated string of all column names (Field names) for a given MySQL table.

Useful when working with custom tables.

Method 1: Using $wpdb->get_col()

<?php
// An array of Field names
$existing_columns = $wpdb->get_col("DESC {$table}", 0);

// Implode to a string suitable for inserting into the SQL query
$sql = implode( ', ', $existing_columns );

Method 2: Using $wpdb->get_results()

<?php
$cols_sql = "DESCRIBE $table";
$all_objects = $wpdb->get_results( $cols_sql );
$existing_columns = [];
foreach ( $all_objects as $object ) {
  // Build an array of Field names
  $existing_columns[] = $object->Field;
}
$sql = implode( ', ', $existing_columns );

Example

<?php
namespace Company\Project\Includes;
class Get {
  public static function rows( $table, $fields = 'all' ) {
    global $wpdb;
    $table = $wpdb->prefix . $table;

    // Build an array of all field names for this table
    // -------------------------------------------------------------------------
    $existing_columns = $wpdb->get_col("DESC {$table}", 0);
    $sql = implode( ', ', $existing_columns );
    if ( 'all' === $fields ) {
      return $wpdb->get_results( "SELECT $sql FROM {$table}" );
    }
    // Build a query for specific fields if these are passed in to the function
  }
}

Note that the implode() function can accept parameters in any order. Probably best to use it as shown, to maintain consistency with explode().

Resources

comments powered by Disqus