How to transpose MYSQL db in PHP

Question

I am using PHP to query a MYSQL db to output data to a csv file.

I am currently able to query the db and export the data to the CSV file.

However i am unable to transpose the data so that the columns are rows, and rows are columns.

CODE:

 function transpose($array) {
             if (!is_array($array) || empty($array)) {
             return array();

             else {
                foreach ($array as $row_key => $row) {
                    if (is_array($row) && !empty($row)) { //check to see if there is a                  second dimension

                foreach ($row as $column_key => $element) {
                       $transposed_array[$column_key][$row_key] = $element;

        else {
               $transposed_array[0][$row_key] = $row;

                   return $transposed_array;

    }
}

    exportMysqlToCsv($tablename,$tokenmain, $id);
    function exportMysqlToCsv($tablename,$tokenmain, $id, $filename = 'Results.csv'){
        $sql_query = "select * from $tablename";

        // Gets the data from the database
        $result = mysql_query($sql_query);

        $f = fopen('php://temp', 'wt');
        $first = true;

        while ($row = mysql_fetch_assoc($result)) {

            if ($first) {

                fputcsv($f, array_keys($row));

                $first = false;
     }

      fputcsv($f, $row);

        } // end while

        $size = ftell($f);
        rewind($f);
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Content-Length: $size");

     // Output to browser with appropriate mime type, you choose ;)
        header("Content-type: text/x-csv");
        header("Content-type: text/csv");
        header("Content-type: application/csv");
        header("Content-Disposition: attachment; filename=$filename");

        fpassthru($f);

        exit;
    }

Somehow, those two functions should be intertwined to give me the output I need.

Any help would be greatly appreciated. thanks! -Merica

sounds like the underlying db sructure is wrong. whats the db schema and the desired csv output? — user557846
The db structure is wrong for the output I would like to see in a CSV file. For example, I have questions as the column headers and answers under each. In the output i want 2 column headers: Question, Answer with the questions in one column and answers in the adjacent column — Merica
well that sounds like it can be done purely in the select query. you need to post concrete examples to get the best help. — user557846

manuerumx manuerumx · Accepted Answer · 2012-10-22T20:29:37

Try this function:

function array_transpose($array, $selectKey = false) {
    if (!is_array($array)) return false;
    $return = array();
    foreach($array as $key => $value) {
        if (!is_array($value)) return $array;
        if ($selectKey) {
            if (isset($value[$selectKey])) $return[] = $value[$selectKey];
        } else {
            foreach ($value as $key2 => $value2) {
                $return[$key2][$key] = $value2;
            }
        }
    }
    return $return;
}


$fruits = array(
    array('id' => 1, 'name' => 'Apple', 'color' => 'Red'),
    array('id' => 2, 'name' => 'Orange', 'color' => 'Orange'),
    array('id' => 3, 'name' => 'Mango', 'color' => 'Yellow')
);
echo "<pre>";
print_r(array_transpose($fruits));
echo "</pre>";

Returns:

Array
(
    [id] => Array
        (
            [0] => 1
            [1] => 2
            [2] => 3
        )

    [name] => Array
        (
            [0] => Apple
            [1] => Orange
            [2] => Mango
        )

    [color] => Array
        (
            [0] => Red
            [1] => Orange
            [2] => Yellow
        )

)

How to transpose MYSQL db in PHP

1 Answers