Showing no logins

[Teach]

Hello,

I'm after some help please.

In my login.php, a new entry is added into my logins table which consists of this structure:

CREATE TABLE `logins` (
  `id_logins` int(11) NOT NULL auto_increment,
  `dateadded` datetime NOT NULL default '0000-00-00 00:00:00',
  `account` varchar(50) NOT NULL default '',
  `ip_address` varchar(50) NOT NULL default '',
  `reverse` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id_logins`)
) ENGINE=InnoDB AUTO_INCREMENT=441733 DEFAULT CHARSET=latin1 AUTO_INCREMENT=441733 ;

What I want to do is show all the accounts that have not logged in, so basically it should go through the table and select each distinct account and find its last login, and if that is over 2 weeks ago, list it.

I have used the following code, where the top part is finding the individual accounts fine. However, for my second part I'm not quite sure how to deal with it, can you help?

  $login=mysql_query("Select DISTINCT account from logins order by account");
  $num2=mysql_num_rows($login);

  echo "              <ul>\n";
  if ($num2>0)
  {
    echo "                <li>Unique <strong>logins</strong>: ";
    $list = array();
    while ($logins=mysql_fetch_array($login))
    {
      $list[] = "".$logins['account']."";
    }
    echo implode(', ', $list);
    echo "</li>\n";
  }
  echo "              </ul>\n";

  $nologin=mysql_query("Select DISTINCT account from logins WHERE dateadded < now()-interval 2 week order by account");
  $num3=mysql_num_rows($nologin);

  echo "              <ul>\n";
  if ($num3>0)
  {
    echo "                <li>No logins for <strong>2 weeks</strong> from: ";
    $list = array();
    while ($twow=mysql_fetch_array($twoweeks))
    {
      $list[] = "".$twow['account']."";
    }
    echo implode(', ', $list);
    echo "</li>\n";
  }
  echo "              </ul>\n";

[bpat1434]

SELECT DISTINCT l.`account`
FROM `logins` AS l
INNER JOIN (
        SELECT `id_logins`, MAX(`dateadded`)
        FROM `logins`
        GROUP BY `account`)
    USING(`id_logins`) AS l2
WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`

Basically, you select what you want (the distinct accounts) and join a sub-query getting the maximum timestamp. Then just do a quick calculation to select only those rows where the timestamp is less than 2 weeks ago.

[Teach]

Oh wow, thank you! That's complex 😮

I tried that code, but got an error of: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

I tried using:

  $nologin=mysql_query("SELECT DISTINCT l.`account` FROM `logins` AS l INNER JOIN (SELECT `id_logins`, MAX(`dateadded`) FROM `logins` GROUP BY `account`) USING(`id_logins`) AS l2 WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`");
  $num3=mysql_num_rows($nologin);

along with the rest of the code above. Have I used it wrong?

[HalfaBee]

You probably have an error in your sql.

use mysql_error to show where.
I normally use

$result = mysql_query( $sql ) or die( mysql_error() . "<br>\n$sql<br>" );

[Teach]

Thanks for that, I got:

Every derived table must have its own alias
SELECT DISTINCT l.`account` FROM `logins` AS l INNER JOIN (SELECT `id_logins`, MAX(`dateadded`) FROM `logins` GROUP BY `account`) USING(`id_logins`) AS l2 WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`

[bpat1434]

Ah, sorry, that's my fault.

SELECT DISTINCT l.`account`
FROM `logins` AS l
INNER JOIN (
        SELECT l3.`id_logins`, MAX(l3.`dateadded`)
        FROM `logins` l3
        GROUP BY l3.`account`)
    USING(`id_logins`) AS l2
WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`

[Teach]

Hiya!
Thanks again for your support, I realise this is a complicated query. It's went flying over my head for sure 😃

I tried that new code, however, I still get the same:

Every derived table must have its own alias
SELECT DISTINCT l.`account` FROM `logins` AS l INNER JOIN ( SELECT l3.`id_logins`, MAX(l3.`dateadded`) FROM `logins` l3 GROUP BY l3.`account`) USING(`id_logins`) AS l2 WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`

Thank you again

[Teach]

Any ideas on this? Thanks!

[bpat1434]

Sorry, my bad. Have been busy lately. This works:

SELECT DISTINCT l.`account`
FROM `logins` AS l
INNER JOIN (
        SELECT `id_logins`, MAX(`dateadded`) `dateadded`
        FROM `logins`
        GROUP BY `account`) AS l2
    USING(`id_logins`)
WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) >= l2.`dateadded`

[Teach]

Thank you so much, that's working great!

[Teach]

Totally forgot to ask you... would you mind letting me know what the "12" does in this code? Just curious 🙂

[Weedpacket]

It's not [font=monospace]12[/font], it's [font=monospace]l2[/font]. It does exactly what any other alias does in a query (like the [font=monospace]l[/font] earlier).

[Teach]

Ah, my silly font - thanks!
For a moment I thought it would limit it to just 12 😃