Hi there.

I've been searching for quite some time, but have yet to find a solution to my problem.

I need to find a way to display all the records in a query, but begin the list with a record that is "out of order"

For example, if my query returns 6 rows, I'd like to present those rows as:

Row 4
Row 5
Row 6
Row 1
Row 2
Row 3

I know the first answer that will jump to mind is "use the Order By clause!" I thought of this too. Unfortunately, the other fields I would order by are not unique, so I can't really use an alphabetical or numerical ordering.

I also tried using but that returns only row 4 through the end (I don't get rows 1-3 outputted)

If anyone knows of a way to output ALL rows from a query, but begin with row X, please let me know.

Thanks very much.

What is the logic determining the output order? For example, do you divide the number of rows / 2 and then output the second half

row 4
row 5
row 6

followed by the first half

row 1
row 2
row 3

?

Without knowing the logic, the first thing that comes to mind is to use (2) cfoutput's

<cfoutput startrow="4" endrow="6">....</cfoutput>
<cfoutput startrow="1" endrow="3">....</cfoutput>

Though there may be a more elegant method, depending on your logic.

Also, some databases provide a row number that can be used for ordering. So an order by clause might still be an option. Which one are you using?

I'm using a mySQL db, and honestly, I don't really care about the output order at this point... It could be 4,1,2,3,5,6 - as long as the specified record (in this case, record #4) is outputted first.

Maybe a little background would help: (I'm about 5 beers in though, so please forgive me if I'm not crystal clear )

I'm trying to build a management application for a flash-based slideshow gallery that is powered by an XML file. The XML file is being dynamically generated from a CF page that pulls database content. In my database, I have one table that lists all my albums, and another that lists all the images (images are tied to the albums by an ID#).

Right now, I'm focusing on just the albums. I want to be able to send a variable to the XML-generating CF page that tells it which album should be loaded first. The idea is that the user clicks on a link to open the slideshow to album #4 (therefore, album #4 must be first in the XML file).

When the user is viewing the slideshow, they can click on the "Gallery" button to view all available albums within the gallery. The order of the other albums in the gallery is unimportant to me, as long as I can give them the requested album (#4) first, so that they start off viewing the album they wanted to see.


Here's the CF/XML file as I have it right now:


Any thoughts?

In that case you can use an order by. Just add a CASE statement to sort the selected album number first. Then order by whatever other columns you want and output the query as usual.





Wow. You are more crystal clear than I am with 0 beers



Also, you could probably eliminate the inner query and replace it with a JOIN instead.

GENIUS!!!

I've never knew you could use a CASE statement in SQL, but it's working like a charm, so I'm reading up on it now.

Thank you so much - I was driving myself nuts all night looking for a solution for this. I owe you my sanity.


Next step is to figure out how to define the order of the images in each album - but that should be a little more permanent, so I think I'm going to create a "position" field in the images table. I'm pretty sure I got that one on lockdown, but if I botch it, I'll be back here, crying for help

You're welcome.

Yes for a more permanent ordering, a "position" column sounds like a good approach.

I've been kicking this around the last 2 days, and I'm having a bit of trouble with the logic I'm using to re-arrange these records. I feel like I'm so close, yet so far...

I've set up a page that displays all the current records - each record has a hidden form input with the current/old value, and a text input where the user can enter the new position. (Kinda like the position function in the "My Queue" feature on the Netflix website)

My form is submitted via an onChange call in each text input, so anytime the user changes any value, two lists are returned: one list of old positions, and one list of new positions. I'm trying to compare the two lists, find the difference between the two, and use that info to make the swap...

Here's what I have now:

(To save space here, I'm setting the lists manually instead of pasting the code for the whole page, form and all)



Any suggestions?

This post has been edited by jorvis: 6 Apr, 2008 - 07:58 PM

There are at least two ways you could do it. You could update all of the records whenever anything changes - OR - do what you are attempting now, which is updated only the changed items.

The first method is simpler. But a disadvantage is it can potentially update more records.

The second method usually impacts less records. But, the logic is slightly more difficult. Also, you have to consider "state". ie The position order may have changed since you first displayed the form. Though that issue mainly applies more to multiple users updating the same information at one time.

In cases like this I typically use the first method. I find it simpler and less error prone.

In either case, I need to figure out how to swap the value that has been changed with the value it is being turned into.

That's where I'm running into trouble. I've been poring over the CF reference on adobe.com, and I just found the ArraySwap function a few minutes ago, but I'm a little unclear on how to identify which positions in the array should be swapped so that I can pass those positions to the function.

Nix that - I think I got it...



*big effin' SIGH*

Now it seems so simple... Sometimes I love being self-taught, and sometimes I just want it to friggin work, y'know?

I totally hear you. Sorry I didn't provide an example. My brain is fried and I can barely form a coherent thought right now, let alone working code ;-)

What I was trying to get at, in my round-about way, was if you just accept a list of the new positions, you don't need to figure out what is changed. Just loop through and update the records. That is generally the simplest, though not always most efficient approach. Personally I find it less problematic all around. Especially with multi-threading. Though I have also used a stored procedure approach as well.

Enough rambling. I am glad you worked it out

No worries - you've already been more help than I ever expected to get from a forum

Took me a bit to get the update queries working properly... records were getting updated in the most random of orders, and I couldn't figure out what the hell was going on... then I realized that I was using the albumID and image position in my WHERE clause and I needed to use the imageID instead...

Here's what the final result came to be:

Form inputs:


CF processing & DB update


If you have any suggestions that would optimize the code, I'd love to hear 'em.

Right now, I'm working on replacing the text inputs with dropdown menus. Once I got to use this, I realized that the text inputs were lame. You have to alter the value, then click out of the input box before the update happens. I think this would be a lot more user-friendly with a dropdown that submits onChange instead.

This post has been edited by jorvis: 8 Apr, 2008 - 05:24 PM