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

Question

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]

Post your query. Join your two tables on the USER_ID and then loop to create the array. — Cory Fail
– Cory Fail, Commented Oct 14, 2019 at 15:06

Dan Roberts · Accepted Answer · 2019-10-14 15:10:47Z

2

It sounds like what you are doing is writing an ajax request and query per table. More likely what you want to do is create join query and one ajax request.

<cffunction name="getUserPosts">
    <cfargument name="userID" required="true" />
    <cfquery name="local.result" datasource="#dsn#">
        SELECT u.User_ID, u.FullName, p.Post_ID, p.Text
        FROM user_info_tb u
            INNER JOIN post_tb p ON u.UserID = p.Posted_By
        WHERE u.User_ID = <cfqueryparam value="#arguments.userID#" />
    </cfquery>

    <cfreturn result />
</cffunction>

Depending on your needs and CF version you could just serialize

<cfoutput>#serializeJson(getUserPosts(form.userID))#</cfoutput>

or to array as you say in question...

<cfset result = getUserPosts(form.userID) />
<cfset resultArray = [] />
<cfloop query="#result#">
    <cfset arrayAppend(resultArray, [result.FullName, result.Text]) />
</cfloop>
<cfoutput>#serializeJson(resultArray)#</cfoutput>

answered Oct 14, 2019 at 15:10

Dan Roberts

4,6943 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shawn Over a year ago

I'd probably do a LEFT OUTER JOIN instead of an INNER JOIN to pick up any users who didn't have any posts. Unless the OP needs to explicitly exclude any user without a post. But I completely agree. This is a problem that should be solved in the query instead of CF.

SOS Over a year ago

Yep, a join is what's needed. Though I'd use the newer options for serializing queries into an array of structures. Much simpler than the manual approach required in older versions like CF11. helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/…

vampyr Over a year ago

thanks im going to try the array method ! thanks for replying

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related