Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-21
11
Medium Priority
?
513 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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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