Export data from SQL to CSV with modified format

Question

Suppose I have a table in SQL named 'myTable'. It contains 3 columns; 'id', 'lat' and 'lng'

|  id  |  lat   |  lng   |
|------+--------+--------|
|  1   |  1.11  |  1.22  |
|  2   |  2.11  |  2.22  |
|  3   |  3.11  |  3.22  |
|  4   |  4.11  |  4.22  |
| .... |  ....  |  ....  |

I want to export it to CSV. I expect the result become like this in the CSV file :

|     |  A   |  B   |   C   |   D  |  ....
------+-----+------+--------+------+--------
|  1  | 1.11 | 2.11 | 3.11  | 4.11 |  ....      //contains 'lat'
|  2  | 1.22 | 2.22 | 3.22  | 4.22 |  ....      //contains 'lng'

Can you help me? I'm using PHP. Thanks a lot.

Beginners are welcome on Stackoverflow but we expect you have done some [search/research](https://stackoverflow.com/help/how-to-ask) yourself as Stackoverflow isn't a free coding service but a question-answering service your question does not show anny attemps you have tryed.. Also i advice you to read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) — Raymond Nijland, Jun 11 '19 at 13:06
I reopened the question because the duplicate links given were not, IMHO, specific enough. If someone can provide an exact duplicate of pivoting into a very large number of columns, I am happy to reclose. — Tim Biegeleisen, Jun 11 '19 at 13:12

score 0 · Accepted Answer · answered Jun 11 '19 at 13:11

This might actually be something which would be best handled in your PHP script, rather than in MySQL. Assuming your are using mysqli, you could try something like this:

$sql = "SELECT id, lat, lng FROM yourTable ORDER BY id";
$result = $conn->query($sql);

$row1 = NULL;
$row2 = NULL;

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        if ($row1 != NULL) {
            $row1 .= ",";
            $row2 .= ",";
        }
        $row1 .= $row["lat"];
        $row2 .= $row["lng"];
    }
}

At the end of the loop in the above script, $row1 and $row2 should contain the first two rows of the output file you expect. The only steps missing might be the header, if you want that, and also the details of how to write a string to a text file.

@MarinaRamosa I suspected you would ask that, but providing full code for how to write a CSV file in PHP would be too broad for your question, I think. If you can't figure out how to do this, I suggest opening a new question. — Tim Biegeleisen, Jun 14 '19 at 08:02

Export data from SQL to CSV with modified format

1 Answers1