Solved

Coldfusion Mysql get data from two tables

Posted on 2016-08-18
3
126 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 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

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
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
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

624 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