cfquery output - order by starting with record eq to url variable

From my first page, I'm clicking on a link that passes 2 url variables:

AutoArtID=966&ArtistID=4

AutoArtID=966 refers to a specific record.  There are multiple ArtistID=4 in my database table.  I'm using ArtistID=4 to create a list of records who's ArtistID=4 on the page I'm linking to AND I'm paging through the records one at a time. I need the list of records to start with AutoArtID=966 (more specifically - AutoArtID=#url.AutoArtID#).

The code below is the page with the pagination that needs to start with AutoArtID=#url.AutoArtID#
<cfparam name="URL.AutoArtID" default="1">
<cfparam name="URL.ArtistID" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_newaqimages" default="1">
<cfquery name="newaqimages" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1, 
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5, 
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName FROM Artwork, Artists WHERE Artwork.ArtistID=#URL.ArtistID# and Artwork.ArtistID=Artists.ArtistID and ArtistPage="1" ORDER BY AutoArtID desc</cfquery>
<cfquery name="titledates" datasource="Ikonltd">
SELECT  ExTitle, BeginDate, EndDate FROM CurrExExhibition
</cfquery>
<cfset MaxRows_newaqimages=1>
<cfset StartRow_newaqimages=Min((PageNum_newaqimages-1)*MaxRows_newaqimages+1,Max(newaqimages.RecordCount,1))>
<cfset EndRow_newaqimages=Min(StartRow_newaqimages+MaxRows_newaqimages-1,newaqimages.RecordCount)>
<cfset TotalPages_newaqimages=Ceiling(newaqimages.RecordCount/MaxRows_newaqimages)>
<cfset QueryString_newaqimages=Iif(CGI.QUERY_STRING NEQ "",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_newaqimages,"PageNum_newaqimages=","&")>
<cfif tempPos NEQ 0>
  <cfset QueryString_newaqimages=ListDeleteAt(QueryString_newaqimages,tempPos,"&")>
</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title><cfoutput query="newaqimages" startrow="#StartRow_newaqimages#" maxrows="#MaxRows_newaqimages#">#newaqimages.FirstName# #newaqimages.LastName#: #newaqimages.Title#</cfoutput></title>

<link href="../css/main.css" rel="stylesheet" type="text/css" />

<!--background from nahem site-->
<script type="text/javascript" src="http://a1.exhibit-e.com/js_libs/mootools/1.2/core.js"></script>
<script type="text/javascript" src="http://localhost:8500/ikonltd.com/js/fullscreen_bg/moo_12.js"></script>



</head>

<body>
<div id="wrap">
<div id="contentWrap">

<div id="masthead"><img src="../images/2010/temporary_for_development/ikon_banner1_smaller_logo.gif" /></div>

<ul class="topNav">
<li><a href="#">Current Exhibition</a></li>
<li><a href="#">Past Exhibitions</a></li>
<li><a href="#">Artists</a></li>
<li><a href="#">New Acquisitions</a></li>
<li><a href="#">Private Room</a></li>
<li><a href="#">About</a></li>
<li><a href="#">Contact</a></li>
<li><a href="#">Home</a></li>
</ul>
<cfoutput query="newaqimages" startrow="#StartRow_newaqimages#" maxrows="#MaxRows_newaqimages#">
<div id="artistPageContentWrap">

<div id="artistInfoNav"><!--left column-->

<h1>#newaqimages.FirstName# #newaqimages.LastName#</h1>
<div id="coolmenu">
<div id="tabledescription">
        <p><b>#newaqimages.Title#</b><br>
          #newaqimages.Date#
          <cfif "#newaqimages.DescriptionWhole#" is "">
            <cfelse>
            #newaqimages.DescriptionWhole# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine1#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine1# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine2#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine2# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine3#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine3# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine4#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine4# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine5#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine5# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine6#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine6# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine7#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine7# 
          </cfif>
        </p>
    	  
       <a href="mailto:ikonltd@earthlink.net?Subject=Re: #newaqimages.FirstName#&nbsp;#newaqimages.LastName# - #newaqimages.Title#">Contact 
          Gallery for Further Information</a></p>
    
	  </div>
	  </div>
	  <div>
<div class="paginationNav">
<a href="#CurrentPage#?PageNum_newaqimages=#Max(DecrementValue(PageNum_newaqimages),1)##QueryString_newaqimages#">Prev</a>&nbsp;&nbsp;<a href="#CurrentPage#?PageNum_newaqimages=#Min(IncrementValue(PageNum_newaqimages),TotalPages_newaqimages)##QueryString_newaqimages#">Next</a></div>
	  </div>
	 
	  <p><a href="artist.cfm?ArtistID=#URL.ArtistID#">Back to Artist Page</a>
<br /><br /><a href="index.cfm">Back to Artist List</a>
<br />
<br />

<a href="">Artist Exhibitions</a>
</div>

<div id="artistWorks"><!-- right column-->

  <img src="../images/lg/#newaqimages.lg#" border="0"></div>
<!--eof artistWorks-->

</div></cfoutput><!--eof artistPageContentWrap-->



</div><!--eof contentWrap-->
</div><!--eof wrap-->


</div>

</div>


<script type="text/javascript">
//<![CDATA[

FullScreenBG.init('http://localhost:8500/ikonltd.com/images/2010/ikon-blank-wall.jpg');

//]]>
</script>
<!--eof show thumb on rollover for artist landing page-->

</body>
</html>

Open in new window

phillystyle123Asked:
Who is Participating?
 
azadisaryevCommented:
you can do this using UNION query or using CASE statement.
something like this:

1) using CASE statement - make sure your db supports it (i.e. MS Access does not). also check correct syntax to use for your db:

<cfquery name="newaqimages" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1,
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5,
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName, (CASE WHEN AutoArtID = #URL.AutoArtID# THEN 0 ELSE 1 END) AS sortcol
FROM Artwork INNER JOIN Artists ON Artwork.ArtistID=Artists.ArtistID
WHERE
    Artwork.ArtistID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ArtistID#">
    AND ArtistPage = "1"
ORDER BY sortcol ASC, AutoArtID DESC
</cfquery>

the CASE clause above creates a 'sortcol' column with value 0 if AutoArtID column value equals passed url value or 1 otherwise. the ORDER BY clause then sorts the recordset by sortcol column first, then AutoArtID column, making sure the record matching passed url value is the first record.

2) using UNION query - please check the syntax for your db as well [some require each SELECT statement to be in (), some do not]:

<cfquery name="newaqimages" datasource="Ikonltd">
(SELECT ..., 0 AS sortcol
FROM Artwork INNER JOIN Artists ON Artwork.ArtistID=Artists.ArtistID
WHERE
    Artwork.ArtistID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ArtistID#">
    AND AutoArtID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.AutoArtID#">
    AND ArtistPage = "1")
UNION
(SELECT ..., 1 AS sortcol
FROM Artwork INNER JOIN Artists ON Artwork.ArtistID=Artists.ArtistID
WHERE
    Artwork.ArtistID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ArtistID#">
    AND AutoArtID <> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.AutoArtID#">
    AND ArtistPage = "1")
ORDER BY sortcol ASC, AutoArtID DESC
</cfquery>

in the above, the first SELECT statement selects the record that matches passed url value, and adds 'sortcol' column with value 0. the second SELECT statement selects all other records and adds 'sortcol' column with value 1. the ORDER BY clause sorts combined recordset by sortcol, then by AutoArtID, making sure the record matching passed url value is the first record

Azadi
0
 
phillystyle123Author Commented:
EXACTLY what i needed!!!! Thanks so much:


SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1,
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5,
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName, (CASE WHEN AutoArtID = #URL.AutoArtID# THEN 0 ELSE 1 END) AS sortcol
FROM Artwork INNER JOIN Artists ON Artwork.ArtistID=Artists.ArtistID
WHERE
    Artwork.ArtistID =
    AND ArtistPage = "1"
ORDER BY sortcol ASC, AutoArtID DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.