Show number of new members who have signed up
15 posts by 3 authors in: Forums > CMS Builder
Last Post: April 15, 2016 (RSS)
I would like to regularly check on how many members have signed up to my site each day. Is there a way to run a query that could appear on a page and show me how many new members have signed up in a certain period of time? Alternatively if it's easier is there a way to show this in the CMS somehow so I don't have to manually count each day?
By Damon - March 7, 2016
Hi,
Yes, you can show the number of signups on the frontend using a date search query on the accounts data.
Here is some example code. Use the Load Records code from you
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
// load viewer library
$libraryPath = 'cmsb/lib/viewer_functions.php';
$dirsToCheck = array('C:/www/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'limit' => '100',
'allowSearch' => true,
));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<style type="text/css">
body {
font-family: arial;
}
</style>
</head>
<body>
<h2>View User Account Signups</h2>
<a href="?createdDate_year_min=<?php echo date("Y"); ?>&createdDate_month_min=<?php echo date("m"); ?>&createdDate_day_min=<?php echo date("d"); ?>">Users signup today</a><br />
<br />
<?php $counter = ""; ?>
<?php foreach ($accountsRecords as $record): ?>
<?php echo htmlencode($record['fullname']) ?> - created: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['createdDate'])) ?><br/>
<?php $counter++ ;?>
<?php endforeach ?>
<br />
<strong>Total Signups: <?php echo $counter; ?></strong> <br />
<?php if (!$accountsRecords): ?>
No user signups found!<br/>
<br/>
<?php endif ?>
</body>
</html>
This will display the 100 must recent user signups. I also includes code that will get todays date and pass it into a search query like this which will filter the list and only show signups from today:
http://example.com/user_signup_search.php?createdDate_year_min=2016&createdDate_month_min=03&createdDate_day_min=07
You can then change the day in the string to see yesterday signups for example.
Adjust the code to match your setup and try this out to see if it gets the results you are after.
Damon Edis - interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Thank you!
I've given this a try and I get a full list of all members with the number signed up totalled at the bottom. The date filtering doesn't appear to be working
By ross - March 11, 2016
Hi there.
The code Damon provided will give you all your account records.
To get a list of accounts created on a specific date, you'll need to add a "WHERE" statement here:
// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'limit' => '100',
'allowSearch' => true,
'where' => "createdDate > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;"
));
Does that make sense?
Give that a shot and let me know how you make out.
Thanks!
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com
Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/
I've added the extra line in, however now it's not working and displaying the following error:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY score DESC;) ORDER BY fullname, username' at line 3
By Damon - March 15, 2016
Did you use the link in the code that would add this search query?
?createdDate_year_min=2016&createdDate_month_min=03&createdDate_day_min=15
The code add the current date into the link so you can see who signed up today.
If this doesn't work, can you attach the code that you are using and let me know what happens when you click the "Users signup today" link?
Damon Edis - interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Yes I clicked the link that added that in, when I do that nothing happens. It still just displays the same full list of members.
The URL changes to: test_admin.php?createdDate_year_min=2016&createdDate_month_min=03&createdDate_day_min=26
See my code below. I removed the additional "WHERE" code you added below as the page doesn't work when that's included
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
// load viewer library
$libraryPath = 'cms/lib/viewer_functions.php';
$dirsToCheck = array('/home/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
// load records from 'aacounts'
list($aacountsRecords, $aacountsMetaData) = getRecords(array(
'tableName' => 'aacounts',
'loadUploads' => true,
'allowSearch' => false,
));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<style type="text/css">
body {
font-family: arial;
}
</style>
</head>
<body>
<h2>View User Account Signups</h2>
<a href="?>createdDate_year_min=<?php echo date("Y"); ?>&createdDate_month_min=<?php echo date("m"); ?>&createdDate_day_min=<?php echo date("d"); ?>">Users signup today</a><br />
<br />
<?php $counter = ""; ?>
<?php foreach ($aacountsRecords as $record): ?>
<?php echo htmlencode($record['fullname']) ?> - created: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['createdDate'])) ?><br/>
<?php $counter++ ;?>
<?php endforeach ?>
<br />
<strong>Total Signups: <?php echo $counter; ?></strong> <br />
<?php if (!$aacountsRecords): ?>
No user signups found!<br/>
<br/>
<?php endif ?>
</body>
</html>
By Damon - March 28, 2016
Hi,
I made some changes to your code in bold and it works for me.
You had the tableName set as aacounts when it should be accounts. Also you has allowSearch set to false.
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
// load viewer library
$libraryPath = 'cms/lib/viewer_functions.php';
$dirsToCheck = array('/home/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'loadUploads' => true,
'allowSearch' => true,
));
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<style type="text/css">
body {
font-family: arial;
}
</style>
</head>
<body>
<h2>View User Account Signups</h2>
<a href="?createdDate_year_min=<?php echo date("Y"); ?>&createdDate_month_min=<?php echo date("m"); ?>&createdDate_day_min=<?php echo date("d"); ?>">Users signup today</a><br />
<br />
<?php $counter = ""; ?>
<?php foreach ($accountsRecords as $record): ?>
<?php echo htmlencode($record['fullname']) ?> - created: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['createdDate'])) ?><br/>
<?php $counter++ ;?>
<?php endforeach ?>
<br />
<strong>Total Signups: <?php echo $counter; ?></strong> <br />
<?php if (!$accountsRecords): ?>
No user signups found!<br/>
<br/>
<?php endif ?>
</body>
</html>
Can you try this and let me know if it works for you?
Damon Edis - interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
In my CMS my members are in a separate tables called "aacounts". The other update you made though has got it working perfectly now. Thank you!
If I also want to be able to make a link where you can click to see how many accounts signed up in the last week how do I do this?
Is there anyway to display the information without having to click on the links to generate the sign up number? So for example I wanted to have a look at how many people have signed up for April, March, February and it was in a list with the sign up number next to it (rather than me having to click on a link to show each)?
By Damon - April 13, 2016
Try this code to show signups by month:
Total Signups for: <br />
April: <?php echo mysql_count('aaccounts',"createdDate BETWEEN '2016-4-1' AND '2016-4-30'"); ?><br />
March: <?php echo mysql_count('aaccounts',"createdDate BETWEEN '2016-3-1' AND '2016-3-31'"); ?><br />
February: <?php echo mysql_count('aaccounts',"createdDate BETWEEN '2016-2-1' AND '2016-2-29'"); ?><br />
Damon Edis - interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/