Checking while loop key exists in seperate array

Question

Having checked a variety of questions but not being able to find quite what I need, I am at a bit of a loss.

I am trying to chose the columns from MySQL I want exported to CSV by parsing the column names and adding the valid column names to a $colnames array, then adding those values as headers to the CSV and then only displaying the relevant data from the database through a while loop.

I have looked at the following in particular having been guided there from other questions: How to get all the key in multi-dimensional array in php

Here is the code:

function query_to_csv($query, $filename, $attachment = false, $headers = true, $specs_off = false) {

    if($attachment) {
        // send response headers to the browser
        header( 'Content-Type: text/csv; charset=UTF-8' );
        header( 'Content-Disposition: attachment;filename='.$filename);
        $fp = fopen('php://output', 'w');
    } else {
        $fp = fopen($filename, 'w');
    }

    $result = mysql_query($query) or die( mysql_error() );

    if($headers) {
        // output header row (if at least one row exists)
        $row = mysql_fetch_assoc($result);
        if($row) {

            // PRODUCTS TABLE SPECIFIC - get rid of specs_ and free_ columns so have nicer data set for user
            if($specs_off) { 

                $columnames = array_keys($row);   
                $colnames = array(); 
                //$colnames = array_keys($row);

                foreach($columnames as $key => $value) {

                    if((substr_count($value, "spec_") < 1) && (substr_count($value, "free_") < 1))  {
                        array_push($colnames, $value);                                  
                    }

                }
            }
            else {
                $colnames = array_keys($row);
            }

            // add in additional columns if exporting client data  
            if($table == 'clients') {array_push($colnames, "products", "last_order_date");}

            //write the colnames to the csv file
            fputcsv($fp, $colnames);

            // reset pointer back to beginning
            mysql_data_seek($result, 0);
        }
    } // done with the headers etc, now lets get on with the data


    // clear out and create the $row_data array 
    $row_data = array(); 

    // run through the row array adding values to row_data as we go 
    while($row = mysql_fetch_assoc($result)) {

        // create the array_keys_multi from https://stackoverflow.com/questions/11234852/how-to-get-all-the-key-in-multi-dimensional-array-in-php/11234924#11234924
        function array_keys_multi(array $array) {
            $keys = array();

            foreach ($array as $key => $value) {
                $keys[] = $key;

                if (is_array($array[$key])) {
                    $keys = array_merge($keys, array_keys_multi($array[$key]));
                }
            }

            return $keys;

        }

        // run the function on the $row array
        array_keys_multi($row); 

        // now use the $keys array  
        foreach($keys as $key => $value) {

             // check if the value is in the colnames array and if so push the data on to the $row_data array ready for export to CSV
             if(in_array($value, $colnames)) {
                 array_push($row_data, $row[$value]);
             }
        }

        // now we are ready to write the CSV
        fputcsv($fp, $row_data);

    }

    fclose($fp);
    exit;

} // end of query_to_csv

// Write the sql statement
$sql = "SELECT * FROM ".$table." "; 
if(isset($where_1_col)) { $sql .= " WHERE `".$where_1_col."` = '".$where_1_val."'"; }
if(isset($where_2_col)) { $sql .= " AND `".$where_2_col."` = '".$where_2_val."'"; }
if(isset($where_3_col)) { $sql .= " AND `".$where_3_col."` = '".$where_3_val."'"; }
if(isset($where_4_col)) { $sql .= " AND `".$where_4_col."` = '".$where_4_val."'"; }
if(isset($order_by_col)) { $sql .= " ORDER BY `".$order_by_col."` ". strtoupper($order_by_dir) ." "; }

// output as an attachment
query_to_csv($sql, $table."_export.csv", true, true, true);

All I am getting is a huge export of the chosen column names repeated as many times as there are values from the initial query. I don't know how to get the values in.

Any suggestions on where I am going wrong or how I can undertake this more neatly are welcomed.

Answer 1

It seems that you just append the new row data to $row_data but never clear that array.

array_push($row_data, $row[$value]);

What I did to fix it:

Move

// clear out and create the $row_data array 
$row_data = array(); 

into the while loop.

Change

// clear out and create the $row_data array 
$row_data = array(); 
while($row = mysql_fetch_assoc($result)) {
...
}

To

while($row = mysql_fetch_assoc($result)) {
// clear out and create the $row_data array 
$row_data = array(); 
...
}

Note:
You are using $table everywhere but never define it. Like here if($table == 'clients')
If it is a global var you need to add global $table or a parameter to your function, too.

Edit:
As mentioned in my comment on your question you could just use array_keys() to get the keys. php.net/manual/de/function.array-keys.php
And then change

array_keys_multi($row); 

to

$keys =  array_keys($row);

After that you can remove array_keys_multi()

Further you could move that part in front of your while-loop because you only need to calculate the column names you need once and not in every iteration.