count multiple values in one column(mysql) PHP (Rs form joomla)

Question

i want to count multiple values from 1 column(FieldValue) using PHP mysqli, and I can't do it.

`SubmissionValueId`       `FormId`     `SubmissionId`     `FieldName`       `FieldValue`
   100567                    33               1               nam1              A
   100567                    33               2               nam2              B
   100567                    33               3               nam3              A
   100567                    33               4               nam4              B
   100567                    33               5               nam5              A
   100567                    33               6               nam6              C
   100567                    33               7               nam7              B
   100567                    33               8               nam8              C
   100567                    29               8               nam8              D
   100567                    30               8               nam8              F
   100567                    25               8               nam8              C

my code is:

$dba = JFactory::getDbo();
$dbb = JFactory::getDbo();
$dbc = JFactory::getDbo();
$dba->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='A' AND FormId =33");
$dbb->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='B' AND FormId =33");
$dbc->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='C' AND FormId =33");
$submissionsa = $dba->loadResult();
$submissionsb = $dbb->loadResult();
$submissionsc = $dbc->loadResult();

$formLayout = '<div id="counter">Submissions-A:'.$submissionsa.'</div><div id="counter">Submissions-B:'.$submissionsb.'</div><div id="counter">Submissions-C:'.$submissionsc.'</div>';

and the result is: Submissions-A=3 Submissions-B= Submissions-C=

BUT i expect this:

Submissions-A = 3
Submissions-B = 3
Submissions-C = 2

Answer 1

It seems Joomla database class use memory or session to get last query. Get result one by one:

$dba = JFactory::getDbo();
$dbb = JFactory::getDbo();
$dbc = JFactory::getDbo();
$dba->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='A' AND FormId =33");
$submissionsa = $dba->loadResult();
$dbb->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='B' AND FormId =33");
$submissionsb = $dbb->loadResult();
$dbc->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='C' AND FormId =33");
$submissionsc = $dbc->loadResult();

Or simply, use one instance of database object

$db = JFactory::getDbo();
$db->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='A' AND FormId =33");
$submissionsa = $db->loadResult();
$db->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='B' AND FormId =33");
$submissionsb = $db->loadResult();
$db->setQuery("SELECT COUNT(`SubmissionValueId`) FROM #__submission_values WHERE FieldValue ='C' AND FormId =33");
$submissionsc = $db->loadResult();