By pwhite on

Hi,

I'm trying to work out how many people are joining my site per week - I'm familiar with module development however I don't know how to work out signups per week from the created field in the users table.

Any ideas or hints in the right direction?

Many thanks

⋅ Categories: Drupal 5.x

Comments

mb450’s picture

mb450 commented

select count(uid) from users where date(from_unixtime(created)) between date(date_sub(now(), interval 7 day)) and date(now());

That will get you a count of everyone that has joined in the last 7 days

alternatively

select count(uid) from users where date(from_unixtime(created)) between date(date_sub(now(), interval x day)) and date(date_sub(now(), interval x day));

if you replace the x's above you can be more flexible

pwhite’s picture

pwhite commented

thanks for that - what if I wanted to say a week was from sunday to sunday rather than just counting back 7 days?

j_ten_man’s picture

j_ten_man commented

I think this should get you the users that signed up last week from Sunday to Sunday.

select count(uid) from users where week(from_unixtime(created)) = week(date_sub(now(), interval 1 week));

Here is a way to get all of the users signed up by week in the current year:

select week(from_unixtime(created)) as week, count(uid) from users where year(from_unixtime(created)) = year(now()) group by week(from_unixtime(created), 0);
pwhite’s picture

pwhite commented

Many thanks for that, if its any help to someone here is the a way to graph the number of users joining per week with the use of the Open Flash Charts API Module:

        $data = array();
	$label = array();
	 
	$result = db_query("select week(from_unixtime(created)) as week, count(uid) as thecount from users where year(from_unixtime(created)) = year(now()) group by week(from_unixtime(created), 0)");
	while ($row = db_fetch_object($result)) {
		$data[] = $row->thecount;
		$label[] = $row->week;
	}
	
	$g = new open_flash_chart_api();
	$g->set_title( 'Number of Users Joining Per Week', '{font-size: 20px;}' );
	$g->set_width(650);
	$g->set_height(400);
	
	//
	// BAR CHART:
	//
	$g->set_data( $data );
	$g->bar_filled( 100, '#B5C3CC', '#33383B', 'Users', 10 );
	//
	// ------------------------
	//
	
	//
	// X axis tweeks:
	//
	$g->set_x_labels( $label );
	//
	// set the X axis to show every 2nd label:
	//
	$g->set_x_label_style( 12, '#33383B', 1, 1 );
	//
	// and tick every second value:
	//
	$g->set_x_axis_steps( 2 );
	//
	$g->set_x_axis_colour( '#A6CAD8','#CBEBF8');
	$g->set_y_axis_colour( '#A6CAD8','#A6CAD8');
	
	$g->set_y_max( 500 ); 
	$g->set_y_label_steps( 5 );
	$g->set_y_legend( 'Number Joining', 12, '#33383B' );
	$g->set_x_legend( 'Week Number', 12, '#33383B' );

	$g->set_bg_colour('#CBEBF8');
	$output .= $g->render();
HowardLake’s picture

HowardLake commented

Thanks, this is useful. But where would I post the code listed above:

" select count(uid) from users where date(from_unixtime(created)) between date(date_sub(now(), interval 7 day)) and date(now()); "

In a template page? Block? View?

I tried adding it in a panel but it just threw up errors. Could well have been my lack of understanding of php though.

Thanks.

Howard Lake
www.fundraising.co.uk

lanexa’s picture

lanexa commented

Hope this helps someone. I created a block, then used Panels to add this block to my Panel page.

<?php

$result = db_fetch_array(db_query("select count(uid) from users where week(from_unixtime(created)) = week(date_sub(now(), interval 1 week))"));

print $result['count(uid)'];

?>

Chris
http://www.lanexa.net