Page 1 of 1

Advanced Export CSV Function

Posted: Thu Apr 30, 2009 8:42 am
by j.smith1981
I am having problems with the following code:

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>
 
It was edited from:
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>
 
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.

Re: Advanced Export CSV Function

Posted: Thu Apr 30, 2009 12:30 pm
by Benjamin
Do you need help with the PHP or your database queries?

Re: Advanced Export CSV Function

Posted: Fri May 01, 2009 10:14 am
by j.smith1981
Ah no I'm fine with that it all works perfectly well apart from having huge digits after the decimal point for the price (does anyone know how to limit this to 2 characters after the decimal point?):

Code: Select all

<?php
 
$connect = mysql_connect('localhost', 'jeremy', 's56pj989'); // Connects to MySQL Server
$db_select = mysql_select_db('xcart', $connect); // Selects X Cart db
 
function parseCSVComments($comments) {
    $comments = str_replace('"', '""', $comments); // Escape all " and replace with ""
    if(eregi(",", $comments) or eregi("\n", $comments)) { // Checks if there's any commas or new lines
    return '"'.$comments.'"'; // If there's any commas or new lines escape them!
    } else {
    return $comments; // If no new lines or commas just return the value
    }
}
 
$sql = 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
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"); // Actual query!
 
$numberFields = mysql_num_fields($sql); // Set out number of fields we are actually fetching should be (4) with any luck!
 
    if($numberFields) { // Checks if we can output anything or we need to?
    for($i=0; $i<$numberFields; $i++) {
        $head[] = mysql_field_name($sql, $i); // Create the headers for each column, this is the field name in the database
    }
    $headers = join(',', $head)."\n"; // Makes the header row in the CSV file
 
    while($info = mysql_fetch_object($sql)) {
        foreach($head as $fieldName) {
        $row[] = parseCSVComments($info->$fieldName);
        } // Ends foreach loop
        $data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row
        $row = ''; // Clear the contents of the $row variable to start a new row
    }
    // Start out actual output of the CSV file!
    header("Content-type: application/x-msdownload");
    header("Content-Disposition: attachment; filename=purchase_Report.csv");
    header("Pragma: no-cache");
    header("Expires: 0");
    echo $headers.$data;
} else {
    // Nothing needed to be output. Put an error message here or something.
    echo 'No data available for this CSV.';
}
?>
I also tried putting in some sort of time stamp for the date, using the bit of the above coding here:
header("Content-Disposition: attachment; filename=purchase_Report.csv");

But what I did originally didnt work.

Does anyone know of a way, when you go and click on the script link to get the file to auto increment on the file name to the date of the day when the user involked the link please?

Thanks again,
Jez.

Re: Advanced Export CSV Function

Posted: Fri May 01, 2009 12:54 pm
by Benjamin
:arrow: Moved to PHP - Code

Have a look at round() or number_format()

http://us2.php.net/round