Solved

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

Posted on 2011-02-21
11
508 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 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