CODEWITHSUNDEEP
phpmysqldatejoomla
JoePanpi
JoePanpi

Reputation: 43

how use custom joomla date in mysql query

i have this query from a custom class in Joomla which uses CURDATE and NOW. 

$query = "SELECT DISTINCT catid FROM table_x WHERE DATE(publish_up)=CURDATE() AND publish_up<=NOW()";

default server time had to change so i want to change it to use joomla custom time called from here:

$date = JFactory::getDate('now', new DateTimeZone('Europe/Berlin'));

i hoped it whould be easy (since i am kinda nooby) like 

....WHERE DATE(publish_up)='".$date."' AND ....

but it doesn't work any ideas?

Upvotes: 1

Views: 2307

Answers (1)

Arek Szczerba
Arek Szczerba

Reputation: 458

SQL CURDATE() and NOW() use different formats http://www.w3schools.com/sql/func_curdate.asp so if you want compare them with your date you need to prepare it right

Please take a look at example code below

$nowdate = JFactory::getDate('now', new DateTimeZone('Europe/Berlin')); // 2016-08-23 15:01:14
$curdate = JFactory::getDate('now', new DateTimeZone('Europe/Berlin'))->format('Y-m-d'); // 2016-08-23

for Joomla older then v3.3 you should use

$curdate = JFactory::getDate('now', new DateTimeZone('Europe/Berlin'))->toFormat('Y-m-d'); // 2016-08-23

then you can build query using JDatabase

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select("distinct ". $db->quoteName('id'));
$query->from($db->quoteName('#__content'));
$query->where($db->quoteName('publish_up') .' <= '. $db->quote( $nowdate )); //today and older
$query->where('Date('.$db->quoteName('publish_up').')' . ' = '.  $db->quote( $curdate )); // today
$query->order('id ASC');

$db->setQuery($query);

$rows = $db->loadRowList();
print_r($rows);

Please take a look at JDate documentation https://docs.joomla.org/How_to_use_JDate

Upvotes: 1

Related Questions