Solved

Coldfusion Mysql get data from two tables

Posted on 2016-08-18
3
87 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
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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