Solved

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

Posted on 2011-02-21
11
504 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
What You Need to Know when Searching for a Webhost Provider
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 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