Use Formit Submissions in a live graph

[tm2000]

Hi all!

So I’ve been asked to create a voting system where members vote for either A, B or C.
I’ve got it all setup and working - but they have now asked if they can see the progress of who’s ‘winning’ (A,B or C).

So I was wondering if it’s possible to create a live graph from the form submissions to show the progess of the voting.

The submissions are being saved in the database so there must be a way to do this, but I’m not very good with PHp so not sure where to start.

Any ideas?

Thanks in advance!

Andy

[halftrainedharry]

@tm2000

The submissions are being saved in the database

How exactly do you save the submissions? Do you have a custom database table or do you use the FormItSaveForm hook?

---

What Javascript library are you intending to use for displaying the graph?
Or asked differently: In what format to you need the data to be?

[tm2000]

Thanks Harry,

So i’m using the hook ‘FormItSaveForm’ to save all the submissions.
To be honest, I’m not worried too much about the display - it could just show a simple number of votes for each option.

So as simple as:

A = 20 votes
B = 30 votes
C = 40 votes

A graph would be nice and fancy, but it’s not really necessary I guess!

[halftrainedharry]

FormItSaveForm (database table modx_formit_forms) is not ideal to query the data, as all the form data is saved as JSON in the column values.

You probably have to loop through all the rows of the correct form (column form), decode the JSON and tally up the submissions.

Here is a link to some similar code, that you could adapt to your needs.

Json to placeholders

Here is some code to loop the forms: <?php //load FormIt package $base_path = $modx->getOption('core_path') . 'components/formit/'; $modx->addPackage('formit', $base_path . 'model/'); //create a query and restrict the rows by the 'form' column $q = $modx->newQuery('FormItForm'); $q->where(array( 'form' => 'test retriever' )); $output = ''; $saved_forms = $modx->getCollection('FormItForm', $q); foreach($saved_forms as $saved_form){ $values_json = $saved_form->get('values'); //read valu…

[tm2000]

Ok thanks Harry,

I’ve had a play, and I’ve got as far as showing all the submissions individually on the page using the snippet you kindly sent.

Are you saying that it could fall over because this is an inefficient way of doing it?
There could be a few thousand submissions.

I also need to total them up, as at the moment it’s just showing me each one as a row.

Thanks
Andy

[halftrainedharry]

@tm2000

this is an inefficient way of doing it?

Yes, this is inefficient. And you also have to run the snippet uncached as the data is always changing.

If you really have “few thousand submissions”, then it’s probably a better idea to save the data in a custom database table. Or adding another hook-snippet, that just keeps track of the total values, increments them and saves them somewhere.

---

@tm2000

I also need to total them up, as at the moment it’s just showing me each one as a row.

Yes, you have to do that in the foreach loop.

[tm2000]

Hi Harry,

Ok thanks for the advice.
So seeing as I only need to know the number of votes for each option (A,B or C) I think the hook idea is probably the best.

but it’s not just a total - it’s looking at the dropdown in the form and seeing whether people submitted A B or C, and then giving a total for each.

Will scour the forums to see if I can work out how to do that.

[tm2000]

Right OK so I don’t have the necessary knowledge/ability to work this one out for myself!!

Is it easier to save all submissions into a new table, and then calculate the votes on a new page, rather than trying to create a custom Hook?

[halftrainedharry]

You have to create a custom hook either way.

---

Here is some example code

Create a new database table (for example with phpMyAdmin) with an SQL query like this

CREATE TABLE IF NOT EXISTS `modx_tm2000_vote` (
    `option` varchar(150) NOT NULL,
	`total` int unsigned NOT NULL default 0,
    PRIMARY KEY (`option`)
)

and add rows for the options A, B and C.

---

Create a page with a form like this:

[[!FormIt?
   &hooks=`saveVote`
   &validate=`vote:required`
   &successMessage=`Your vote was submitted!`
   &submitVar=`mySubmit`
]]

[[!+fi.successMessage:notempty=`<p class="success">[[!+fi.successMessage]]</p>`]]

<form action="[[~[[*id]]]]" method="post">
    <label for="vote">Your vote:</label>
    <select id="vote" name="vote">
        <option value="A">A</option>
        <option value="B">B</option>
        <option value="C">C</option>
    </select>
    <button type="submit" name="mySubmit" value="1">Send</button>
</form>

<hr>
[[!getVotes]]

---

In the hook snippet saveVote update the total value in the database.

<?php
$vote = $hook->getValue('vote'); //read value of the 'vote' form field
$stmt = $modx->prepare("UPDATE `modx_tm2000_vote` SET `total` = `total` + 1 WHERE `option` = ?");
if ($stmt->execute([$vote]) && $stmt->rowCount() == 1){
    return true;
} else {
    return false;
}

---

Show the totals with the getVotes snippet.

<?php
$stmt = $modx->prepare("SELECT * FROM `modx_tm2000_vote` ORDER BY `option` ASC");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$output = [];
foreach ($rows as $row) {
    $output[] = $row['option'] . '=' . $row['total'] . " votes";
}
return implode('<br>', $output);

---

This is of course just one possible solution.
You could also insert a new row for every vote into the database table, and then use COUNT() and GROUP BY in the getVotes snippet.
Or create a proper model for the custom database table, so that you can use xPDO.
Or store the totals in a file instead of a database table, etc.

[tm2000]

Crikey Harry - that’s amazing. I obvisouly haven’t gone through it yet, but just wanted to quickly thank you for taking the time out to do this. It’s very much appreciated.

I’ll have a go at implimenting this and let you know how it works.

Thanks so much!!

Andy

[bwente]

Would Rowboat suit your needs?

https://docs.modx.com/3.x/en/extras/rowboat/rowboat

[tm2000]

OK hat’s off to Harry here. That worked perfectly.
I’ve moved the resutls to a private page, so that users can’t see the votes, and I’ve also added another custom hook to prevent people from voting over and over again - and it’s perfect - thanks Harry so much - I’ve learnt a lot on this one!

Thanks
Andy

[quentin100]

Hi Andy,

Just a suggestion, but you could show the vote progress to the users as well on (successful) submit if you wanted to, without having a private page, by adding the getVote snippet into the successMessage.

[[!+fi.successMessage:notempty=`<p class="success">[[!+fi.successMessage]] 
<hr> [[!getVotes]]</p>`]]

Kudos to @halftrainedharry for a nice, clean and quick solution with full code.

[tm2000]

Thanks - yes I will bear that in mind too. It was an amazing and detailed solution from Harry.