Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to insert separate row for unique login count each day in database table

 
Shailesh Kumar Saxena
Greenhorn
Posts: 25
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Shailesh Kumar Saxena
Greenhorn
Posts: 25
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin

Thank you for a soon reply. I got some basic idea.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic