Solved

Coldfusion Mysql get data from two tables

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CFFILE upload help 98 113
duplicate data not insert in mysql 46 46
php mysql if then statement syntax 4 27
Need help with a query 6 55
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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now