[How to show MySQL query data table and export it?]

try this.

 

<?php

$host="localhost";

$user="username";

$password="password";

$database="db_name";

$table="table_name";

 

$connect = mysql_connect($host,$user,$password);

if (!$connect)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db($database, $connect);

 

  header($outtype);

  $outtype = 'Content-disposition: attachment; filename="x.csv"';

   

$result = mysql_query("SELECT * from $table");

 

 

 

while($row = mysql_fetch_array($result))

  {

  echo $row['date'] . "," . ;  //put your desired columns here to show

  echo "\n";  //changes line/row in

  }

 

$fname = ('testFile.csv');

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

fwrite($fp,"");  // $csvdata --> "" (empty also works)

fclose($fp);

 

 

header('Content-type: application/csv');    // /octet-stream for /csv works as well

header("Content-Disposition: attachment; filename=".$fname);  //inline --> attachment

readfile($fname);

     

 

mysql_close($connect);

?>

[PHP Header Attachment Alternative (convert to CSV file)]

This script prompts the user and asks where to save the .csv file:

 

<?php

$host="localhost";

$user="xcart";

$password="xcart0r";

$database="xcart";

//$table="table_name";

 

$connect = mysql_connect($host,$user,$password);

if (!$connect)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db($database, $connect);

 

  header($outtype);

  $outtype = 'Content-disposition: attachment; filename="x.csv"';

   

$result = mysql_query("SELECT A4.value AS adventcode, A1.productcode, A1.product, SUM(A2.price) AS cost_price, SUM(A1.amount) AS qty, SUM(A2.price * A1.amount) AS grand_total, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS order_date

FROM xcart_order_details A1

LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid

LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid

LEFT JOIN xcart_extra_field_values A4 ON A1.productid = A4.productid

WHERE A2.membershipid = '2' AND A3.status = 'P' AND  A4.fieldid = '3'

GROUP BY A1.productid

ORDER BY A1.orderid AND A1.productid ASC");

 

 

 

while($row = mysql_fetch_array($result))

  {

  echo $row['date'] . "," . ;  //put your desired columns here to show

  echo "\n";  //changes line/row in

  }

 

$fname = ('testFile.csv');

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

fwrite($fp,"");  // $csvdata --> "" (empty also works)

fclose($fp);

 

 

header('Content-type: application/csv');    // /octet-stream for /csv works as well

header("Content-Disposition: attachment; filename=".$fname);  //inline --> attachment

readfile($fname);

     

 

mysql_close($connect);

?>