CODEWITHSUNDEEP
coldfusion
Yannick
Yannick

Reputation: 235

MySQL if no result

I'm trying to create a script in ColdFusion that detects if a query exist for the specified user by using these queries:

            // User one is defined by the current session
            <cfquery name = "Friendship" datasource = "#DSN#">
            SELECT * 
            FROM  `conversations` 
            WHERE  `user_one` LIKE  '#user.id#'
            ORDER by id ASC
            </cfquery>

          <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
            SELECT *
            FROM users
            WHERE id = '#user_two#'
            ORDER BY id DESC
          </cfquery>

But, when this query doesn't have a result i want to use the following queries:

            <cfquery name = "Friendship" datasource = "#DSN#">
            SELECT * 
            FROM  `conversations` 
            WHERE  `user_two` LIKE  '#user.id#'
            ORDER by id ASC
            </cfquery>

          <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
            SELECT *
            FROM users
            WHERE id = '#user_one#'
            ORDER BY id DESC
          </cfquery>

Is this possible to do in ColdFusion? I want to keep the names of the query this way, otherwise i will need to change my whole site. This is the code of my page, if you need it:

  <cfif IsDefined("FriendshipToUsername.username")>

            <div class="person">
                <div class="avatar">
                    <div style="z-index:1;width: 64px; height: 73px; margin-bottom:-20px; margin-top:-30px; float: right; background: url(http://habbo.it/habbo-imaging/avatarimage?figure=#FriendshipToUsername.look#&amp;action=wav&amp;direction=3&amp;head_direction=3&amp;gesture=srp&amp;size=b&amp;img_format=gif);"></div>

                </div>
                <div class="info">
                  <a href="chat.cfm?chat=#Friendship.id#" style="text-decoration: none; color: ##4b4b4b;">  <h3>#FriendshipToUsername.username#</h3></a>
                    <a href="chat.cfm?chat=#Friendship.id#" style="text-decoration: none; color: ##4b4b4b;"><p>#FriendshipToUsername.motto#</p></a>
                    <p>Laatst gezien: gisteren</p></a>

                </div>
            </div>
            </cfif>

Thanks in advance!

Upvotes: 0

Views: 104

Answers (3)

Joe C
Joe C

Reputation: 3546

Check the recordcount property of the friendship and friendshipToUsername queries. You can simply re-query the database reusing the same query names as before.

You should also, of course, be using cfqueryparam to protect your database from sql injection attempts.

<!---(original friendship and friendshipToUsername queries)--->
<cfif NOT friendship.recordcount>   <!--- or "friendship.recordcount EQ 0" if you prefer --->
  <cfquery name = "Friendship" datasource = "#DSN#">
    SELECT * 
    FROM  `conversations` 
    WHERE  `user_two` LIKE  <cfqueryparam value="#user.id#" cfsqltype="cf_sql_integer">
    ORDER by id ASC
  </cfquery>

  <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
    SELECT *
    FROM users
    WHERE id = <cfqueryparam value="#user_one#" cfsqltype="cf_sql_integer">
    ORDER BY id DESC
  </cfquery
</cfif>

Upvotes: 1

steve
steve

Reputation: 1490

You can test the .recordcount attribute as shown below.

<cfif friendship.recordcount eq 0>

     <cfquery name = "Friendship" datasource = "#DSN#">
      SELECT * 
      FROM  `conversations` 
      WHERE  `user_two` LIKE  <cfqueryparam cfsqltype="cf_sql_varchar" value="#user.id#">
      ORDER by id ASC
     </cfquery>

     <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
     SELECT *
     FROM users
     WHERE id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#user_one#">
     ORDER BY id DESC
     </cfquery>

</cfif>

I would also suggest looking up and using the cfqueryparam tag to prevent your code from being affected by sql injections.

Upvotes: 4

James Hill
James Hill

Reputation: 61793

I suggest modifying your original query a bit so that it tries to retrieve both records at the same time. This way, you won't need to make two trips to the server if the first query returns zero rows:

<cfquery name = "Friendship" datasource = "#DSN#">
    SELECT * 
    FROM  `conversations` 
    WHERE  `user_one` LIKE  '#user.id#'
    OR [get other user here]
    ORDER by id ASC
</cfquery>

Once your query has executed, simply pull out the appropriate user.

Upvotes: 1

Related Questions