See if you can help

[maximo1970]

Sorry about the subject line but I can't think of how to describe my problem.

My DataBase table contains a field for the date and time, ie 2004-03-06 21:34:12. I want to group by the date only, ei 2004-03-06. I'm using perl to select the data which is then displayed on a web site. I have the date in a variable but just can't seem to find the right function to select my data. I have tried :-

Date LIKE '2004-03-06%' this works.
Date LIKE \"$date\" this does not work.
Date LIKE \"$date%\" this does not work either.

So any help would be great.

Cheers.

 

[sleipnir214]

Instead of generating your query and sending it to MySQL, generate the query and send it to the browser or console so you can see what is being generated.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TonyGroves]

How about: [tt]"SELECT ... datefield LIKE '$date\%'"[/tt]

(1) Perl interprets an unescaped % within double-quotes as a hash prefix.
(2) The variable $date must be in the format yyyy-mm-dd.
(3) DATE is a reserved word; if you use it as a field name, you must enclose it in 'backticks' (grave-accent characters).

-----
ALTER world DROP injustice, ADD peace;

 

[maximo1970]

Tony,

Tried your suggestion of "SELECT ... datefield LIKE '$date\%'". This didn't work sadly. Here's my current code as it may help.

my $curs = $dbh->prepare("SELECT Device_Name, Event, Checked, Comments FROM tbl_event_log WHERE Device_Name = \"$ipaddress\" GROUP BY Checked DESC LIMIT 5");

$curs->execute();

This works for the time being but is not the result I'd like to have. I want to display just information with today's date. "Checked" is my date field and is formated as 'yyyy-mm-dd hh:mm:ss'

Cheers

 

[TonyGroves]

The following should work:
[tt]
@d=localtime;
$s=sprintf '%d-%02d-%02d',1900+$d[5],$d[4],$d[3];
my $curs=
$dbh->prepare
(
"SELECT Device_Name, Event, Checked, Comments".
" FROM tbl_event_log WHERE Device_Name='$ipaddress' AND Checked LIKE '$s\%' ".
"GROUP BY Checked DESC ".
"LIMIT 5"
);
$curs->execute();
[/tt]

The current date is formatted into yyyy-mm-dd form before being inserted into the query.

-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Just noticed - is your query logic correct? Do you mean ORDER BY instead of GROUP BY? GROUP BY will just return one random record for each value of Checked. ORDER BY will return every matching record.

-----
ALTER world DROP injustice, ADD peace;

 

[r937]

GROUP BY is correct, assuming you want multiple dates in the result

e.g. LIKE '2004-04%', while ugly and inefficient, will return all datetime values for april 2004

however, you still need to group on the date only, not the date and time

if you are only looking for totals for one date, then WHERE is sufficient, and you don't need GROUP BY





rudy

http://r937.com/

SQL Consulting