How to sort the list of subscriptions?

[BigSerge]

Hello,

I have several subscriptions, and each one can be paid monthly, quarterly, semi-annually or annually. In the members profile they have 12 links, but I don't know why the links are all mixed up, and it is very confusing for members who want to subscribe.

Right now the subscriptions are in this order, even if I added them in order. First the Gold 1 month, 3 months, 6 months and 1 year(these have existed for many years), now I have added the Silver and Platinum in this same order. So I don't understand why it is in this order.

Gold Membership - 1 Month
Platinum Membership - 1 Year    
Silver Membership - 1 Year    
Gold Membership - 1 Year
Platinum Membership - 1 Month   
Silver Membership - 1 Month    
Silver Membership - 3 Months
Platinum Membership - 3 Months   
Gold Membership - 3 Months    
Gold Membership - 6 Months    
Platinum Membership - 3 Months    
Silver Membership - 6 Months    

I wanted to order the platinum 1, 3, 6, and 12 months first, then the gold and then the silver, always in this order, so it wouldn't be so confusing.

How can I do this?

I have SMF 2.0.19.

Thanks

[Sir Osis of Liver]

Subscriptions appear to be sorted in the order they were added, not by id_subscribe but in the order of rows in smf_subscriptions.  Not sure how yours got scrambled. Can you post a screenshot of smf_subscriptions?

[Arantor]

No, they're sorted by whatever order they happen to be in the database which is a very very different thing, and in no way guaranteed to be order of creation.

Unfortunately there is no way to fix this for the desired order without a really really nasty set of code edits.

Would be better if someone wrote a mod to allow them to be reordered manually.

[BigSerge]

Subscriptions appear to be sorted in the order they were added, not by id_subscribe but in the order of rows in smf_subscriptions.  Not sure how yours got scrambled. Can you post a screenshot of smf_subscriptions?

No, it was not the order they were added. I added first 1 month, then 3 month, 6 month and 1 year, then next one for the same order.

Ok, I got that you were talking about the DB table, so I opened the DB table and I saw that the "Sort by key:" was set to None, so I switched to "PRIMARY ASC", and suddenly they were all perfectly sorted, in the order they were added. But I did not see how I could save this order.

Then I changed the id_subscribe numbers to the order I want, and now even when the Sort by key is set to None, the order is correct. However, when I go to the Admin panel and to the member Profile and click Paid Subscriptions, the order is still like before, all mixed.

I attached the DB table and the Admin panel screenshots. I changed the names of two subscriptions and the last one is the only one in the correct order, do not know why.

As a construtive critic, I have no idea how someone builds a so complicated software, add the Paid Subscriptions(that was a Mod before) to the main script, and does not think about something so basic like allowing to sort the subscriptions in the order the admin wants!

Thank you for your help. 

[BigSerge]

No, they're sorted by whatever order they happen to be in the database which is a very very different thing, and in no way guaranteed to be order of creation.

Unfortunately there is no way to fix this for the desired order without a really really nasty set of code edits.

Would be better if someone wrote a mod to allow them to be reordered manually.

Yes, like I posted above, they were sorted in the database without any logical order, and not in the order I created them. I have no idea why this happens.

I hope there is a way to resolve this without that nasty set of code edits you are saying. If not, I need to hire someone to correct this, because even I as admin, when I go to my profile and click Paid Subscriptions, I am confused looking to that list, even if I added all them. Imagine how the possible buyers will feel. 

[Kindred]

Also directly changing the database is contraindicated

[Arantor]

something so basic

Would not suggest it was that basic. Sorting the custom fields in 2.1 is equally as complex and just adding 'sorting' produced multiple bugs.

[BigSerge]

Also directly changing the database is contraindicated

Why?

[BigSerge]

something so basic

Would not suggest it was that basic. Sorting the custom fields in 2.1 is equally as complex and just adding 'sorting' produced multiple bugs.

Basic in terms of function. As per the code, I do not know.

[Sir Osis of Liver]

If you change id_subscribe I'd be surprised if it doesn't break existing subscriptions.  They're linked to smf_log_subscribed, which contains the member info for each subscription.

[Arantor]

And changing id_subscribe doesn't even guarantee getting the outcome desired because it's not stored or retrieved in ID order, but the order they're physically stored on disk which has no bearing whatsoever on either the ID, order of creation or anything else.

[Sir Osis of Liver]

No, they're sorted by whatever order they happen to be in the database which is a very very different thing, and in no way guaranteed to be order of creation.

Looks like they can be reordered by copying each row and assigning new id_subscribe in proper sequence (i.e., 1 > 100, 2 >200, 3 >300, etc.), then deleting original rows and restoring original id_subscribe to each row.  Wouldn't try this on production database, but seems to work on test install.

[Arantor]

Even that isn't a guarantee though. There are honestly no guarantees about the exact placement of rows in a database table and any presumptions are simply that: presumptious.

If it worked the way you describe, in insertion order, the symptoms described by the OP wouldn't be the case.

You CANNOT make assumptions about how the database will organise things on disk. EVER. If you want something in a particular order you need an ORDER BY clause.

[Sir Osis of Liver]

Hmm, worked last night but today it doesn't, copied rows are added somewhat randomly.

If subscriptions were created in order in which they're to be displayed, id_subscribe should be in correct order, you just have to reorder table rows.  Something like this seems to work -

Code Select Expand


ALTER TABLE smf_subscriptions ORDER BY id_subscribe;


OP would have to restore original id_subscribe to all rows.

[BigSerge]

If you change id_subscribe I'd be surprised if it doesn't break existing subscriptions.  They're linked to smf_log_subscribed, which contains the member info for each subscription.

Well, then I have a problem, because I already do not remember the numbers of each one. I also reordered because I noticed that two of them had the same number id_subscribe, the 5, and no Nr. 6, so I had one with Nr. 14, two with Nr. 5, and I only have 13 subscriptions, so I "corrected" everything.

As you say below that I need to restore id_subscribe to all rows, I can't do it anymore. Well, I got a lesson.

Next time, I'd better not mess with what I don't know.

Now better to write down in a paper the users with subscriptions, and the respective end date, remove all the subscriptions, create new ones, and add again manually the users to the respective subscription?

[Sir Osis of Liver]

You can restore database backup from just before you made the changes.

[BigSerge]

True. I forgot that I had that possibility. Thanks

[Arantor]

I can't vouch whether this will work on a database that has been messed about with, but on a vanilla install this will let you reorder them.

Take a backup first, of course.

[BigSerge]

I can't vouch whether this will work on a database that has been messed about with, but on a vanilla install this will let you reorder them.

Take a backup first, of course.

I was able to reorder the table as it was before I messed with it, using a backup, but, when I tried to install your mod, I got these messages:

This mod is not compatible with your version of SMF, it requires 2.1 final or later.

The package you are trying to download or install is either corrupt or not compatible with this version of SMF.

But my version is 2.0.19, like I said on my first message, and I am in the middle of a new membership launching, and, can't upgrade to 2.1 now.

I am very thankful for your help, but, I can't install it.

[Arantor]

Argh, sorry about that, I just assumed you were on 2.1.

It's not a big rework for 2.0, just not something I can do until I'm done with work for the day.