Solved

Need cfquery output results grouped alphabetically and sorted by sponsored links at top

Posted on 2011-02-21
11
499 Views
Last Modified: 2013-12-24
Hello Experts,

I am trying to produce what I think should be a fairly straightforward cfquery and cfoutput of results, but just cannot seem to come up with the correct syntax. What I have is a database of records where I need to pull a list of restaurants, sorted alphabetically by business name, but with our sponsored links first (represented by a nonempty "Webaddress" field) in the list of search results. It needs to be a single query, I believe, as there are "n" records allowed per page, so I am utilizing RecordCount, MaxRows, and Previous/Next buttons. I believe that I need to use Group By in my query and/or Group in my cfoutput, or some combination thereof, but everything I try gives incorrect results, as well as issues with the Next and Previous records. So, here is an example of what I need:

Page 1:

A Restaurant (linked)
B Restaurant (linked)
C Restaurant (linked)

Next 3 Records >

Page 2:

D Restaurant (linked)
A Restaurant (no link)
B Restaurant (no link)

...etc.

I hope the above makes sense.

Currently my query looks like this:

<CFQUERY DATASOURCE="mwinfo"
      NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
      FROM dining
      ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC
</CFQUERY>

My cfoutput looks like this:

<CFOUTPUT QUERY="results" StartRow="#StartRow#" maxRows="#MaxRows#">

My results are basically the opposite of what I need; i.e. unlinked results appear first, in alphabetical order, then the linked (sponsored) results appear second -- though these sponsored links are not in alphabetical order by the busname field, but rather the Webaddress field.

Any assistance someone could provide, to get these results the way I need them, would be SO appreciated. Thank you in advance!
0
Comment
Question by:tam423
  • 5
  • 4
  • 2
11 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 34946326
This will place the empty webaddress 2nd and the populated ones 1st.   Then order by business name

<CFQUERY DATASOURCE="mwinfo"  NAME="results">
  SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
   FROM dining
   ORDER BY case when Webaddress is null then 2 else 1 end, busname
</CFQUERY>
0
 

Author Comment

by:tam423
ID: 34946425
qdemaria,

Thanks so much for the quick response. Can you clarify what the actual ORDER BY clause would be, to account for the NULL Webaddres, etc.?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34946432
You didn't mention, but it looks like you are using MySQL, so the syntax would be a bit different..


<CFQUERY DATASOURCE="mwinfo"  NAME="results">
  SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
   FROM dining
   ORDER BY  iif(Webaddress is null, 2, 1),  Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC
</CFQUERY>



 
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34946454
>  Can you clarify what the actual ORDER BY clause would be, to account for the NULL Webaddres

if the webaddress is null, then assign a "2" to that record,  if it has a value, then assign a 1 to that record
Sorting by this, will place all webaddress records first, then all non-webaddress records

Then sort by business name

1  AAAA  
1  CCC
1  DDD
2  BBBB
2  EEEE
2  FFFF

0
 

Author Comment

by:tam423
ID: 34946735
qdemaria,

I am actually using MS Access. I tried your syntax above and the results are better, but still not correct. What I'm getting is something like this:

Page 1:

A Restaurant (linked)
B Restaurant (linked)
C Restaurant (no link)
D Restaurant (linked)
E Restaurant (linked)
F Restaurant (no link)
G Restaurant (no link)
H Restaurant (linked)

Page 2:

R Restaurant (no link)
S Restaurant (linked)
...
Y Restaurant (linked)
Z Restaurant (no link)

Page 3:

A thru Z Restaurants (no link)

So, while most of the sponsored links are now sorted first, there are a few non-sponsored listings being interspersed among them, for some reason (and those are not repeated in the A thru Z non-sponsored listings coming at the end). I verified in the database that there is, indeed, nothing in the Webaddress field for those interspersed ones, as well.  I don't know why this is happening -- could it be because the synatax should be something else, for MS Access vs. MySQL? Thank you again...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:gdemaria
ID: 34946813

Let's try a bit different, perhaps the field cotains an empty string rather than NULL

ORDER BY  iif(isNull(Webaddress,'') = '', 2, 1),  Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC


also try this to see what the field holds...

 SELECT if(webaddress is null,'Null', if (webaddress = '','Empty', 'Populated'))
 from table...


0
 

Author Comment

by:tam423
ID: 34946918
gdemaria,

I tried your new ORDER BY syntax, but got the error,

"Wrong number of arguments used with function in query expression 'iif(isNull(Webaddress,'') = '', 2, 1)'. "

I also tried your SELECT statement in a test query to see what the field holds:

<CFQUERY DATASOURCE="mwinfo"
      NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, if(webaddress is null,'Null', if (webaddress = '','Empty', 'Populated')), pricerange, cuisine1, cuisine2, cuisine3, blurb
      FROM dining
      ORDER BY  Webaddress,  Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC
</CFQUERY>

...and received the error,

"Undefined function 'if' in expression. "

So, I probably don't have the SELECT query correct, in some way. I'm sorry -- a little out of my element here...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34947089
no points ...

Unfortunately MS Access does many things differently than in most db's :)  So things that work one way in MS SQL have to be tweaked for MS Access.  I'm pretty sure ISNULL is one of those functions.  

isNull(Webaddress,'')

In MS SQL that says "Webaddress" is null, return an empty string instead.  I don't know the equivalent function in Access. So I'd fall back to using IIF
       
        IIF( Webaddress IS NULL, '', Webaddress)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34947093
Typo correction:   In MS SQL that says IF "Webaddress" is null, THEN return an empty string instead.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 34947441
agx is right, thanks.  I looked up to see that isNull exists in MS Access, it does, but turns out it is different than other languages.

So, let's try this....

   FROM dining
   ORDER BY  iif(Webaddress is null, 2, if(webaddress = '',2,1))
                 ,  Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC



>  Undefined function 'if' in expression

For the error in the SELECT, the  if  should be spelled with two  "i" s     iif()   not   if()
0
 

Author Comment

by:tam423
ID: 34947502
gdemaria,

That was it! Thanks so much! I just had to change your "if" to "iif" in the second part of your ORDER BY clause -- (iif(webaddress='',2,1) -- and your latest solution worked like a charm.

Thank you again!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
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 shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

12 Experts available now in Live!

Get 1:1 Help Now