[SOLVED] help with finding specific row in this function

[rondog]

I found this function online that exports some fields from a database and makes it a CSV file. I have two fields that are unix time stamps and I would like to run the date() function on those specific fields, however I dont know where in this function to do that. Can anyone point me in the right direction?

 

I will be trying in the mean time, thanks.

<?php
function exportMysqlToCsv($table,$filename = 'export.csv')
{
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = "SELECT email,firstname,lastname,birth,address1,address2,city,state,zip,phone,date_added,last_login,next_login,times_logged FROM $table";
// Gets the data from the database
$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;
}
?>

[kickstart]

Hi

 

That is just looping round all the returned fields. Designed to be very simple and flexible. But it would be a fudge to reformat the data for a field in the loop.

 

It would be better to format the date in the SELECT statement so the script can just write it out directly.

 

All the best

 

Keith

[rondog]

How do I format in the SELECT statement? I've never done such a thing. I tried this:

$sql_query = "SELECT email, firstname, lastname, birth, address1, address2, city, state, zip, phone, date_added, DATE_FORMAT(last_login,'%M/%e/%Y'), DATE_FORMAT(next_login,'%M/%e/%Y'), times_logged FROM $table";

 

But no luck..my column name was DATE_FORMAT(next_login,'%M/%e/%Y') in the CSV file

[kickstart]

Hi

 

Use FROM_UNIXTIME:-

 

FROM_UNIXTIME(next_login,'%M/%e/%Y')

 

PHP deals in unix timestamps while Mysql deals in another format of time stamps. If you want to store unix timestamps in mysql you need to store them in an integer field (which is what I presume you have done).

 

All the best

 

Keith

[rondog]

ahh thank you that worked. For anyone else, here is what my select looks like:

 

$sql_query = "SELECT email, firstname, lastname, birth, address1, address2, city, state, zip, phone, date_added, FROM_UNIXTIME(last_login,'%M %d, %Y, %l:%i %p'), FROM_UNIXTIME(next_login,'%M %d, %Y, %l:%i %p'), times_logged FROM $table";