CODEWITHSUNDEEP
mysqlcoldfusion
Yannick
Yannick

Reputation: 235

Mix queries together

I've made a chat script in ColdFusion that uses different MySQL tables. One is where the conversation is stored, and the other one is the one that defines who started the conversation and who received it.

Now, I've made a script that allows you to chat together with someone. When sending a message, it adds a row into the MySQL table with a timestamp, the message id, the user id that sended the message and the message itself. The whole conversation is displayed with this code:

// Get all conversations started by the user that is signed in 
<cfquery name = "Friendship" datasource = "#DSN#">
    SELECT *
    FROM  `conversations`
    WHERE  `user_one` LIKE  '#user.id#'
    ORDER BY  `conversations`.`lastmessage` DESC
</cfquery>

// Get all conversations started by others but receiver is the user that is signed  in                                                                                  
<cfquery name = "FriendshipBack" datasource = "#DSN#">
    SELECT *
    FROM  `conversations`
    WHERE  `user_two` LIKE  <cfqueryparam value="#user.id#" cfsqltype="cf_sql_integer">
    ORDER BY  `conversations`.`lastmessage` DESC
</cfquery>


// OUTPUT - conversations that I began
<cfoutput query="Friendship">
    // This code translates a user id into a name
    <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
        SELECT *
        FROM users
        WHERE id = '#user_two#'
    </cfquery>

    <cfif IsDefined("FriendshipToUsername.username")>
        <cfif IsDefined('URL.chat') and URL.chat neq "">
        </cfif>                

       // Display username 
      <h3>#FriendshipToUsername.username#</h3>
    </cfif>

</cfoutput>

/// OUTPUT - conversations that I received
<cfoutput query="FriendshipBack">
    // This query translates a user id into a name
    <cfquery name = "FriendshipToUsernameBack" datasource = "#DSN#">
        SELECT *
        FROM users
        WHERE id = <cfqueryparam value="#user_one#" cfsqltype="cf_sql_integer">
    </cfquery>

    <cfif IsDefined('URL.chat') and URL.chat neq "">
    </cfif>                
    // Display username 

    <h3>#FriendshipToUsernameBack.username</h3>
</cfoutput>

At this moment the chat list is seperated into two categories: one is a list where the signed in user started the conversation, the other one is where the signed in user received a conversation from someone else. The problem is, that I don't want it to display it that way, so I want to mix both lists together and list them all based on the timestamp (from newest to oldest)

At this moment the chat list is seperated into two categories: one is a list where the signed in user started the conversation, the other one is where the signed in user received a conversation from someone else. The problem is, that I don't want it to display it that way, so I want to mix both lists together and list them all based on the timestamp (from newest to oldest)

Is there any way to achieve this?

I know that my code is exploitable, but this is still WIP

Upvotes: 0

Views: 83

Answers (2)

Leigh
Leigh

Reputation: 28873

At this moment the chat list is seperated into two categories: one is a list where the signed in user started the conversation, the other one is where the signed in user received a conversation from someone else.

Then simply query the table where either of those conditions are true, and sort the records by timestamp. (Also, as mentioned on another thread, when searching for an exact match the correct operator is equals ie =, not LIKE).

  ...
  WHERE  conversations.user_one = <cfqueryparam value="#user.id#" 
                                           cfsqltype="cf_sql_integer">
  OR     conversations.user_two = <cfqueryparam value="#user.id#" 
                                           cfsqltype="cf_sql_integer">
  ORDER BY  conversations.lastmessage DESC

This code translates a user id into a name

Do not query within a loop, if you can avoid it. It adds a lot of unnecessary overhead. The proper way to retrieve the related columns is adding a JOIN to your query. Since you must join to the user table multiple times (to get both names) use an alias to differentiate:

  SELECT conversations.user_one
         , conversations.user_two
         , u1.username AS userNameOne
         , u2.username AS userNameTwo
         , ... 
  FROM   conversations 
            JOIN users u1 INNER JOIN u1.id = conversations.user_one
            JOIN users u2 INNER JOIN u2.id = conversations.user_two
  WHERE  ...

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

If you run a single union all query instead of two individual queries, you will be able to do so.

Upvotes: 1

Related Questions