0

I manage a large Drupal7 website for a radio station and I built a few custom PHP pages that piggy back onto Drupal's webform module for dealing with giveaways (we give away tickets, CDs, books, etc. a few times a week, listeners submit an online form, etc). Folks submit a single Drupal form, the form contains a dropdown menu for the giveaway items where the key is the nid of the giveaway item node. Our staff then uses my PHP script to select giveaway winners at random and then they email them.

The PHP script is getting slower and slower and it's because the database of submissions is getting really really big.

Do you think it's alright to delete most of the older entries in the database table? BTW, there's a separate table that stores only the giveaway winners in case we need to pull up their contact info.

What's the easiest/best way to do that? We can probably assume that all entries from 2017 and ealier are no longer needed. I would want to use phpMyAdmin.

Drupal's webform module works by putting all webform submission in one table, _webform_submissions, and all data in another table, _webform_submitted_data. Both have a nid field so I could say if nid=30 maybe, but only the first one has a date file ('submitted')

Maybe this?

DELETE FROM live_webform_submissions WHERE nid=30 AND submitted < 1483228800;

1483228800 = 1/1/17

And should I only shrink the live_webform_submissions table and not the _webform_submitted_data table? That may depend on what webform_get_submissions() sorts through.

I can test it on a testing copy of the site first before doing this operation to the live table. Save a backup first of course.

The PHP script works by getting all submissions via the webform_get_submissions() function, with a filter for the giveaways form (that is used for all giveaway items). Then my code sorts out the ones for the specific giveaway item via it's nid - this is where there's some major slow down probably.

Here's how that works (the actual code is a lot longer, message me if you would like to see the whole thing)

function print_giveaway_winners($nid) {
  $form = node_load(30); // giveaway entry is always for one form, node # 30
  $num = $_REQUEST['number']; 

  print "<p>Number of winners to choose: ".$num."<br />";

  // first we need to get all submission for the 
  $submissions = webform_get_submissions($form->nid);

  // filter out ones that are for our giveaway $nid
  $maybes = array();
  foreach($submissions as $submission) {
    if ($submission->data[1]['value'][0] == $nid) {
      $maybes[] = $submission;
    }
  }

  // unpublish it now
  unpublish_node($nid);

  $winners = array();
  for ($i=0; $i<$num; $i++) {

    $winner = get_giveaway_winner($maybes);
    $output = webform_submission_render($form, $winner, NULL, 'html');
    print render($output);
}

// choose a random winner from an array of potential winners
function get_giveaway_winner(&$maybes) {

  // choose one at random
  $rand_key = array_rand($maybes, 1);
  $winner = $maybes[$rand_key];

  // remove it from the list of potential winners so it can't be selected again
  unset($maybes[$rand_key]);

  return $winner;
}
  • 1
    That will work. I believe there's a configuration option to store only X total submissions. But yes, eventually you need to purge or manage entries yourself in some cases. I have done this as well. – tenken Feb 27 '18 at 17:28
  • @tenken unless yes there's an option to limit the total number of submissions but I don't think that's what I want. I'm not sure but I think it would close the webform once that limit is reached. Right? – Dan Mantyla Feb 27 '18 at 17:31
  • @Dan, do you use the same webform for all contests every year? If so, you may want to consider using the node-clone module in conjunction with webform so you can use one form per contest moving forward (archiving/unpublishing the old form/closing it from accepting submissions). You could also consider setting up a php script that routinely truncates the submission table between contests. – Anson W Han Feb 28 '18 at 0:04

Your Answer

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

Browse other questions tagged or ask your own question.