I am trying to convert the time format from a unix time stamp to a m/d/Y format inside the query.
[php]stmt = $conn->prepare("SELECT orderid, DATE_FORMAT(date, ‘%m/%d/%Y’) as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where date BETWEEN $BeginDate AND $EndDate ");
[/php]
However, when i do this the query runs but the output for the orderdate is empty
I think your problem is after the WHERE clause?
Here’s how to do it in PDO:
[php]/* I wouldn’t use date for I believe that is a reserved MySQL word, I used my_date */
$sql = “SELECT orderid, DATE_FORMAT(my_date, ‘%m/%d/%Y’) as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where my_date :from_date AND :to_date”;
$stmt = $db_pdo->prepare($sql);
$stmt->execute(Array(’:from_date’ => $BeginDate, ‘:to_date’ => $EndDate));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);[/php]
date is the name of the field in the database (tables in an ecommerce app that I didn’t write). This is code that i got from a kind of tutorial so my full understanding of all of it is dubious.
well here is my full query section
[php]try{
$conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT orderid, DATE_FORMAT(date, ‘%m/%d/%Y’) as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where date BETWEEN $BeginDate AND $EndDate ");
$stmt->execute();
//set the resulting array to associative
$result = $stmt->SetFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchALL())) as $k=>$v) {
echo $v;
}
}[/php]
When i try it the suggested way I get this error
PHP Fatal error: Uncaught Error: Call to a member function prepare() on null in /home/--------/TGemailaddressreport2.php:69
Stack trace:
#0 {main}
thrown in /home/vintagep/public_html/tvpsecure/TGemailaddressreport2.php on line 69
for the code
[php]try{
$conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$stmt = $conn->prepare("SELECT orderid, DATE_FORMAT(date, ‘%m/%d/%Y’) as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where date BETWEEN $BeginDate AND $EndDate ");
$sql = “SELECT orderid, DATE_FORMAT(date, ‘%m/%d/%Y’) as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where orderdate :from_date AND :todate”;
$stmt = $db_pdo->prepare($sql);
$stmt->execute(Array(’:from_date’ => $BeginDate, ‘:to_date’ => $EndDate));
//set the resulting array to associative
//$result = $stmt->SetFetchMode(PDO::FETCH_ASSOC);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchALL())) as $k=>$v) {
echo $v;
}
}
[/php]
I dont have this variable anywhere ,$db_pdo, and that may be the problem
I was able to get it to work with my original code but updating the DATE_FORMAT to
DATE_FORMAT(FROM_UNIXTIME(date), ‘%m/%d/%Y’) as orderdate
thanks
OP said in the first post it is unix timestamp.
I am trying to convert the time format from a[b] unix time stamp[/b]