Panos
asked on
Coldfusion Mysql get data from two tables
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:
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
attributes_categories_text
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, that one gets me sometimes too. Glad I could help!
ASKER
The code did the job. The mistake was i didn t use the right cfoutput group and order by.
thank you very much