Using wordpress function to retrieve data

Question

I have been working on open source plugin and trying to add a new feature to it. For now I have been doing like this

else if( $selected_report == "greport" ) {

    $sql = mysql_query( "SELECT rating_postid FROM {$wpdb->ratings}" ) or die( mysql_error() );
}

generate_csv( $sql );
exit;

function generate_csv( $sql ) { 

    header( 'Content-Type: text/csv; charset=utf-8' );
    header( 'Content-Disposition: attachment; filename=data.csv' ); 


    $row = mysql_fetch_assoc( $sql );
    $fp = fopen( 'php://output', 'w' );

    if( $row ) { 
        fputcsv( $fp, array_keys( $row ) ); 
        mysql_data_seek( $sql, 0 ); 
    }

    while( $row = mysql_fetch_assoc( $sql ) ) {
        fputcsv( $fp, $row );
    }   

    fclose( $fp );
}

This works perfectly but I have been told to use WordPress functions in order to get the data. I changed the query to something like this

$sql = $wpdb-> get_results( "SELECT rating_id FROM {$wpdb->ratings}" );

This is creating problems with downloading function as it does not recognize sql_fetch_assoc. I tried to just iterate the values which also didn't work. This is the first time I am working with WordPress. Any suggestions would be a great help.

Answer 1

By default $wpdb->get_results() returns results in the form of an array of objects. If you want an array of associative arrays instead, just do this:

$results = $wpdb->get_results( "...", ARRAY_A );

Obviously I'm not familiar with your project or the code you're using, and I haven't tested this, but you'd probably want to do something like the following:

$results = $wpdb->get_results(
    "SELECT rating_postid FROM {$wpdb->ratings}",
    ARRAY_A
);

if ( ! empty( $results ) ) {

    header( 'Content-Type: text/csv; charset=utf-8' );
    header( 'Content-Disposition: attachment; filename=data.csv' );

    $fp = fopen( 'php://output', 'w' );

    fputcsv( $fp, array_keys( $results[0] ) );

    foreach ( $results as $row ) {
        fputcsv( $fp, $row );
    }

    fclose( $fp );

}

Answer 2

You should never use any mysql functions in wordpress. When you call wordpress functions, you will get the data directly returned to you. For eg.

$values = $wpdb->get_col( "SELECT rating_id FROM {$wpdb->ratings}" );
foreach($values as $value) {
    // $value is the exact value as stored in the mysql database table
}

For more details, refer https://codex.wordpress.org/Class_Reference/wpdb