Highlighted

Trying to create an array mixed with data from different mysql tables

New Here ,
Oct 13, 2019

Copy link to clipboard

Copied

Basically i have few tables in MySql and im trying to create an array mixed with data from diferrent tables. I'm using AJAX to get the data from CFC. I've tried using different ajax requests to get the data from different tables but thats making my code very long.

 

First example table - user_info_tb

--------------------------------------

USER_ID | FULL_NAME

----------------------------------------

      2              Jack

      3             John

      4             Jamie

 

Second example table - post_tb

 

--------------------------------------

POST_ID | POSTED BY (USER_ID FROM THE FIRST TABLE)| TEXT |

--------------------------------------------------------------------------------------------------

      1              2                                                                                        Hi

      2              3                                                                                        Hello

      3              4                                                                                        Good Morning

 

 

how can i create an two dimensional array to to get something like this

 

[0]

     [1]-[Jack]

     [2]-[Hi]

[1]

     [1]-[John]

     [2]-[Hello]

[2]

     [1]-[Jamie]

     [2]-[Good Morning]

 

Thanks in advance ! If anybody can help please reply

TOPICS
Advanced techniques, Database access

Views

203

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Trying to create an array mixed with data from different mysql tables

New Here ,
Oct 13, 2019

Copy link to clipboard

Copied

Basically i have few tables in MySql and im trying to create an array mixed with data from diferrent tables. I'm using AJAX to get the data from CFC. I've tried using different ajax requests to get the data from different tables but thats making my code very long.

 

First example table - user_info_tb

--------------------------------------

USER_ID | FULL_NAME

----------------------------------------

      2              Jack

      3             John

      4             Jamie

 

Second example table - post_tb

 

--------------------------------------

POST_ID | POSTED BY (USER_ID FROM THE FIRST TABLE)| TEXT |

--------------------------------------------------------------------------------------------------

      1              2                                                                                        Hi

      2              3                                                                                        Hello

      3              4                                                                                        Good Morning

 

 

how can i create an two dimensional array to to get something like this

 

[0]

     [1]-[Jack]

     [2]-[Hi]

[1]

     [1]-[John]

     [2]-[Hello]

[2]

     [1]-[Jamie]

     [2]-[Good Morning]

 

Thanks in advance ! If anybody can help please reply

TOPICS
Advanced techniques, Database access

Views

204

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 13, 2019 0
Adobe Community Professional ,
Oct 14, 2019

Copy link to clipboard

Copied

I shall call the queries, respectively Q1 and Q2. 

Then you could do something like

<!--- Remember that in ColdFusion array indices generally start from 1 --->
<cfset myArray=arrayNew(2)>
<cfloop from="1" to="3" index="i"> 
<cfset myArray[i][1]=Q1.fullname[i]>
<cfset myArray[i][2]=Q2.text[i]> 
</cfloop>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 14, 2019 0
John123 LATEST
Participant ,
Oct 16, 2019

Copy link to clipboard

Copied

Simplyfy the data extract by using a join to get one row per user then loop and add to the array

 

<cfquery name="q" datasource="#application.setting.DSN.Source#">
select u.full_name, p.text
   from user_info_tb u
        join post_tb p on p.posted_by = u.user_id
</cfquery>

<cfscript>
myArray=arrayNew(2);
for (row in q) {
    myArray.append([row.full_name, row.text]);
}

writeDump(myArray);
</cfscript>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 16, 2019 0