mysql query with perl

[reefbum]

I'm very new to perl and mysql and am in need of a bit of help with a perl script that is doing query to mysql DB for radius. Currently the script is doing a sum of octects from DB by username only. My issue is that it is doing the sum for ALL entries from that username. What i need it to do is sum all database entries by username for the current date only. So that database entries for previous days are not included in the sum.

This is the portion of the script in question.


$cursor = $dbh->prepare("SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
FROM radacct
WHERE username='$ARGV[0]'")
or die("Couldn't prepare");
$cursor->execute;


Thanks for any help you can offer

 

[MillerH]

Following the mysql documentation:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Say I have the following LOGIN table:

mysql> DESC LOGIN;
+-----------+----------+---------------------+
| Field     | Type     | Default             |
+-----------+----------+---------------------+
| ID        | int(11)  | NULL                |
| userID    | int(11)  | 0                   |
| created   | datetime | 0000-00-00 00:00:00 |
+-----------+----------+---------------------+

To count the number of Logins for "today" for a specific account, I could do the following:

SELECT COUNT(*) FROM LOGIN WHERE userID='$ARGV[0]' AND created>CURDATE()

You'll have to either tell us your table schema, or simply modify the use of CURDATE() to your own query. Please note that the sql query that I wrote works because created is of type DATETIME. If it were a TIMESTAMP, the query would have to convert the field to a date first.

 

[reefbum]

This is the schema has other tables but I think this provides the info needed

+------------+--------------+---------------------+
| Field      | Type         | Default             |
+------------+--------------+---------------------+
| ID         | int(11)      | NULL                |
| UserName   | varchar(64)  | 0                   |
| _AcctTime  | datetime     | 0000-00-00 00:00:00 |
+------------+--------------+---------------------+

If I understand this correct my new query would look like this?

$cursor = $dbh->prepare("SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
                         FROM radacct
                 WHERE username='$ARGV[0]' AND _AcctTime>CURDATE()")
    or die("Couldn't prepare");
$cursor->execute;

Thanks again for the help

 

[MillerH]

That should do it.

It's always a good idea when writing a new sql query to test it at a sql prompt. In this instance you would have fill in a username of course. But I find this is always a good sanity check to make sure that the results match what you expect. In this instance I would remove the SUM's and instead pull all records to see if they are indeed all the records for the current date. It's also the best way to learn SQL, since you can play with the query to see what small changes will do. Good luck.