From Formit to Database

5,888 Posts

Bruno17 Reply #11, 14 years, 9 months ago

you can try somethink like that:

$classname = 'Headlines';
$date = strftime('%Y-%m-%d');
$query = $modx->newQuery($classname);
$query->where("DATE(" . $modx->escape($classname) . '.' . $modx->escape('publishedon') . ") = '" .$date."'", xPDOQuery::SQL_AND);

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

28 Posts

shotup Reply #12, 14 years, 9 months ago

Thank you for your help Bruno17 but this gives me a white screen.
Is it because I use Revo 2.1.3? I thought I read somewhere, that modx->escape is no longer supported in 2.1 and above but I might be wrong.

Modx 2.1.3, PHP 5.2

28 Posts

shotup Reply #13, 14 years, 9 months ago

Does anybody know, why this is not working:

$query = $modx->newQuery('Headlines');
$query->where(array(
    strftime('%Y','date') => '2012',
));

Modx 2.1.3, PHP 5.2

387 Posts

whistlemaker Reply #14, 14 years, 9 months ago

$query->where(array(
    strftime('%Y','date') => '2012',
));

Maybe because 'date' is some text - 'date' in fact and not a date perhaps you meant $date.

So:

$query->where(array(
    strftime('%Y', $date ) => '2012',
));

28 Posts

shotup Reply #15, 14 years, 9 months ago

@whistlemaker: Well, 'date' is the name of the column of my custom db where the date is stored as Unix Timecode value and the field is type (int20).

I also worked again on Burno17 idea and I changed this

$classname = 'Headlines';
$date = strftime('%Y-%m-%d');
$query = $modx->newQuery($classname);
$query->where("DATE(" . $modx->escape($classname) . '.' . $modx->escape('publishedon') . ") = '" .$date."'", xPDOQuery::SQL_AND);

to this

$classname = 'Headlines';
$date = strftime('%Y-%m-%d');

$query = $modx->newQuery($classname);

// changed $modx->escape('date')

$query->where("DATE(" . $modx->escape($classname) . '.' . $modx->escape('date') . ") = '" .$date."'", xPDOQuery::SQL_AND);

because the column in the db where the date is stored as a Unix Timestamp is called "date". The snippet now runs fine but it doesn't give me any records back so I still must be doing something wrong. Maybe this line is wrong:

$date = strftime('%Y-%m-%d');

As far as I understand, there must be the date value from the resources publishedon content tag. I try again to explain in detail, what I would like to do: From a resource, I call a snippet. This snippet queries my custom db (called tb_headlines;classname is headlines). It should return all records that have the same date like the resources publishedon date value. The db field where the date is stored as a unix timestamp is called "date". Any help is very much appreciated. [ed. note: shotup last edited this post 14 years, 9 months ago.]

Modx 2.1.3, PHP 5.2

28 Posts

shotup Reply #16, 14 years, 9 months ago

Ok, after 3 days of try&error, reading tons of forum posts and instructions, I found the solution. For everyone who is interested, here is the code:

// Query the Database

$classname = 'Headlines';
//Get the "Published On" Date/Time from the resource
$pubon = $modx->resource->get('publishedon');
//Shorten the "Published On" to 10 digits, so we have only the date
$pubonshort = substr($pubon,0,10);
//Convert the "Published On" Date back into a Time Stamp Value
$pubTimeStamp = strtotime($pubonshort);
$query = $modx->newQuery($classname);
$query->where(array(
      'date' => $pubTimeStamp,
     
));

$quotes = $modx->getCollection('Headlines',$query);

The problem was, that in my db, the date field was defined as int(20). So I had to convert the resource "publishedon" date into an integer. But because the time is also saved within the publishedon field, I had do shorten it or better say, extract just the date and convert it back into a timestamp value. Maybe it's not the best was to do it but so far it works and at least I understand what I do

Thanks to all of you for your help!

Modx 2.1.3, PHP 5.2

5,888 Posts

Bruno17 Reply #17, 14 years, 9 months ago

if you have unixtime you could also try:

$classname = 'Headlines';
$date = strftime('%Y-%m-%d');
$query = $modx->newQuery($classname);
$query->where("FROM_UNIXTIME(publishedon,'%Y-%d-%m')  = '" .$date."'", xPDOQuery::SQL_AND);

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

28 Posts

shotup Reply #18, 14 years, 9 months ago

Thanks Bruno. What I don't get in your code is this:

$date = strftime('%Y-%m-%d');

I thought strftime needs a value to convert but I only see a pattern for formating the time.

Modx 2.1.3, PHP 5.2

387 Posts

whistlemaker Reply #19, 14 years, 9 months ago

@shotup
... just trying to help.
strftime() is a php function which takes two parameters - the second is a time stamp, an integer.
'date' in this context is a string and not a time, even though it may be a column name in your database that is not what 'date' means as part of strftime().

To answer your last question - the second parameter in strftime() is optional - if you leave it out it uses the current server time. So

$date = strftime('%Y-%m-%d');

makes $date = the current time (as set on the server for php) formatted thus 2012-01-16

From the excellent php docs:

strftime

(PHP 4, PHP 5)

strftime — Format a local time/date according to locale settings

Description

string strftime ( string $format [, int $timestamp = time() ] )

Format the time and/or date according to locale settings. Month and weekday names and other language-dependent strings respect the current locale set with setlocale().
... and lots more ...

[ed. note: whistlemaker last edited this post 14 years, 9 months ago.]