Thread: Track your signups

Last 24h's Active Signups:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) <= 1

Last 24h's Unconfirmed Signups:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Unconfirmed' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) <= 1

Last 24h's Logins:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastLoggedIn)) <= 1

Last 24h's Sent Messages:
SELECT COUNT(ID) FROM `Messages` WHERE (TO_DAYS(NOW()) - TO_DAYS(Date)) <= 1

Today's Active Signups:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) < 1

Today's Unconfirmed Signups:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Unconfirmed' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) < 1

Today's Logins:
SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastLoggedIn)) < 1

Today's Sent Messages:
SELECT COUNT(ID) FROM `Messages` WHERE (TO_DAYS(NOW()) - TO_DAYS(Date)) < 1

And the php to display it? If you have the time, of course.

I'm a greedy bastard - huh?

SMoge

My input - works for me. Put them within Site Statistics on admin index page

Edit: admin/index.php

above // number of articles insert:

Code:

$total_active_24 = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) <= 1" );
$total_unconfirmed_24 = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Unconfirmed' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) <= 1" );
$total_login_24 = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastLoggedIn)) <= 1" );
$total_sentmsg_24 = db_arr( "SELECT COUNT(ID) FROM `Messages` WHERE (TO_DAYS(NOW()) - TO_DAYS(Date)) <= 1" );
$total_active_today = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) < 1" );
$total_unconfirmed_today = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Unconfirmed' AND (TO_DAYS(NOW()) - TO_DAYS(LastReg)) < 1" );
$total_login_today = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE Status = 'Active' AND (TO_DAYS(NOW()) - TO_DAYS(LastLoggedIn)) < 1" );
$total_sentmsg_today = db_arr( "SELECT COUNT(ID) FROM `Messages` WHERE (TO_DAYS(NOW()) - TO_DAYS(Date)) < 1" );

After:

Code:

<!--  Site statistics  Begin-->

		  <div style="text-align: left; padding: 6px 6px 0 8px; background: url(images/head_block_green.gif) no-repeat 0px 6px; font: bold 10px Verdana; color:#FFFFFF; height:21px; line-height:16px">Site statistics</div>
		  <div style="text-align: left; border-left: 2px solid #2AA181; border-right: 2px solid #2AA181; width:598px; height:auto">
			   <div style="padding: 10px 10px 10px 10px">
               <center> <table bgcolor=CCCCCC width=70% cellspacing=0 cellpadding=1 align="center" border="0">
				<tr>
				<td>
                <table width=100% class=text cellpadding=3 cellspacing=1>

insert:

Code:

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Confirmed within last 24 hours</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_active_24[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Unconfirmed within last 24 hours</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_unconfirmed_24[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Logins within last 24 hours</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_login_24[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Sent messages within last 24 hours</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_sentmsg_24[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Confirmed today</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_active_today[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Unconfirmed today</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_unconfirmed_today[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Logins today</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_login_today[0]; ?></td>
</tr>

<tr>
	<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Sent messages today</font></td>
	<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_sentmsg_today[0]; ?></td>
</tr>

although you can insert the table rows below other statistics if required.

DONT FORGET - BACKUP ADMIN/INDEX.PHP AND DB BEFORE MAKING ANY MODS

**UPDATED**

Add the code below to the top of admin\index.php (replace 4 with the ID of your 'Gold' member level)

Version 4.02

Code:

$total_gold = db_arr( "SELECT COUNT(ID) FROM `Profiles` WHERE mem_level = '4' ");

Version 4.1

Code:

$total_gold = db_arr( "SELECT COUNT(IDMember) FROM `ProfileMemLevels` WHERE IDLevel = '4' AND DateExpires > Now()");

Add the code below to site statistic section of admin/index.php

Code:

<tr>
<td bgcolor=FFFFFF align="left">&nbsp;&nbsp;&nbsp;&nbsp;<img src=images/arrow.gif><font color=0000FF>Total Gold Members</font></td>
<td bgcolor=FFFFFF width=50 align="right"><?php echo $total_gold[0]; ?></td>
</tr>

Note: This will work for 4.02 - I believe the SQL code is slightly different for 4.1 - Confirm?

Right - Just checked a Dev DB I remembered I had for 4.1

Code:

$total_gold = db_arr( "SELECT COUNT(IDMember) FROM `ProfileMemLevels` WHERE IDLevel = '4' ");

I think we should tidy this thread up once confirmed as well.

All the other code works but i am guessing i need to figure out the right path so it can grab the list of gold members wich would be Id's of 4, 5 and 6 as i have 3 different memberships. WEll they are all the same but 1 is check anther is cc and another is telebill

$total_gold = db_arr( "SELECT COUNT(IDMember) FROM `ProfileMemLevels` WHERE IDLevel = '4' Or IDLevel = '5' Or IDLevel = '6' ");

Ah..

ProfileMemLevels contains all memberships, including expired one - sorry. When I get enough spare time I'm going to take a look at 4.1

Right, so we need to select all 'current' memberships.

How about:

$total_gold = db_arr( "SELECT COUNT(IDMember) FROM `ProfileMemLevels` WHERE IDLevel = '4' Or IDLevel = '5' Or IDLevel = '6' AND DateExpires > Now()");


This will only give you a count of memberships where DateExpires if after Now()

Not the wrong table, just didn't spot the DateExpires column.

If you have a look at the data in the table, are there old transactions? Members who's membership has expired?

What happened to your counting on gold members? I suppose Gregger's last code should work.