Data entry statistics: Difference between revisions
(→user) |
No edit summary |
||
Line 11: | Line 11: | ||
4 cleitner XXXXX Carl Leitner cleitner@intrahealth.org 2 | 4 cleitner XXXXX Carl Leitner cleitner@intrahealth.org 2 | ||
</pre> | </pre> | ||
====Lookup id by user name==== | |||
<source lang='sql'> | |||
SELECT id FROM `user` WHERE username='cleitner' | |||
</source> | |||
which will return '4'. | |||
===form=== | ===form=== | ||
This table is used to associate a numeric id to every form stored in the entry tables. Example is: | This table is used to associate a numeric id to every form stored in the entry tables. Example is: | ||
Line 18: | Line 24: | ||
2 person 0 | 2 person 0 | ||
</pre> | </pre> | ||
====Lookup if by form name==== | |||
<source lang='sql'> | |||
SELECT id FROM `form` WHERE name='person' | |||
</source> | |||
will return '2' | |||
====record==== | |||
This table is used to track new instances of a form stored in the form storage mechanism. | |||
== | ==Data Entry Queries== | ||
=== | ===How many new person records added=== | ||
Suppose we want to get the number new people (e.g. new person forms) added since May 22, 2012 we would do something like: | |||
<source lang='sql'> | <source lang='sql'> | ||
SELECT | SELECT count(*) FROM `record` WHERE | ||
form = (select id from form where name='person') | |||
AND | |||
last_modified >= '2012-05-22 00:00:00' | |||
</source> | </source> | ||
Revision as of 09:02, 19 July 2012
All data entry and chagnes for forms with the "entry" form storage mechanism are tracked. However, there is no web interface to look at this data. Instead, we suggest that you run specialized SQL queries in phpmyadmin.
We give some examples below
Tables
The following tables are relevant for making these queries:
user
This table contains a list of usernames and ids. The user's id is what is used to record data changes. Example is:
id username password firstname lastname email creator 4 cleitner XXXXX Carl Leitner cleitner@intrahealth.org 2
Lookup id by user name
<source lang='sql'> SELECT id FROM `user` WHERE username='cleitner' </source> which will return '4'.
form
This table is used to associate a numeric id to every form stored in the entry tables. Example is:
id name type 1 marital_status 0 2 person 0
Lookup if by form name
<source lang='sql'> SELECT id FROM `form` WHERE name='person' </source> will return '2'
record
This table is used to track new instances of a form stored in the form storage mechanism.
Data Entry Queries
How many new person records added
Suppose we want to get the number new people (e.g. new person forms) added since May 22, 2012 we would do something like: <source lang='sql'> SELECT count(*) FROM `record` WHERE form = (select id from form where name='person') AND last_modified >= '2012-05-22 00:00:00' </source>