Skip to main content
2 votes

How to retrieve RSForm submission data grouped by SubmissionId?

You actually don't need to perform multiple joins with the same table to gather your desired results. What is best suited is a "pivot" technique. There is no cleaner way to deal with rsform's ...
mickmackusa's user avatar
  • 4,791
2 votes
Accepted

How to refactor two select queries on RSForm submission data?

Here is what I believe the mySQL query to be with (1,2,3) substituted in for your imploding of results because I wanted to check the mySql syntax of it. I don't have time to convert into PHP right ...
YellowWebMonkey's user avatar
2 votes
Accepted

PlotALOT RSForm Query showing only one result

From this sample data: INSERT INTO `o2pe0_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES (2124732, 23, 47529, 'SHELTER', 'Bellville'), (...
mickmackusa's user avatar
  • 4,791
1 vote
Accepted

Joomla mysql query - group_contact

I really can't see the use in retrieving the SubmissionValueIds or FormIds associated with the rows of data after you pivot into an associative structure. When you fold the original rows together, ...
mickmackusa's user avatar
  • 4,791
1 vote

How to join custom fields data to users table without extra rows in result set

To better understand your scenario, I've created this db-fiddle with your sample data and made a basic SQL query to display the JOINed data as a tabular result set. SELECT u.id, u.name, u.email, ugm....
mickmackusa's user avatar
  • 4,791
1 vote
Accepted

PieChart For RSForm for PlotaLot based on a dropdown field

Add a HAVING clause that pre-qualifies aggregrate data in your derived table. The following condition will require that a submission contains a Part No value of 5. HAVING COUNT(FieldName = 'Part No' ...
mickmackusa's user avatar
  • 4,791
1 vote

Show article fields in module

Assuming this is for Joomla 3, you can use the following: use Joomla\CMS\Factory; use Joomla\CMS\MVC\Model\BaseDatabaseModel; \JLoader::register('FieldsHelper', JPATH_ADMINISTRATOR . '/components/...
Lodder's user avatar
  • 26.9k
1 vote
Accepted

Join #__content with #__fields_values then filter and sort using #__fields_values data

I have reverse engineered a minimal verifiable example on db-fiddle.com based on your question and answer details. Using my reduced and generic sample data with the sql in your answer, the output is: (...
mickmackusa's user avatar
  • 4,791
1 vote
Accepted

RSFORM combine 2 submission form results in one Plotalot view

I think the simplest way forward is to use a JOIN to relate your first query to your second query. An INNER JOIN (same as JOIN) will only generate rows in the result set where both "derived"...
mickmackusa's user avatar
  • 4,791
1 vote
Accepted

Charts/Graphs for RSFORM using mysql for VChart and/or PlotaLot

This will rely heavily on the magic of a "pivot" technique. Here are some more examples in JSE to compare/contrast with https://joomla.stackexchange.com/questions/tagged/pivot. Basically, a ...
mickmackusa's user avatar
  • 4,791
1 vote

How to get Custom Fields related to Joomla Content?

Let's say you have two custom fields in your database. In the #__fields_values table: 1 relates to the custom field called "genre" 2 relates to the custom field called "somethingelse" To use pure ...
mickmackusa's user avatar
  • 4,791
1 vote

How to refactor two select queries on RSForm submission data?

Yes, your queries can certainly use refactoring. Using two separate calls on the same table is asking MySQL to do needless extra work. To convert your queries into plain English, you want to: ...
mickmackusa's user avatar
  • 4,791
1 vote

How to re-write sql for joomla JDatabase

If you are new, then I'd like to include some basic error checking as well as printing out the generated query and the 2-column resultset. Untested Code: $db = JFactory::getDBO(); try { $query =...
mickmackusa's user avatar
  • 4,791
1 vote

How to re-write sql for joomla JDatabase

I think you are asking, Joomla code to execute query ? if yes $db = JFactory::getDBo(); $query = $db->getQuery(true); $query->select("MAX(CASE WHEN v.FieldName=".$db->q('Team_Name')." THEN ...
Alagesan's user avatar
  • 111
1 vote

How to retrieve RSForm submission data grouped by SubmissionId?

Here you are: $query->select($db->qn('s.SubmissionId','id')) ->select($db->qn('s.DateSubmitted','date')) ->select($db->qn('sv.FieldValue','status')) ->...
Alexandr's user avatar
  • 1,130
1 vote

Joomla 3.3.6 Union SQL Issue

A few points: You have really made a rod for your back by not "normalizing" your org table. My strong recommendation is to restructure your org table to have a type column versus three static ...
mickmackusa's user avatar
  • 4,791

Only top scored, non community-wiki answers of a minimum length are eligible