Counting Number of Log Ins

[Xtremer360]

One more question to ask. I'm trying to do a count of total logins that have happened in the last 5 months including the current one so far.

 

My users_logins table has the following structure: users_id, session_id, login_date(datetime), last_activity(datetime).

 

What the end result is going to be is:

 

<tr>

    <td></td>

    <th scope="col"><?php echo date("M", strtotime("-4 month")); ?></th>

    <th scope="col"><?php echo date("M", strtotime("-3 month")); ?></th>

    <th scope="col"><?php echo date("M", strtotime("-2 month")); ?></th>

    <th scope="col"><?php echo date("M", strtotime("-1 month")); ?></th>

    <th scope="col"><?php echo date("M"); ?></th>

</tr>

<tr>

    <th scope="row">Logins</th>

    <td>94</td>

    <td>53</td>

    <td>124</td>

    <td>92</td>

    <td>105</td>

</tr>

 

Where the first td would be May and the last td would be September. Do I have to do this in 4 separate queries. If so here's what I have for the first query. I'm not sure what woudl go for the WHERE.

 

$four_months_ago_query = "SELCT COUNT(date_login) FROM users_logins WHERE ? ";

[joel24]

couldn't you group it by month and retrieve only the last 5 months...?

 

SELECT COUNT(date_login) FROM users_logins WHERE login_date >= NOW() - INTERVAL 5 MONTH

GROUP BY MONTH(date_login)

[Xtremer360]

That looks good but I'm going to be printing the rows differently as you can see with my html table.  How woudl I print them differently. I'm sure do it as a foreach but what from there?

[Xtremer360]

I tried that code but lets say I change it to do:

 

SELECT COUNT( login_date )
FROM users_logins
WHERE login_date >= NOW( ) - INTERVAL 5
DAY
GROUP BY DAY( login_date )

 

Because after thinking about ti I want it to display the last 5 days instead. The query works as well. However since I've displaying the last 5 days how can I get it to show "0" for the last 2 days since there won't be any data for those days yet.

 

 

[joel24]

why would you want to show those days if there is no data? and why isn't there any data?

[Xtremer360]

Because I'm trying to display the number of logins for the last 5 days. Which means its either going to have data or there's going to be a 0. I need it to display the 0 for that day.

[joel24]

you'll need to group by year, month, day - otherwise you'll get logins from the 2nd Jan and 2nd Feb and 2nd March etc being counted as one...

for those days with no logins I would just use PHP to determine if that date had no logins, display zero...

[Xtremer360]

I would I do that though is my question.

[Xtremer360]

I don't now how my message posted twice.

[monkeytooth]

What you really need to do is take a giant stick and shove it up your grande ass.. I mean uh.. I mean..

 

 

$homo= "SELECT * FROM users_logins WHERE login_date BETWEEN NOW() AND DATE_SUB(login_date , INTERVAL 5 day)"; 
$lesbians = mysql_query($homo) or die(mysql_error());

$totals = array();
while($yourMom = mysql_fetch_array($lesbians)){
      $z = 0;
      while($x=1;$x < 5;$++)
      {

          $compareTimes = strtotime(date('Y-m-d H:i:s') . ' +1 day');
          if(strtotime($yourMom['login_date']) <= strtotime('-".$x." day"))
          {
             $z++;
          }
      }
  array_push($totals, $z);
}


foreach($totals as $counted)
{
     echo $counted."<br>";
}

 

now will this work? I have no idea.. just kinda came up on this in a whim its a bit sloppy but it could work..