Date format problem

PHP
1

Hi,

I have a contract database, and when ever I try and export to a csv file I get a query failed error, which I have created.

I have used var_dump and tested the output in Server Management studio and it works, so its not the query its something else which I cant work out.

Here is the code from the full page:

<?
error_reporting(E_ALL);
ini_set('display_errors','Off');

session_start();
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$username=$_SESSION['user1'];
if ($_SESSION['flaglog']!=1)
  {	
header("location:index.php");
  }
else
{
$query = "SELECT * FROM Users WHERE Username='$username'";
$res = sqlsrv_query($conn, $query); 
while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) { 
$name = $result["First_Name"];
$security = $result["Admin"];
//echo $security;
}
$contractLike = substr($username, 0, 3);
//echo $contractLike;
}

$query = $_SESSION['limitClauseAdv'];

$query = str_replace('WITH LIMIT AS ( ', '', $query); 
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace('))', ')', $query);
$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
$query = str_replace("%')", "%'", $query);
//var_dump($query);

function cleanData(&$str)
 {
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
  $str = "'$str";
}
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}

// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv; charset=UTF-16LE");

$out = fopen("php://output", 'w');

$flag = false;

 $result = sqlsrv_query($conn, $query) or die('Query failed!');
 while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {

  if(!$flag) {
  // display field/column names as first row
  fputcsv($out, array_keys($row), ',', '"');
  $flag = true;
  }
  //array_walk($row, 'cleanData');
//start
foreach ($row as $key => $value)
	{ 
    if ($value instanceof DateTime) 
  	{ 
    $row[$key] = date_format($value, 'd/m/y'); 
	}
    }
//end
$row = str_replace("&#44;", ",", $row);
$row = str_replace("&#46;", ".", $row);
    fputcsv($out, array_values($row), ',', '"');
  }

  fclose($out);
  exit;

?>

When I test it with this query it doesn’t work -

string(121) "SELECT * FROM Intranet WHERE  date_Modified > '01/05/15')  select COUNT(*) from LIMIT WHERE  date_Modified > '01/05/15'"

But when I use this query it works -

string(43) "SELECT * FROM Intranet  order by ID DESC"
2

I think I can see the issue now actually, its the bracket on its own ‘01/05/15’) which I don’t understand why its on its own, and alos just noticed that the dates in the database are featured like YYYY/MM/DD and above it seems to be DD/MM/YYYY so again how can I swap this around to gather that data.

3

To convert the date either use date() and strtodate():

$date = date('Y-m-d', strtotime('01/05/15'));

Or use the datetime function:

$dt = DateTime::createFromFormat('d/m/y', '01/05/15');
$date = $dt->format('Y-m-d');
4

or use DATE_FORMAT() in your SQL to specify the format to be returned

1 Like
5

+1 I’m all up for leaving mysql to do all the work. If you can afford to modify the statement this would be quicker.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.