MySQL to CSV export

[_spaz]

Is it possible to export SQL results from multiple tables to CSV format?  I have a query that joins multiple tables in a database and I would like it to export that data to CSV, here's my SQL query:

 

SELECT MediaFile.id, MediaFile.filename, MediaFile.createtime, JobMediaFile.starttime, JobMediaFile.endtime, JobMediaFile.progress, Job.name, SummaryAlert.title, FileStatus.status

FROM MediaFile

LEFT JOIN JobMediaFile ON JobMediaFile.mediafile_id = MediaFile.id

LEFT JOIN Job ON JobMediaFile.job_id = Job.id

LEFT JOIN SummaryAlert ON JobMediaFile.id = SummaryAlert.jobmediafile_id

LEFT JOIN FileStatus ON FileStatus.Mediafile_id = MediaFile.id

GROUP BY MediaFile.id

[jjacquay712]

I had made a script to do this exact thing a few months ago. When I get home I'll try to dig it up and post it.

[jon23d]

fputcsv($resource, $fields), handles this beautifully!

[_spaz]

fputcsv($resource, $fields), handles this beautifully!

 

Great thanks, this is the code that I'm currently using and it works great.  Is there a way to display the Field names for each column?

 

<?php

 

 

 

mysql_connect("192.168.0.184","root","vtec19");

 

// Select the database

$db_select=mysql_select_db("CerifyDB");

 

define('CSV_SEPERATOR',';');

define('CSV_PATH','\\');

define('CSV_FILENAME','data.csv');

 

 

$_POST[keyword] = Canwest;

 

$result = mysql_query("SELECT MediaFile.id, MediaFile.filename, MediaFile.createtime, JobMediaFile.starttime,    JobMediaFile.endtime, JobMediaFile.progress, Job.name, SummaryAlert.title, FileStatus.status

          FROM MediaFile

LEFT JOIN JobMediaFile ON JobMediaFile.mediafile_id = MediaFile.id

      LEFT JOIN Job ON JobMediaFile.job_id = Job.id

      LEFT JOIN SummaryAlert ON JobMediaFile.id = SummaryAlert.jobmediafile_id

      LEFT JOIN FileStatus ON FileStatus.Mediafile_id = MediaFile.id

  WHERE MediaFile.filename LIKE '%$_POST[keyword]%' GROUP BY MediaFile.id");

 

$fp = fopen(CSV_FILENAME, 'w');

if ($fp == false) {

  die("Could not open data.csv for writing");

 

 

}

 

 

while ($record = mysql_fetch_row($result)) {

  fputcsv($fp, $record);

 

}

 

echo '<a href="' . CSV_PATH . CSV_FILENAME . '" target="_blanc">CSV File</a>';

fclose($fp);

?>

[jon23d]

$row_counter = 0;

if (mysql_num_rows($results)) {

  while ($r = mysql_fetch_assoc($results)) {

    if ($row_counter++ == 0)  fputcsv(array_keys($file, $r);

    fputcsv($file, $r);

  }

}

[_spaz]

$row_counter = 0;

if (mysql_num_rows($results)) {

  while ($r = mysql_fetch_assoc($results)) {

    if ($row_counter++ == 0)  fputcsv(array_keys($file, $r);

    fputcsv($file, $r);

  }

}

 

Thanks, works great!