Trying to export MySQL query as CSV using PHP

Question

I'm getting a warning

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, array given

when trying to export a MySQL query result as CSV using PHP code like this:

public static function exportLocationCSV($id){
        $sql =<<<EOF
            SELECT col1, col2, col3
            FROM table1
            JOIN table2 ON table1.col0=table2.col0 
            WHERE table1.col0 = $id;
EOF;


        // output headers so that the file is downloaded rather than displayed
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=data.csv');

        // create a file pointer connected to the output stream
        $output = fopen('php://output', 'w');

        // output the column headings
        fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

        $query_export= self::$db_connection->query($sql);
        $rows = array();
        while($r = mysqli_fetch_assoc($query_export)){
            $rows[] = $r;
        }

        // loop over the rows, outputting them
        while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
}

What does self::$db_connection->query() function look like? Is it a custom function that you created ? — Maximus2012
– Maximus2012, Commented Sep 4, 2015 at 20:55
You might also want to try var_dump($query_export) to see what you get. — Maximus2012
– Maximus2012, Commented Sep 4, 2015 at 20:56

Jay Blanchard · Accepted Answer · 2015-09-08 15:57:19Z

1

You're mixing mysql_* and mysqli_* API's. mysql_fetch_assoc() will not work with the mysqli_ API. Your second while loop is the one using the wrong function call.

This line:

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

should be change to the correct function call:

// loop over the rows, outputting them
foreach($rows AS $row) fputcsv($output, $row);

edited Sep 8, 2015 at 15:57

answered Sep 4, 2015 at 21:24

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

plzhelp Over a year ago

It says WARNING: mysqli_fetch_assoc expects parameter 1 to be mysqli_result, array is given

Jay Blanchard Over a year ago

Because you're not using a query resource for your second loop (you're using an array) you do not need to use mysqli_fetch_assoc() at all. You should likely use a foreach() loop there @plzhelp Apologies for not noticing that earlier.

Amr · Accepted Answer · 2015-09-04 20:59:02Z

-1

mysql_fetch_assoc used to fetch a result row as an associative array here is more about mysql_fetch_assoc

what you are trying to do is to pass $rows which is array while mysql_fetch_assoc expecting you to pass mysql_query

answered Sep 4, 2015 at 20:59

Amr

1771 gold badge2 silver badges15 bronze badges

Collectives™ on Stack Overflow

Trying to export MySQL query as CSV using PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related