Solved

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

Posted on 2011-02-21
11
503 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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