[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Coldfusion Mysql get data from two tables

Posted on 2016-08-18
3
Medium Priority
?
166 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 53

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 53

Expert Comment

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

591 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