1

I would like to count and display on my front page how many people have signed a civi petition. This I have been able to do using php as below. What I am wondering is if I am causing any kind of insecurity in my system - if I can get the data out what is to stop someone else inserting data in?

Sorry this may be a 101 question but I haven't been able to find this out by searching. Thanks! Caroline

open php
$sql=("SELECT COUNT(*) FROM civicrm_activity WHERE activity_type_id=32");
$rs = mysql_query($sql);
$result = mysql_fetch_array($rs);
echo "<h1> <b><center>SIGNATURES SO FAR:     $result[0]</center></b> </h1> ";
close php
  • I wonder if you are using Drupal? You could possibly use the Views module to produce this kind of count. – Zachary Sep 16 '15 at 0:22
  • Yes I am using drupal - and I have just had a go at creating a view but it looks like it is a bit more complicated than that. For others who end up here I am now following the instructions civicrm.stackexchange.com/questions/2180/… If I get it working I will come back and accept the answer :)) – Caroline B Sep 16 '15 at 8:21
2

I would highly recommend you use the CiviCRM API to access this information.

A php version would look something like this:

$result = civicrm_api3('Activity', 'get', array(
  'sequential' => 1,
  'activity_type_id' => "Petition",
  'options' => array('limit' => 1000),
));

(Setting limit to be something larger than you would expect to get.)

Which would return the count (in addition to the activities which you can ignore).

{
"is_error":0,
"version":3,
"count":1,
"id":1,
"values":[{
    "id":"1",
    "activity_type_id":"9",
.....

If you are using version 4.6, in place of the get action, use getcount, which will only return the count... without detail and not requiring the limit.

You can play with the call by going to /civicrm/api/explorer.

  • get is capped at 25 by default, and the proper action there would be getcount, but don't think it's optimised for activities yet – Xavier Sep 17 '15 at 6:45
  • Good point Xavier, so the limit on the api call needs to be set sufficiently high. And, the demo 4.6 site does have the getcount action working on activities. (I will add the limit to my answer; but on the assumption of use of earlier versions I will leave getcount here as a comment.) – DaveD - BackOffice Thinking Sep 17 '15 at 13:21
  • It's not a good idea to use getcount in that case, as it's just a get, and as soon as you have a few signatures, it will take too long. Writing a dedicated get_count is the better option, but probably too complicated for the task, the select is the easiest option IMO – Xavier Sep 17 '15 at 20:02
1

you don't have any parameter to the sql command you run, it should be safe.

what you haven't shown that might be a potential problem is how you open the connection to mysql, if you don't do it and let your cms do it, you should be fine, otherwise, be sure you don't risk sharing the connection credentials by accident.

beware that your select counts all the signatures for all the petitions and for all statuses, it might or might not be what you want

  • I have just put the data into a drupal block and put input type as php. Originally I put the connection in a separate file and called it from the block but when I deleted the include it still worked....... So I think I am therefore letting the cms open the connection? Do you agree (and if yes how does it do that I am curious!?) – Caroline B Sep 16 '15 at 8:25
  • it's using the last opened connection. if you have both civi and drupal in the same database, you should be fine, if not, you might end up with running the query on the wrong database, or in general get your counter broken if you add a new drupal module. – Xavier Sep 16 '15 at 9:28
  • ah so if I go down this route I would be better opening the connection by using the include (I put the connection details in another file call it)? Would this still count as letting my CMS do it? Thank you for your response – Caroline B Sep 16 '15 at 11:27
  • If you have Civi and Drupal tables in the same db, instead of using PHP's mysql_query, use the Drupal API's db_query. It's a wrapper around mysql_query that ensures Drupal handles the connection: api.drupal.org/api/drupal/includes!database!database.inc/… – Jon G - Megaphone Tech Sep 16 '15 at 16:59
  • If Drupal and Civi tables are in different databases, you can use the Drupal db_set_active function to have Drupal manage the CiviCRM connection: api.drupal.org/api/drupal/includes!database!database.inc/… – Jon G - Megaphone Tech Sep 16 '15 at 17:01
0

As Zach points out, you should be able to do this via Views and use the Aggregation option (in the advanced section) to pull up a count of the number of Activities of type X in time period Y etc.

-1

Not sure about the security risks, not my expertise. But I would expect it to be a liability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.