[SOLVED] MySQL Store every login dates and times

[samona]

Hi all,

 

I was wondering if there is a way to log every sign in a user makes.  For example, I want to be able to store when a user was logged in and how many times during that day he logged in.  This way I can have an archive I can go back to and find out who was logged in between hour1 and hour2 on any given day.

 

If it is possible, can you please help me out because I don't know where to start.  Thanks!

[fenway]

Just insert a record into a log table with a timestamp whenenver any user action occurs.

[samona]

That makes sense thanks, but now i'm a little confused on how i will connect the tables I have.

 

I have a User table, a Task table, a Calendar table that contains days 1 -31, and now a Log table.

 

The primary key in the User table is the UserID;

The Calendar table does not have a PK it has a foreign key UserID.

and the Log table does not have a PK it has a foreign key UserID.

 

The Task table consists of tasks that must be completed each day.  What I need to do is record who completed that task and at what time did they complete that task that day.

 

Do the tables I use make sense for this?

[samona]

Hi All,

After hours of thinking how to create this database of daily tasks I came up with the following tables.

 

I have a 'User' table which describes a UserID and the user.

I have a 'Tasks' table which describes a TaskID and the task information.

 

I have a predicament.  I want to create a table where I list the specific TaskIDs and then fill in the UserID of the person who completed the task, along with the time he/she finished the task.  If I had a table that was just for one day, it would be simple enough.  But how am I going to fit "TaskID,UserID,Time" for each day into one table?  I probably need more than one table for this, but I just cant' think of it.  Can someone give me a bump in the right directions? Thanks in advance.

[roopurt18]

But how am I going to fit "TaskID,UserID,Time" for each day into one table?

By creating a third table, call it `task_user`, with the fields `user_id`, `task_id`, and `created`.

 

CREATE TABLE IF NOT EXISTS `task_user` (
  `user_id` INT NOT NULL,
  `task_id` INT NOT NULL,
  `created` DATETIME NOT NULL
)

 

As for connecting the tables...ever heard of a JOIN?

[samona]

thanks a lot roopurt, I really appreciate you taking the time to reply to my post. 

 

Regarding Joins, I know them but I'm not sure what would be the primary key in the third table.  In order to differentiate between the different tasks I would need to have the information in all three columns.  So would the combination of user_id,task_id, and Datetime, be the primary key?

 

 

[roopurt18]

You don't need a primary key.  Neither should you make the three columns fall under a unique constraint.

 

Assuming the following tables:

`task_user` : with the structure in my previous post

`users` : `user_id`, `login`

`tasks` : `task_id`, `name`, `description`

 

Select everything between two dates:

SELECT
  m.`created`, u.*, t.*
FROM  `task_user` m
INNER JOIN `users` u ON u.`user_id`=m.`user_id`
INNER JOIN `tasks` t ON t.`task_id`=m.`task_id`
WHERE
  m.`created`>='2007-11-01 00:00:00' AND m.`created`<'2007-12-01 00:00:00'

[samona]

Thanks a lot for that information, I finally understood what you mean.  So, say I wanted to show all the tasks that were done and all the tasks that were not done on a particular day, would I be able to query that from this database using those 3 tables and display them on one page?

[roopurt18]

Yes.  It's pretty clear how to determine which tasks were done, so here is how you determine which tasks weren't done.  If all of the possible tasks exist in the `tasks` table, then you want to determine which ones do not exist in the `task_user` table.  For this we use a LEFT JOIN.

 

In an INNER JOIN between two tables, say X and Y, a match must be found in each table in order for it to be returned in the result set.  In other words, if the INNER JOIN column is id, then only rows that have the same X.id and Y.id will be returned.

 

In a LEFT JOIN, all of the rows in the LEFT TABLE are automatically returned whether there is a matching row in the other table or not.  In the cases where there is a matching row, LEFT JOIN behaves like INNER JOIN.  In the cases where there is no matching row, LEFT JOIN returns NULL for the other table.

 

So in order to determine which tasks do not have a record of being done, we will join the tables as before, using LEFT JOIN.  The task table has to be our "left" table, or the main table we are selecting from.

SELECT
  m.`created`, u.*, t.*
FROM `tasks` t
LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id`
LEFT JOIN `users` u ON m.`user_id`=u.`user_id`
WHERE
  (m.`created`>='2007-11-01 00:00:00' AND m.`created`<'2007-12-01 00:00:00')
  OR m.`task_id` IS NULL

I'm not sure if that query is exactly what you need; an INNER JOIN on `users` might be more appropriate than the LEFT JOIN, but it might also cause some rows not to be returned.  fenway or barand would be able to say for sure on that issue as they seem to have more experience in these matters.

 

Continuing with the example, when you receive this in PHP you'll be receiving a list of tasks both completed and not completed.  On each iteration over the result set you'll need PHP logic to test if the task is completed or not, but we can shovel this logic onto the database engine with a slight modification to the query:

SELECT
  m.`created`, u.*, t.*,
  IF(m.`created` IS NULL, 0, 1) AS `completed`
FROM `tasks` t
LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id`
LEFT JOIN `users` u ON m.`user_id`=u.`user_id`
WHERE
  (m.`created`>='2007-11-01 00:00:00' AND m.`created`<'2007-12-01 00:00:00')
  OR m.`task_id` IS NULL

 

Now you can easily order the tasks alphabetically with the database, but that might give you a "scattered" display on the web page of what still needs to be done.  So let's say you want the items to still be completed at the top of the page in one table followed by a second table of tasks that have been completed.  We will order the result set on the `completed` column and then alphabetically.

SELECT
  m.`created`, u.*, t.*,
  IF(m.`created` IS NULL, 0, 1) AS `completed`
FROM `tasks` t
LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id`
LEFT JOIN `users` u ON m.`user_id`=u.`user_id`
WHERE
  (m.`created`>='2007-11-01 00:00:00' AND m.`created`<'2007-12-01 00:00:00')
  OR m.`task_id` IS NULL
ORDER BY
  `completed`, t.`name`

If that doesn't work, you can replace `completed` in the ORDER BY with the IF(m.`created` IS NULL, 0, 1) from the column list.

 

Now that we have shoved as much work as possible onto the database (the logic and ordering), we should be able to easily display two tables on the PHP page:

<?php
  $sql = "
    SELECT
      m.`created`, u.*, t.*,
      IF(m.`created` IS NULL, 0, 1) AS `completed`
    FROM `tasks` t
    LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id`
    LEFT JOIN `users` u ON m.`user_id`=u.`user_id` 
    WHERE
      (m.`created`>='2007-11-01 00:00:00' AND m.`created`<'2007-12-01 00:00:00')
      OR m.`task_id` IS NULL
    ORDER BY
      `completed`, t.`name`
  ";
  $q = mysql_query($sql);
  if($q){
    $whichTable = null; // this marks which section we are currently working on: complete or incomplete
    while($row = mysql_fetch_assoc($q)){
      if($row['completed'] !== $whichTable){
        // beginning a section
        // if you are creating tables, you must end any previous table here
        // but ONLY IF $whichTable !== null
        $hdr = $row['completed'] ? 'Completed Tasks'
                                          : 'Remaining Tasks';
        echo "<h1>{$hdr}</h1>";
        // If you are creating a table, begin the table here
        $whichTable = $row['completed'];
      }
      // I'm doing a simple echo, if you were creating tables, you'd create a 
      // table row here
      echo '<pre style="text-align: left;">' . print_r($row, true) . '</pre>';
    }
    // if you are creating tables, close the final table here: echo '</table>'
  }else{
    echo 'Error: ' . mysql_error();
    exit();
  }
?>

 

(edit) There is a small problem here that just occurred to me.  Using the LEFT JOIN as I have done will tell you which tasks have never been completed; it will not tell you which tasks have not been completed within a specific time frame.  For example, my query is selecting tasks that were done in November.  Let's say a specific task occurred in August.  It will not meet any of the conditions in the WHERE clause so it will not show up at all!  To fix this, use a temporary table.  What you do is create a temporary table and copy from `task_user` all the rows between the date range you desire.  Then when writing the main SQL statement, instead of LEFT JOIN'ing with the real `task_user` table, perform the LEFT JOIN on the temporary table.  I can elaborate on this if you need me to.

 

(edit x2) Or if you are using MySQL 4.1 or higher you can use a sub-query.  Please let me know if that is the case.

[fenway]

That's quite the detailed post!  Nice job.

 

Just two comments on the (edits) -- you can limit the time frame in the ON clause, no need for a temporary table.  And most of the time, subqueries are slower than left joins.

[roopurt18]

@fenway

Just two comments on the (edits) -- you can limit the time frame in the ON clause, no need for a temporary table.  And most of the time, subqueries are slower than left joins.

Do you mean like this?

...
LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id` AND
  m.`created`>='2007-11-01 00:00:00' AND
  m.`created`<'2007-12-01 00:00:00'
...

I've done things like that in the past, but somewhere I read that it's best to have only the joining columns present in JOIN statements so I've been avoiding that.

 

As for my sub-query, it would have been something like this:

    SELECT
      m.`created`, u.*, t.*,
      IF(m.`created` IS NULL, 0, 1) AS `completed`
    FROM `tasks` t
    LEFT JOIN (
      SELECT * FROM `task_user` m1 
      WHERE m1.`created`>='2007-11-01 00:00:00' AND 
        m1.`created`<'2007-12-01 00:00:00'
    ) AS m ON t.`task_id`=m.`task_id`
    LEFT JOIN `users` u ON m.`user_id`=u.`user_id` 
    ORDER BY
      `completed`, t.`name`

That effectively eliminates the WHERE clause on the outer query as well.  I guess it depends on the size of `task_user`, but couldn't that potentially be much faster than a very large LEFT JOIN, especially if `task_user`.`created` is indexed?

[samona]

Wow, thanks so much.  I have learned so much just reading what you guys are writing.  Once I get home I will reread everything and spend more time thinking about it.  You guys are awesome and thank you so much. I love this place!!! 

[fenway]

@fenway

Just two comments on the (edits) -- you can limit the time frame in the ON clause, no need for a temporary table.  And most of the time, subqueries are slower than left joins.

Do you mean like this?

...
LEFT JOIN `task_user` m ON t.`task_id`=m.`task_id` AND
  m.`created`>='2007-11-01 00:00:00' AND
  m.`created`<'2007-12-01 00:00:00'
...

I've done things like that in the past, but somewhere I read that it's best to have only the joining columns present in JOIN statements so I've been avoiding that.

Yes, that's what I mean.  Ideally, you don't want to have inequalities (definitely not != or not in) in the join condition, but ranges (i.e. BETWEEN) can be partially optimized, so if it's required for your query, it's fine.

 

As for my sub-query, it would have been something like this:

    SELECT
      m.`created`, u.*, t.*,
      IF(m.`created` IS NULL, 0, 1) AS `completed`
    FROM `tasks` t
    LEFT JOIN (
      SELECT * FROM `task_user` m1 
      WHERE m1.`created`>='2007-11-01 00:00:00' AND 
        m1.`created`<'2007-12-01 00:00:00'
    ) AS m ON t.`task_id`=m.`task_id`
    LEFT JOIN `users` u ON m.`user_id`=u.`user_id` 
    ORDER BY
      `completed`, t.`name`

That effectively eliminates the WHERE clause on the outer query as well.  I guess it depends on the size of `task_user`, but couldn't that potentially be much faster than a very large LEFT JOIN, especially if `task_user`.`created` is indexed?

Oh... I call that a "derived table", to distinguish it from a subquery that refers to the "outer table".  Just be aware that it's still temporary in memory, which means it's not indexed, even though the underlying table is.

[samona]

Ok, theres a problem.  The sql statements now only provide for a form with one task at a time.  But it is possible that the user has completed multiple tasks at the same time.  So how would I insert all the tasks selected in one form into the database when the user clicks submit? 

[roopurt18]

Ok, theres a problem.  The sql statements now only provide for a form with one task at a time.  But it is possible that the user has completed multiple tasks at the same time.  So how would I insert all the tasks selected in one form into the database when the user clicks submit?

 

I don't follow.  The SQL should select all tasks.  What is the statement you are using?

[samona]

I'm trying to put it in the form like:

 

<input type="radio" name="finished_homework" value="yes"/>Yes

<input type="radio" name="finished_homework" value="no"/>No

 

but when i do the sql statement how will it know that the task "finished_homework" is task number 1?  I can't figure out how I would write that sql statement.

 

If i use the insert, then it will just insert "yes" or "no".  And when I do select * where task_id="4" it doesn't print out anything. 

[roopurt18]

I was under the impression that you had a `task` table full of all the possible tasks?  Is this not the case?

 

Can you use phpMyAdmin to export your `task` table?  Or copy and paste a sample of the data contained within?

[samona]

Yes, there are three tables. 

 

1. A task table with taskID and task_description.

2. A user table with user_name and userID.

3. A user_task table with taskID, userID, and Date.

 

task 'finished_homework' has a taskID of 1.

task 'cleaned_my_room' has a taskID of 2.

 

user table has user 'samona' with a userID of 1.

user table has a user 'michael' with a userID of 2.

 

So, should the form look like the following:

 

<input type="radio" name="1" value="yes"/>Yes
<input type="radio" name="1" value="no"/>No

 

 

[roopurt18]

If the only options are yes / no, it makes more sense to use a checkbox for your input.  If you manipulate the name attribute of your form inputs correctly, then you can coerce $_POST into a format that's easier to work with on the server.

 

Your form should look like this IMO:

<form method="post" action="">
  <p>
    <input type="checkbox" name="tasks[1]" value="1" />
    finished_homework
  </p>
  <p>
    <input type="checkbox" name="tasks[2]" value="1" />
    cleaned_my_room
  </p>
  <p>
    <input type="submit" name="Submit" />
  </p>
</form>

 

When you process that form, $_POST will have an index 'tasks' which will be an array.  Each item in the array will be of the form task_id => '1' if the task was done, otherwise it won't appear in the array.  You can then create the values to insert into the DB very easily with:

<?php
  foreach($_POST['tasks'] as $task_id => $val){
    // assumes $user_id is set elsewhere
    // you MUST sanitize $task_id and $user_id, $val is unimportant
    $sql = "
      INSERT INTO `task_user` (`task_id`, `user_id`, `created`) VALUES (
        {$task_id}, {$user_id}, NOW()
      )
    ";
  }
?>

 

That's not really optimal code (or how I would code it anyways), it's just meant to give you an idea.

[samona]

Thanks roopurt, I will try it when I get home.  I don't know how I can thank you and the website for creating this helpful community. Would a donation to phpfreaks help?

[roopurt18]

Well, it doesn't help me!  But I don't do this for personal gain, so sure, help the community instead.

 

[samona]

Can I ask a personal question? Are you a Professor ?

[roopurt18]

Nah.

 

I work for a small company and maintain a php / mysql website with a bit of fancy javascript thrown in, although the current user base is relatively small.

 

I've been told I should consider a career in teaching on many occasions though, or a comedian, psychologist, or one of a few other things I can't think of right now.  The one career no one has ever said I should consider is computer programmer, imagine that.

 

I'm a nerd at heart, but I fool almost everyone into thinking I'm not!

[samona]

You're a really smart guy and I can see that.  But you should consider teaching one day cuz you're really good at explaining stuff. Me I'm new to PHP and mysql.  I'm trying to be as best as I can be, but I know it takes a lot of time.  Maybe one day, I can help you out and answer posts too.  Thanks for everything, and I'm sure I'll have more questions in the future.

 

 

[samona]

<?php
  foreach($_POST['tasks'] as $task_id => $val){
    // assumes $user_id is set elsewhere
    // you MUST sanitize $task_id and $user_id, $val is unimportant
    $sql = "
      INSERT INTO `task_user` (`task_id`, `user_id`, `created`) VALUES (
        {$task_id}, {$user_id}, NOW()
      )
    ";
  }
?>

 

 

at the end of this loop would i do mysql_query($sql), but isn't that running just one query since sql will continue to be written over through each time in the loop?