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, Oct 14 '19 at 15:06

score 2 · Answer 1 · answered Oct 14 '19 at 15:10

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>

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. — Shawn, Oct 14 '19 at 17:49
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. https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/serializejson.html — SOS, Oct 14 '19 at 20:23
thanks im going to try the array method ! thanks for replying — vampyr, Oct 21 '19 at 09:20

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

1 Answers1