Questions tagged [pivot]

This tag is for questions that deal with PIVOT syntax which converts row data into columnar data. This technique is commonplace when collecting one-to-many rows from tables such as Joomla's `#__fields_values`.

Filter by
Sorted by
Tagged with
1
vote
1answer
49 views

Create Plotalot chart from RSForm Pro data using pivot query

I'm trying to extract RSForm Pro form data from the database to create Plotalot charts. There is a field on the form called "resolved" to which users respond "Yes or No". I would ...
1
vote
3answers
128 views

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

I wrote an override in Joomla 3.X and sorted the result from the DB using this query, which works: $query = "SELECT content.*, fieldvalues.* FROM #__content AS content, #...
1
vote
2answers
60 views

How to count aggregate data from RSForm submission values with Joomla's query building methods?

user SubmissionId FormId FieldName FieldValue bob 2 16 state mystate1 bob 2 16 qda myqad1 bob 2 16 city mycity1 rob 3 16 state mystate2 rob 3 16 qda myqad1 rob 3 16 city mycity1 jac 4 16 state ...
1
vote
1answer
80 views

RSFORM combine 2 submission form results in one Plotalot view

After requesting help from local db-masters with no result, I would like to ask this question here. The output required is this for example: Shelter ClientID Year Month Fees Amount ...
2
votes
1answer
81 views

PlotALOT RSForm Query showing only one result

After being helped very well by Stack community and learning a bit I created a query to show the sum of payments per client. A client can pay multiple times. I would like to know the sum of payments ...
1
vote
1answer
159 views

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

I am working for a homeless organisation in Capetown. We are using RSForms to register our clients (about 3500 per year). We have a sizable database, but it's very hard to create a dashboard. We are ...
1
vote
1answer
78 views

How to collect multi-row data from #__fields_values and assign specific aliases for each value in the result set?

I have the following (working) query to get name, username and the value of a custom field: // Get a db connection. $db = JFactory::getDbo(); // Create a new query object. $query = $db->getQuery(...
3
votes
2answers
575 views

How to get Custom Fields related to Joomla Content?

I want to know how they get the values of custom user fields within Joomla articles. This is to contribute ideas and ways to solve it.
2
votes
2answers
260 views

How to refactor two select queries on RSForm submission data?

I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using the "Sourcerer" plugin to add custom PHP ...
1
vote
2answers
107 views

How to re-write sql for joomla JDatabase

I am new to custom Joomla coding so excuse me for this, I want to execute the following statement: "SELECT MAX(CASE WHEN v.FieldName = 'Team_Name' THEN v.FieldValue END) 'Team_Name' , MAX(...
2
votes
2answers
395 views

How to retrieve RSForm submission data grouped by SubmissionId?

I have a table with 3 columns: SubmissionId, FieldName and FieldValue. 1) SubmissionId keeps ID submission. 2) FieldName stores field name of a form. 3) FieldValue stores field value of form field. ...
2
votes
3answers
286 views

Joomla 3.3.6 Union SQL Issue

I am having trouble using the union functions in Joomla. This is the SQL I need: SELECT * FROM (SELECT o.title AS org_name, u1.name AS username, 'president' AS `type` FROM ...