Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Coldfusion Mysql get data from two tables

Posted on 2016-08-18
3
Medium Priority
?
140 Views
Last Modified: 2016-08-19
Hello experts.
I need help to create a query to retrieve data from two tables and then create an array to return data from a function.
I have one attributes_categories with more than an attribute_id that i have in the testcode i post and a attributes_categories_text table to get the name of the categories in several languages
attributes_categories_text table:
table_id(int) attr_id(int)  attr_text (varchar)  language(char)

I did try with left joins and group but i did nt get what i want.
I post a code that is working and giving what i need but i m sure that it could be done with a better way.
Any help?
The test code:
 <cfquery name = "qrycategories" datasource="#request.dsn#">
  SELECT ac.attr_cat_id
  FROM attributes_categories2 ac 
  ORDER BY ac.attr_cat_ordervalue ASC  
 </cfquery>
 <cfoutput>
 <cfset Records = []>
 <cfloop query="qrycategories">
  <cfquery name = "data" datasource="#request.dsn#">
   SELECT act.attr_cat_text, act.language
   FROM attributes_categories_text2 act 
   WHERE  act.attr_cat_id = #qrycategories.attr_cat_id#
  </cfquery>
  <cfset elem = {} />
  <cfset elem["attr_cat_id"] = attr_cat_id />
  <cfloop query="data">
   <cfset elem["attr_cat_text_#data.language#"] = '#data.attr_cat_text#' />
  </cfloop>
  <cfset arrayAppend(Records, elem) />
 </cfloop>
</cfoutput>

Open in new window

0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 41761657
EDIT - Fix code typo

A grouped output should have worked.  Maybe you didn't ORDER BY the same column used in the cfoutput group? Assuming "attr_cat_ordervalue" are unique values, what happens if you try something like this?


<cfquery name = "qryData" datasource="#request.dsn#">
  SELECT p.attr_cat_id
	       , c.attr_cat_text
	       , c.language
               , p.attr_cat_ordervalue
  FROM  attributes_categories2 p
                    LEFT JOIN attributes_categories_text2 c ON c.attr_cat_id = p.attr_cat_id
  ORDER BY p.attr_cat_ordervalue ASC, p.attr_cat_id
</cfquery>

  <cfset Records = []>

  <!--- MUST group by same column in ORDER BY --->
  <cfoutput query="qryData" group="attr_cat_ordervalue">

     <!--- Code here executes ONCE per grouped column --->
     <cfset elem = { "attr_cat_id" = attr_cat_id } >

     <!--- do nothing if there aren't any related records --->
     <cfif len(language)>
          <cfoutput>

             <!--- Code here will execute for EACH related record --->
             <cfset elem["attr_cat_text_#data.language#"] = data.attr_cat_text />

         </cfoutput>
     </cfif>

  </cfoutput>

Open in new window

0
 
LVL 2

Author Comment

by:Panos
ID: 41762402
Hi agx
The code did the job. The mistake was i didn t use the right cfoutput group and order by.
thank you very much
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41762454
Yes, that one gets me sometimes too.  Glad I could help!
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question