shockme has asked for the wisdom of the Perl Monks concerning the following question:
I've narrowed the following problem down to the involvement of $begDate and $endDate. Here's what's happening:my $numParams = 1; my $SQL = 'SELECT syslog_timestamp, syslog_host, syslog_text FROM syslog WHERE syslog_text like ?'; if ($begMonth) { $begDate = "$begYear\-$begMonth\-$begDay 00:00:00"; $endDate = "$endYear\-$endMonth\-$endDay 23:59:59"; $SQL .= ' AND syslog_timestamp >= ?'; $SQL .= ' AND syslog_timestamp <= ?'; $numParams += 2; } my $sth = $dbh->prepare($SQL); if ($numParams == 1) { my $DBquery= $sth->execute($SearchPhrase); } elsif ($numParams == 3) { my $DBquery= $sth->execute($SearchPhrase, $begDate, $endDate); }
When I execute this, it always returns 0 rows. I've omitted some code which displays the contents of $SQL to me, as well as code which displays the contents of $begDate and $endDate. When I cut and paste the contents of $SQL and the values of $begDate and $endDate into the MySQL commandline, it returns over 1,000 rows (which is what I would expect). If I don't give it $begDate or $endDate, it works fine. However, any time I use the date logic, it returns 0 rows and no errors.
Also, if I omit the placeholders and construct the following:
It works like a charm, returning in excess of 1,000 rows.if ($begMonth) { $begDate = "$begYear\-$begMonth\-$begDay 00:00:00"; $endDate = "$endYear\-$endMonth\-$endDay 23:59:59"; $SQL .= " AND syslog_timestamp >= '$begDate'"; $SQL .= " AND syslog_timestamp <= '$endDate'"; }
So, I'm apparently missing something obvious in the placeholder construction/execution. Any suggestions?
(Note: Typos in the above are due to cut and paste errors.)
If things get any worse, I'll have to ask you to stop helping me.
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: MySQL Placeholder Date Problem
by Abigail-II (Bishop) on Nov 25, 2003 at 16:36 UTC | |
by shockme (Chaplain) on Nov 25, 2003 at 16:49 UTC | |
by vek (Prior) on Nov 26, 2003 at 13:46 UTC | |
by shockme (Chaplain) on Nov 25, 2003 at 17:06 UTC | |
by Art_XIV (Hermit) on Nov 25, 2003 at 18:42 UTC | |
by shockme (Chaplain) on Nov 25, 2003 at 22:42 UTC | |
by runrig (Abbot) on Nov 26, 2003 at 18:52 UTC | |
Re: MySQL Placeholder Date Problem
by runrig (Abbot) on Nov 25, 2003 at 21:53 UTC |