Advanced Export CSV Function
Posted: Thu Apr 30, 2009 8:42 am
I am having problems with the following code:
It was edited from:
http://www.phpsimple.net/tutorials/mysql_to_csv/
I have the following code working from php:
It just brings out the HTML, with results of what comes out of HTML when its been parsed, ie the CSV file contains just HTML tags, not what I was wanting what so ever, can someone help me do some code that would allow this, just a basic list of products ordered with the mySQL query?
Thanks,
Jeremy.
Code: Select all
<html>
<head>
<title>Shopping Cartridge World : X Cart : Purchasing Report</title>
</head>
<body>
<!-- <h3>Shopping Cartridge World : X Cart : Purchasing Report</h3> -->
<br />
<!-- CSV EXPORT PART -->
<?php
// Connect database
$host = 'localhost'; // Sets the variable host as a literal no parsing for db connections
$user = 'jeremy'; // username
$passwd = 's56pj989'; // db password (abbreviated to unix password command)
$dbname = 'xcart';
$conn = mysql_connect($host, $user, $passwd) or die('<h3>Error connecting to database</h3>'); // Database connection
$result=mysql_query("SELECT xcart_order_details.productcode, SUM( xcart_order_details.amount ) AS qty, SUM( xcart_order_details.price / 1.2 - 1.35 ) AS item_price, SUM( (
xcart_order_details.price / 1.2 - 1.35
) * xcart_order_details.amount ) AS total
FROM xcart_order_details
left join xcart_orders
on xcart_order_details.orderid = xcart_orders.orderid
where xcart_orders.date
-- COMMENTED OUT BECAUSE OF TESTING : BETWEEN (UNIX_TIMESTAMP( DATE( DATE_SUB( now( ) , INTERVAL 1 DAY ) ) ) + ( 15 *60 *60 )) AND (UNIX_TIMESTAMP( DATE( now( ) ) ) + ( 15 *60 *60 ))
GROUP BY xcart_order_details.productcode
ORDER BY xcart_order_details.productcode");
$out = '';
// Get all fields names in table "name_list" in database "tutorial".
$fields = mysql_list_fields(xcart,xcart_order_details . xcart,xcart_orders);
// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);
// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="n";
// Add all values in the table to $out.
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="n";
}
// Open file export.csv.
$f = fopen ('purchase_REPORT.csv','w');
// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>
<a href="#" onClick="history.go(-1)">Back</a>
<!-- <input type=button value="Back" onClick="history.go(-1)"> -->
</body>
</html>
http://www.phpsimple.net/tutorials/mysql_to_csv/
I have the following code working from php:
Code: Select all
<html>
<head>
<title>Shopping Cartridge World : X Cart : Purchasing Report</title>
</head>
<body>
<h3>Shopping Cartridge World : X Cart : Purchasing Report</h3>
<!-- <a href="#" onClick="history.go(-1)">Back</a> -->
<input type=button value="Back" onClick="history.go(-1)">
<?php
$host = 'localhost'; // Sets the variable host as a literal no parsing for db connections
$user = 'jeremy'; // username
$passwd = 's56pj989'; // db password (abbreviated to unix password command)
$dbname = 'xcart';
$conn = mysql_connect($host, $user, $passwd) or die('<h3>Error connecting to database</h3>');
mysql_select_db($dbname);
$db_query = "SELECT xcart_order_details.productcode, SUM( xcart_order_details.amount ) AS qty, SUM( xcart_order_details.price / 1.2 - 1.35 ) AS item_price, SUM( (
xcart_order_details.price / 1.2 - 1.35
) * xcart_order_details.amount ) AS total
FROM xcart_order_details
left join xcart_orders
on xcart_order_details.orderid = xcart_orders.orderid
where xcart_orders.date
-- COMMENTED OUT BECAUSE OF TESTING : BETWEEN (UNIX_TIMESTAMP( DATE( DATE_SUB( now( ) , INTERVAL 1 DAY ) ) ) + ( 15 *60 *60 )) AND (UNIX_TIMESTAMP( DATE( now( ) ) ) + ( 15 *60 *60 ))
GROUP BY xcart_order_details.productcode
ORDER BY xcart_order_details.productcode";
$result = mysql_query($db_query);
echo '<table border=0 width="50%">';
// Header columns
echo '<tr>';
echo '<td><strong>Productcode</strong></td>' . '<td><strong>QTY</strong></td>' . '<td><strong>Item Price</strong></td>' . '<td><strong>Total</strong></td>';
echo '</tr>';
// SQL results
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<tr>';
echo '<td>' . "{$row['productcode']}" . '</td>' . '<td>' . "{$row['qty']}" . '</td>' . '<td>' . "{$row['item_price']}" . '</td>' . '<td>' . "{$row['total']}" . '</td>';
echo '</tr>';
}
echo '</table>';
?>
<!-- <a href="#" onClick="history.go(-1)">Back</a> -->
<input type=button value="Back" onClick="history.go(-1)">
</body>
</html>
Thanks,
Jeremy.