Solved

Combining API results with Local Database Query?

Posted on 2010-08-26
7
259 Views
Last Modified: 2012-05-10
Hi Folks,

I'm using Ray Camdens CFYahoo package (http://cfyahoo.riaforge.org/) to utilize the Yahoo Search API.  As part of my flow, I'd like to reference a local database during screen output.  For example, my local DB will include comments about sites (one of the fields in there could be the web url, for example Apple.com, which we could use to map against the API results).

I can successfully query and return results from the API, however I'm struggling with matching it up against the database.  Whilst I can easily do a cfquery whilst looping through the results, with 10 results per page, this means incurring 10 cfqueries one after another - which to me seems incrediby inefficient.

To keep the output clean, I only want to show the actual comment count against each result, the user then has the option to view the comments if they wish.  Of course, there will be an awful lot of results with no comments at all.

So, my question is - is there a way I can match these up with a single query before I output the page?
0
Comment
Question by:RedBullLee
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 33537097
Not simply... no.  But you could probably do it more efficiently than running 10 separate queries.  

I'm assuming the API already returns a query.  So you could run 1 db query, and merge the results back into the API query manually. Obviously not as pretty as a QoQ might be.  But again, they don't support outer joins. So the manual route is probably as good as it gets.

1. Add a "CommentCount" column to the API query using queryAddColumn().  
2. Run a single db query and pass in the list of 10 url's you wish to check:

       SELECT WebURL, COUNT(*)
       FROM    YourTable
       WHERE  WebURL IN
       (
                 <cfqueryparam value="#listOfURLs#" list="true" cfsqltype="cf_sql_varchar">
       )
      GROUP BY WebURL

3. Finally loop through the results of your db query, and populate the "CommentCount" column for each url.


0
 

Author Comment

by:RedBullLee
ID: 33540901
Hi _agx_,

Many thanks again for coming to my rescue!  

I've managed to put some test data in to test this out, and I can successfully match up the comment counts against the weburls returned from the API.

However, I'm struggling to figure out the output looping on the page itself.  I understand that a straightforward list of urls with comment counts could be done simply by outputting the query, however that won't return urls for which there are no comments, nor would it return any of the other data the API returns.

Assuming I simply output the API result as before, how do I get my 'new' query into the equation at output/loop level?
0
 

Author Comment

by:RedBullLee
ID: 33542124
After a bit of trial and error, I've now managed to put something together that works.

Here's what I've got;

// The Query \\
<cfquery name="sresult" datasource="#datasource#">
SELECT WebURL, COUNT(*) AS cmtcount
FROM tmp
WHERE WebURL IN (<cfqueryparam value="#result.url#" list="yes" cfsqltype="cf_sql_varchar">)
GROUP BY WebURL
</cfquery>

// The Output \\
<cfoutput query="result">
#result.title#
#result.abstract#
#result.url#
<cfloop query="sresult"><cfif WebURL EQ '#result.url#'>#sresult.cmtcount#</cfif></cfloop>
</cfoutput>

Am I on the right track with this?  As mentioned, it works - just want to be sure it is actually the best way of doing it?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 33543048
It's been hectic. Sorry for the delay.

>> how do I get my 'new' query into the equation at output/loop level?

Personally, I wouldn't do it that way.  My preference is to do any prep work first. In other words, merge all the data I need into 1 query (ie the API query). Then output it as usual.

I would probably add an empty "commentCount" column to the api query first:

     <cfset commentCount = listToArray(RepeatString("0,", 10))>
     <cfset queryAddColumn( yourAPIQueryName, "CommentCount", commentCount)>

Then use valueList() to generate the list of 10 url's to pass into the db query.  The result should be a list of url's in the same order as in the API query rows.  NOTE:: I'm deliberately ignoring possible delimiter issues for now ...

       <cfset listOfURLS = valueList(yourAPIQueryName.webURL)>

Then I'd pass the list into the db query:
       ....
       WHERE  WebURL IN   ( <cfqueryparam value="#listOfURLs#" list="true" ...> )

Finally, I'd transfer the db results _into_ the API query by looping through the db query. For each result, I'd find the matching row in the API query and update the "CommentCount" value.

<cfloop query="yourDBQuery">
        <!--- find the current URL in the API query --->
        <cfset row = listFindNoCase( listOfURLs, yourDBQuery.WebURL )>
        <cfif row>
              <!--- transfer the comment count value into the API query --->
              <cfset yourAPIQuery.webURL[row] = yourDBQuery.CommentCount>
        </cfif>
</cfloop>

Then I'd output the API query as normal.  As I mentioned before, I deliberately ignored delimiter issues. But if you're using url's (ie strings) that's definitely something to be careful with. Make sure you're using a delimiter that can't appear in the data, or obviously you'll get the wrong results.


0
 

Author Comment

by:RedBullLee
ID: 33543167
Thanks again _agx_,

I'll do a bit of experimenting with both methods, the delimiter issue is important and hadn't crossed my mind, so I'll work on that one.

Your help, as ever, is much appreciated :-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33544336
You're very welcome.  

Yeah, delimiters can be tricky.  I often end up picking some non-printable character. Usually a safe bet.  But when it's critical I check the input first, and cfabort if that character exists in the data ;-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33547908
... One final comment for the archives. If you've only got a max of 10 items in each query, it probably doesn't matter which method you use.  Looping up to 100 times (rows in outer output loop * rows in db query) versus looping only once in the other method.... well, it probably won't make a noticeable difference in performance.  It's when you start dealing with a larger number of records that it becomes a factor.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now