PHP create a complex CSV file

Question

I am in need to create a CSV file getting the data from a mySQL DB.

The fact is that I want the CSV tp be corrected labeled and not just writing the data like this:

id,name,url
1,thisismyname,thisismyurl

I need the CSV file to look well ordered and each data inserted in the relative column.

Also with the function I am going to add below I can only grab the data from the DB and write it to the CSV file as it is. But I need to work with the data and have the CSV labeled in this way:

Campaign Name:
Name of the campaign

Campaign Url:
Url of the campaign

Tot visits:
Tot of visits

Tot unique visits:
Tot of unique visits

id     name         url
1      thisname     this url
2      thisname     this url
3      thisname     this url
4      thisname     this url
5      thisname     this url

This is the PHP code I have so far..I need to understand how to achieve a correct structure of the CSV with PHP and adding the lines in it the exact way I want..

Thanks for your help!

function genCSV($filename, $attachment = true, $headers = true) {
    // send response headers to the browser
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename=' . $filename);
    $fp = fopen('php://output', 'w');

    $query = "SELECT * FROM campaigns";
    $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) {
            fputcsv($fp, array_keys($row));
            // reset pointer back to beginning
            mysql_data_seek($result, 0);
        }
    }

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

    fclose($fp);
}

Try to let mysql to do the work for you: `SELECT ... INTO OUTFILE ...`, save the result in a location reachable by your application, take a look here: http://stackoverflow.com/questions/1119312/mysql-export-into-outfile-csv-escaping-chars#1197023 — Tom Regner, Jun 06 '13 at 10:30
Your question heavily depends on how your database is structured. What kinds of columns does the table `campaigns` have? Are other tables neccessary to gather the required information that you like to output? — Hauke P., Jun 06 '13 at 10:41

score 2 · Accepted Answer · answered Jun 06 '13 at 11:26

Here is a much less elegant solution than the one proposed by @Tom Regner.

I needed to backup certain database tables (all those with a given prefix) but not others. This method, though somewhat slow, allows you to select exactly which tables and which columns from those tables are copied. It was originally written to allow each piece of data to be AES encrypted before being entered into the file but there are other uses for it. As written here, the result is a CSV file with the first line containing the list of columns for the table and the rest containing the data in CSV. It will stand adaptation to output the result of any sql into CSV, if you like.

Obviously: mysqlidb = mysqli databse resource, backups/ = directory to put finished files in.

FWIIW, here is the code:

$sql="SHOW TABLES LIKE 'yourtable%'";
$result = $mysqlidb->query($sql);
$tableresult=$mysqlidb->query($sql);
while($tables=$tableresult->fetch_assoc())
{
$keys=array_keys($tables);
$tablename=$tables[$keys[0]];
echo "Writing $tablename <BR>";
$file=fopen("backups/$tablename.enc","w");
$cols=array();
$sql="SHOW COLUMNS FROM $tablename";
$result=$mysqlidb->query($sql);
while($row=$result->fetch_assoc())
    {
    $cols[]=$row['Field'];
    }
fputcsv($file,$cols);
$sql="SELECT * FROM $tablename";
$result=$mysqlidb->query($sql);
while($row=$result->fetch_assoc())
    {
    fputcsv($file,$row);
    }
fclose($file);

}

PHP create a complex CSV file

1 Answers1