homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Setting Date
textex

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373745 posted 6:27 pm on Oct 12, 2011 (gmt 0)

AND created_at < DATE_SUB(CURDATE(), INTERVAL 2 DAY)

I am trying to email today's data and all future. But using the above is sending an email to members from the db creating date. Any input?

 

janharders

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4373745 posted 6:36 pm on Oct 12, 2011 (gmt 0)

use >, if you mean "greater than", use < only if you mean "small than".

textex

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373745 posted 7:38 pm on Oct 12, 2011 (gmt 0)

What about for a specific start date of 10-01-11? What would the command be?

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4373745 posted 6:13 pm on Oct 13, 2011 (gmt 0)

What's between greater and lesser? Equal! :-)

A note though, curdate() and now() are internal mySQL functions. To execute a select with a static value it must be quoted.

AND created_at = '2011-13-11'

or

AND created_at = "2011-13-11"

You can also use

>=
<=

Both of these work for not equal

!=
<>

textex

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373745 posted 6:28 pm on Oct 13, 2011 (gmt 0)

I will give these a try. Thanks!

textex

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373745 posted 7:51 pm on Oct 13, 2011 (gmt 0)

I am still getting an error. What am I doing wrong?
$emails_to_send = $dbh->selectall_arrayref(
qq(
SELECT $cols
FROM apps
WHERE is_dup = 0
AND e3 IS NULL
AND AND created_at >= "2011-11-01", INTERVAL 2 DAY
ORDER BY id
LIMIT 5
),
{ Slice => {} },
);

janharders

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4373745 posted 9:23 pm on Oct 13, 2011 (gmt 0)

You are obviously trying to use date functions but you're not telling mysql which function you want to use.

[dev.mysql.com...]
has them all and their meanings. You'll probably want DATE_SUB().

also, "AND AND" will give you errors. and " is not the quote char for mysql, it's '
Use placeholders:
my $sth = $dbh->prepare("SELECT * FROM table WHERE field = ?");
$sth->execute( $fieldvalue );

and ? will magically be replaced by $fieldvalue and DBI will take care of escaping special chars and quote it correctly.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4373745 posted 4:19 pm on Oct 14, 2011 (gmt 0)

Huh? You can use double quotes, if it doesn't conflict with content(? Just tested that). Single quotes are a decent habit to form though, because one approach to coding (in Perl or PHP) is encapsulate select statements in double quotes to allow interpolation of the variables.

$query = "select * from table where the_date='$my_date'";

More correction examples . . .

AND created_at >= date_add('2011-11-01', INTERVAL 2 DAY)

or

AND created_at >= date_sub('2011-11-01', INTERVAL 2 DAY)

or for an exact match on the day, you don't use the functions.

AND created_at >= '2011-11-01'
AND created_at <= '2011-11-01'
AND created_at = '2011-11-01'
AND created_at <> '2011-11-01'

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4373745 posted 6:27 pm on Oct 14, 2011 (gmt 0)

Double quotes are a MySQL extension to the ANSI standard. I run my MySQL server in ANSI mode so if you try to use double quotes they will be interpreted as an identifier. I agree though, it's good practice to keep with single quotation marks.

References for further reading:
[dev.mysql.com...]
[dev.mysql.com...]

textex

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4373745 posted 7:04 pm on Oct 14, 2011 (gmt 0)

This works:
AND created_at >='2011-10-01'
AND created_at < DATE_SUB(NOW(), INTERVAL 2 DAY)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved