Skip to main content
V
v7mp
Participant
October 13, 2019
Question

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

  • October 13, 2019
  • 2 replies
  • 309 views

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

This topic has been closed for replies.

2 replies

J
John123
Participating Frequently
October 16, 2019

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>

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
October 14, 2019

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>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices