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`.
17 questions
1
vote
1
answer
174
views
How do I insert multiple records?
First, I took user_id, name, and email for the #__users table and joined with custom #__field_values lot, address, and phone.
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->...
- 13
1
vote
1
answer
85
views
Joomla mysql query - group_contact
I have mysql query:
// Get a db connection.
$db = JFactory::getDbo();
// Create a new query object.
$query = $db->getQuery(true);
$formId = 21;
$query = "select * from #...
- 83
2
votes
2
answers
1k
views
How to join custom fields data to users table without extra rows in result set
I have rudimentary knowledge of SQL (when it comes to anything but single tables) and am trying to come up with a query that will give me all the custom fields for my users.
This query will give me ...
- 21
1
vote
1
answer
70
views
PieChart For RSForm for PlotaLot based on a dropdown field
So I got this Pie Chart to display the Count of Voters vs Position and it's working fine.
SELECT Position,
COUNT(1) AS count
FROM (
SELECT
MAX(IF(FieldName = 'Position', FieldValue, NULL)) ...
- 113
1
vote
1
answer
353
views
Show article fields in module
I have two custom fields in my article for longitude and latitude. I'm then trying to build a module to get these two fields from the current article displayed and show in the module.
I have the ...
- 2,078
1
vote
1
answer
285
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 ...
- 13
1
vote
3
answers
380
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
2
answers
222
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
...
- 43
1
vote
1
answer
172
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 ...
- 47
2
votes
1
answer
133
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 ...
- 47
1
vote
1
answer
271
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 ...
- 47
1
vote
1
answer
99
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(...
- 13
3
votes
2
answers
922
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.
- 167
2
votes
2
answers
310
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 ...
- 265
1
vote
2
answers
128
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(...
- 11
2
votes
2
answers
686
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.
...
- 217
2
votes
3
answers
298
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
...
- 277