How to insert separate row for unique login count each day in database table
posted 4 years ago
I am working on a struts based web application using postgresql DB. I need to display last 15 days of login history of my website login users as a bar chart for website admin. I have tried to generate a bar chart using JFreeChart library. It is taking two Database coloumns and creating a bar chart as a image on the fly(dynamically). My problem is that I don't have any idea that how to store the count of unique login per day for last 15 days in database. Main problem is, it should maintain a new row for each new date and the entries should be counted date-wise.
How to get the logic so that whenever date is changing it should go to insert a new row for each new date? Any help will be appriciated.
How to get the logic so that whenever date is changing it should go to insert a new row for each new date? Any help will be appriciated.
Shailesh Saxena,
Pilibhit, U.P. INDIA
posted 4 years ago
Do you want to track unique logins, that is, one user's login will be counted only once on every given day?
You'll need a table containing rows, say, DATE and USERNAME. When a user logs in, you'll check whether a record for current date and the username already exists in the table, if it does, do nothing, if it doesn't, insert a new row with current date and the username. If you define a primary key on the table (and you should do it), you can just try to insert the row on each login and just handle possible primary key collision gracefully.
To obtain the number of logins per day, you'd just select count of records (COUNT(*)) grouped by the date. You could create a view with this query to plug it into JFreeChart seamlessly.
If you don't want to keep a longer history, you also need to have a process in place that will delete records older than 15 days every day.
You'll need a table containing rows, say, DATE and USERNAME. When a user logs in, you'll check whether a record for current date and the username already exists in the table, if it does, do nothing, if it doesn't, insert a new row with current date and the username. If you define a primary key on the table (and you should do it), you can just try to insert the row on each login and just handle possible primary key collision gracefully.
To obtain the number of logins per day, you'd just select count of records (COUNT(*)) grouped by the date. You could create a view with this query to plug it into JFreeChart seamlessly.
If you don't want to keep a longer history, you also need to have a process in place that will delete records older than 15 days every day.
