PHP and MySQL login system

Report · Dec 16, 2010

Hi,

I am working on a php and mysql login system.

I am creating a login system which tracks the user activity. The time and date gets saved into the database when they last logged on. My client requires to know how many times a user has logged in between two dates. The only way I can think of doing this is inserting into the database every time they have logged in (time and date) and then carry out a query of when the user has logged on between date1 and date2.

At the moment the database updates the last time they logged in. So would it be best to insert every time they log in as appose to updating? Is this the best way to do it?

Thanks

Report · Dec 16, 2010

What you mentioned would probably work but it is not the most efficient method especially if there are going to be frequent visits by the users. You could create a numeric field in your login table - something like visit_count. Everytime someone logs in retrieve the value of that column, add one to it and update the record with the new value. From efficiency point of view, I would prefer this.

J.S.

http://www.ultrasuite.com/

Report · Dec 16, 2010

By just having a count of the number of times a user logs in, you lose the ability to query for the number of times between two arbitrary dates. If you know the dates that the customer is concerned about a priori, then you can just create a counter and update by one. When the end date occurs, do the query and reset the counters.

If, however, you have no prior knowledge of the date range your client wants to query on, unfortunately you are going to at least have a counter per day per user - meaning that if a user logs in on day n, then you need to start a counter for day n and update it every time that user logs in on that day. When day n+? occurs and the user log in, then you have to start a new counter for that user.

The question I would ask the client is for how long do you need to retain that information? If you provide the information for the number of times the user logs in between date1 and date2, can you then delete that data? If the client never asks for the information for a particular user, how long do you have to keep that data? While the client every ask for a query between date1 and date2 and then later ask for the information with a date range and includes or intersects with the previous date range request?

There are a lot of requirements missing here and without knowing them, you could code yourself into a corner and have a maintenance nightmare on the database.

Walter

B & B Photography

Report · Dec 16, 2010

Walter is absolutely right. My method would only give you a count of total logins and not a count of logins between specific dates which is what the OP asked. Sorry, I missed that.

Report · Dec 17, 2010

Thanks for the feedback guys...

I forgot to mention that I also have a login counter for each user but this was not enough for my client.

I spoke to my client briefly today and they said that they would like to export the login database every month. Is this something I can automate?

Thanks again for your help! 😄

Report · Dec 17, 2010

A simple answer is yes. What format does the client want? Do you need to delete the data after you have exported it? What are the data retention requirements? Is the export by user by date or by date by user? Are they looking for analysis - who logged in the most, what days were the heaviest, what days were the lightest, do they want graphs or raw data?

Lots of options here but you need to get some answers from your client.

Walter

B & B Photography

Report · Dec 17, 2010

Your client is lucky he didn't ask Walter the same question because Walter's questions would have left his head spinning. That said, all Walter's questions are relevant and it's best to get them clarified from the client before proceeding further.

J.S.
http://www.ultrasuite.com/
http://twitter.com/dwUltraSuite

Report · Dec 17, 2010

I actually pose questions like those quite differently when talking to a client. A client with a spinning head does not pay the bills.

I figure that here on the forums, we can talk frankly to each other in a language, I hope, we should all understand. I tend to like to create a written Statement of Work (SoW) with my client so that I have all the requirements nailed down and the client knows the issues and what they are getting. That has made my life and the life of my client's a lot easier.

Very rarely get into "discussions" about what they wanted and what I delivered with the SoW between us. It also allows for negotiation of fees when I can cost out each feature and service since its well defined. Any changes cause a renegotiation and that is made very clear in the beginning. I do that for both web work and photography.

Walter

B & B Photography

Report · Dec 18, 2010

Walter, your approach is very simple and professional which is how it should be. And it wouldn't hurt to emulate it.

However, as you know, I was just trying to inject some humor into the thread. It gets boring after a while - just answering technical questions.

Report · Dec 18, 2010

Yes, I do know and no offense taken. Just trying to give some hints to the OP.

Report · Jan 05, 2011

Hi again guys!

Thanks so much for your help! you gave me a lot to think about. After the Christmas break I spoke to my client again. They would like to export the table as an excel or csv file.

My form now works where every time a user logs in the time/date and their username gets saved into the database (this is in a table of its own). They said that the data can be deleted after a month if the export is automated. So the username and login time/date will be in this exported CSV or Excel file.

bnbwalt - how would I go about doing this? How can I automate this process? They would like to receive the file via email.

Thanks again for your help! and Happy new year

Report · Jan 05, 2011

The quick answer is, yes it should be possible. The long answer, for me, is I have never tried exporting from MySQL under PHP code. I will look into it and let you know. To be honest, I will probably search the web. If JS (Ultrasuite) is monitoring, he may have experience in this area. Sending the email is the simple part unless the size of the file gets too big. You will be able to ZIP it from PHP but, depending on the volume, it could still be a problem in the long term.

Do you or the client have any estimate of the traffic load over a month?

Walt

Report · Jan 05, 2011

The process is easy to achieve in three easy steps:

First step: google php export to excel

Second step: google php email script

Third step: google MySQL DELETE and MySQL WHERE to understand what to delete WHERE parameters are met in database.

See a pattern forming here? search, search, and more searching.

Report · Jan 05, 2011

Its amazing how stating the obvious rewards some people.

Thank you for your effort.

Report · Jan 06, 2011

Lol thanks bnbwalt!

I searched on google - only managed to export a csv but with the data all in one cell! which I why I came back to the forum... I found a few articles online but had no luck. I'll be working on it today as well! hopefully will find a way. Thanks for your help!

Report · Jan 06, 2011

Can you send the code you use? Also the test file you created? I want to see

if its something simple like not specifying the format correctly such that the

input side of Excel is not seeing the field breaks.

Very few problems are not solveable given time

Report · Feb 09, 2011

Hi again bnbwalt,

Sorry about the late reply. I have been waiting for my client to get back to me. I sent them the login system without the excel export as I couldn't get it to work how I wanted it. I followed a tutorial online, it was the only thing that worked for me:

<input type="hidden" value="<? echo $csv_output; ?>" name="csv_output">

</form>

<?php echo $row['id'];

$csv_output .= $row['id'] . "&#09 ";?>

<?php echo $row['username'];

$csv_output .= $row['username'] . ", ";?>

<?php echo $row['loggedin'];

$csv_output .= $row['loggedin'] . ", ";?>

Everything is exported in one cell ... I understand how and why this is happening but I can't find any other solution?

Any ideas?

Many thanks for all your help on this. This has really helped me a lot.

Report · Feb 11, 2011

Hi,

Getting the PHP and Mysql Login system with Dreamweaver is pretty cool job.

I have developed numerous Php based applications.

I started using Dreamweaver since 2003.

I am amazed by the ease at which i can work on Coding.

Thanks to Adobe.

SeeToronto

Sr Web Developer Toronto

http://www.giantcanada.com Classifieds