On Sun, 7 Feb 2021 at 11:54, Phil Thane phil@pthane.co.uk wrote:
That's do-able via the WP dashboard but a bit of a pain. Finding the last number issued on a particular sequence involves inspired guesswork and wildcard searching.
Lots of "don't do it like that" with some valid arguments but just for the fun of it I figured it might be worth trying how to do it like that anyway.
But whilst I think I understand the objective (find the next numeric suffix for a given alpha prefix) I don't really understand where the code to do it will go.
At the (My)SQL level it's a bit messy but not hard. For a field member_number in table members of surname 'surname': SELECT member_number FROM members WHERE field_member LEFT(member_number,1) = LEFT(surname,1) ORDER BY LENGTH(member_number) DESC, member_number DESC LIMIT 1
.. should get you the highest existing member number (MySQL doesn't have a natural sort option so sorting T123 is treated as lower than T35 (alphabetical ordering), hence the sort by length as well).
So, to get the next one (everything from FROM unchanged):
SELECT CONCAT( UPPER(LEFT(surname,1)), CAST(RIGHT(member_number, LENGTH(member_number)-1) AS SIGNED) + 1 ) as member_number FROM members WHERE field_member LEFT(member_number,1) = LEFT(surname,1) ORDER BY LENGTH(member_number) DESC, member_number DESC LIMIT 1
(untested but it should be close).
Or, to insert a new record with that as the ID: INSERT INTO members (surname,membership_number) VALUES (surname, SELECT CONCAT( UPPER(LEFT(surname,1)), CAST(RIGHT(member_number, LENGTH(member_number)-1) AS SIGNED) + 1 ) as member_number FROM members WHERE field_member LEFT(member_number,1) = LEFT(surname,1) ORDER BY LENGTH(member_number) DESC, member_number DESC LIMIT 1 ) )
But the question would be how to use that in your scenario.
Also it would probably be quite slow with a gazillion records but for a few thousand it ought to be OK.
Easier would be to split the membership number into two fields, member_number_prefix and member_number (the latter being an int) which would make a lot of this trivial, but assuming you're wanting to work within existing WP database fields that wouldn't be ideal.