Hi guys,
I'm having a weird problem relating to the exportation of data to a .csv file. Basically, I need to export specific fields from three different tables into one sheet. This process runs fine and I have successfully extracted the data needed and it shows up in excel. I'm using two files for this:
export_products_feed.php (this is basically where my mysql connections and table variable calling is kept)
<?php
require('includes/application_top.php');
require('exportcsv.inc.php');
$table="manufacturers a, products_description b, products c";
exportMysqlToCsv($table);
?>
The second page being used is where the problem lies:
exportcsv.inc.php
<?php
function exportMysqlToCsv($table,$filename = 'products_stock.csv')
{
$ordersort .="manufacturers_name, products_name ASC";
$wherecond .= "c.products_quantity >='1'";
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = "select a.manufacturers_name, b.products_name, b.products_url, c.products_quantity from $table WHERE a.manufacturers_id = c.manufacturers_id AND b.products_id = c.products_id AND $wherecond ORDER BY $ordersort" ;
$result = mysql_query($sql_query);
$fields_cnt = mysql_num_fields($result);
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++)
{
$l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
$schema_insert .= $l;
$schema_insert .= $csv_separator;
} // end for
$out = trim(substr($schema_insert, 0, -1));
$out .= $csv_terminated;
// Format the data
while ($row = mysql_fetch_array($result))
{
$schema_insert = '';
for ($j = 0; $j < $fields_cnt; $j++)
{
if ($row[$j] == '0' || $row[$j] != '')
{
if ($csv_enclosed == '')
{
$schema_insert .= $row[$j];
} else
{
$schema_insert .= $csv_enclosed .
str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
}
} else
{
$schema_insert .= '';
}
if ($j < $fields_cnt - 1)
{
$schema_insert .= $csv_separator;
}
} // end for
$out .= $schema_insert;
$out .= $csv_terminated;
} // end while
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=$filename");
echo $out;
exit;
}
?>
The area of the code that I'm having issues with is:
$ordersort .="manufacturers_name, products_name ASC";
$wherecond .= "c.products_quantity >='1'";
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = "select a.manufacturers_name, b.products_name, b.products_url, c.products_quantity from $table WHERE a.manufacturers_id = c.manufacturers_id AND b.products_id = c.products_id AND $wherecond ORDER BY $ordersort" ;
Now, this is where my slight problem is... when I error tested $sql_query to see why products_url fields were not showing up, i found that the code would work fine and all the field columns in excel would contain the selected data until the
b.products_id = c.products_id
section was added in (this just basically means that the products dont appear more than once in excel)
I've user tested other ways, and either, every field column gets data inserted into it, including products_url, but the output is skewed, or the output is correct, but the products_url field (which is contained in products_description table) stays blank.
Any ideas where I'm going wrong with this? As the products_url field needs to output the URL of the product for a feed/report for work.
Cheers