Export to CSV changes in PHP

Question

I am working on Export to CSV in PHP. I have code that works fine it gives me output in ExcelSheet as I want. Code snippet:

public function generate_csv() {
 $data_rows = array();
   $table = 'ProductDetails';
    $data_rows = array();
    global $wpdb, $bp;
    $data= $wpdb->get_results("SELECT * FROM " . $table . "");
    $fh = @fopen( 'php://output', 'w' );

foreach ($data as $u ) {
        $row = array();
        $row[0] = $u->productCode;
        $row[1] = $u->productTitle;
        $row[2] = $u->productDescription;
        $row[3] = $u->specification;
        $row[4] = $u->whereToBuy;       
        $data_rows[] = $row;
    }

  header("Pragma: public");
      ... Some more header ...
  header("Content-Transfer-Encoding: binary");

  fputcsv( $fh, $header_row );
  foreach ( $data_rows as $data_row ) {
         fputcsv( $fh, $data_row );
  }
   fclose( $fh );
   die();
 } 

As you can see in code I am hard coding all column names and creating array. The problem is if phpMyAdmin add/remove column in database then to get perfect ExcelSheet necessary changes need to make in this code also. Can any one please help me to make this code dynamic.? Like what should be instead of $row[0], $row[1], $row[2].... ??

Thank You

Answer 1

More global approach is to use double foreaches

 $data_rows=array();
    foreach ($data as $u ) {
        $row = array();
        foreach ($u as $field)
            {
            $row[] = $field; // collect dynamic row fields

            }
    $data_rows[] = $row;  // each row will have own array of fields
    }

/// EDITED

public function generate_csv($table) // better to have table name here
{
 $data_rows = array();
    $data_rows = array();
    global $wpdb, $bp;
    $sql = "SELECT * FROM " . $table . "";

    $data= $wpdb->get_results($sql);    
    $fh = @fopen( 'php://output', 'w' );


    //following the example from: https://stackoverflow.com/a/31068464/1171074
    $header_data=array(); 
    foreach ( $wpdb->get_col( "DESC " . $table, 0 ) as $column_name ) {
        $header_data[] = $column_name; 
    }

    array_push($data_rows,$header_data); // first array will be columns names

     foreach ($data as $u ) {
        $row = array();
        foreach ($u as $field)
            {
            $row[] = $field; // collect dynamic row fields

            }
    $data_rows[] = $row;  // each row will have own array of fields
    }

    ............  // rest of the code

    }

Answer 2

You can use the virtual INFORMATION_SCHEMA.COLUMNS table to get the column names, like so:

"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = {$table}"

Answer 3

This should get you pretty close, if not all the way there. It will query the table, build a headers row for the csv, then it will assemble each data row. You shouldn't need to know the row name, if you iterate the response row as value..

I apologize up front if it's a little buggy, since I don't have a PHP box handy where I'm at to verify the precise syntax.

public function generate_csv() {

    global $wpdb;
    global $bp;
    $headers   = array();
    $data_rows = array();
    $table     = 'ProductDetails';
    $data_rows = array();
    $header_row;

    # determine table field names
    $table_sql = sprintf("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '%s'",$table);

    # this will run the query and stuff field names into
    $resp = $wpdb->get_results($table_sql);
    foreach($resp as $row){
        array_push($headers,$row[0]);
    }

    # get the records from the datbase
    $data= $wpdb->get_results(sprintf("SELECT * FROM %s",$table));

    # open output handle
    $fh = @fopen( 'php://output', 'w' );

    foreach ($data as $record ) {
       $row = array();
       foreach($record as $value){
           array_push($row,$value);
       }
       array_push($data_rows,$row);
    }
    header("Pragma: public");
      ... Some more header ...
    header("Content-Transfer-Encoding: binary");

    fputcsv( $fh, $headers );
    foreach ( $data_rows as $data_row ) {
        fputcsv( $fh, $data_row );
    }
    fclose( $fh );
    return;
 }