Dynamic sorted A-Z Index with anchor links

Wappler General
1

I’ve got brain freeze again!

I want to do a dynamic A-Z Index of items (via a multiple records query) and simply organise by the initial letter of the title field. And to then have divider title block with A, B, C etc.

I am struggling as to where to go from here. Any help, guidance or sample code would be appreciated.

I can do the anchor links fine (red boxes)

image
image1199×740 53.1 KB

This is what the MySQL 'library_title' field typically looks like

image

I can do a 'substr' to get the initial letter

SELECT	lib.library_id,
	lib.library_desc,
	lib.library_title,
    substr(lib.library_title,1,1) as alpha, 
	lib.library_text,
	lib.data_order,

I’d also like to do something like this with the A, B, C where the letters are bold if there is content under that heading

image

2

I hate it when my brain kicks in to gear at midnight when I really want to go to bed!

Anyway, glad it did because I started to remember some SQL which got lost in the memory banks. For your info, here is the SQL and a screenshot of the new layout.

SELECT /* Finds unique A-Z characters ie A B D G etc */
    '' AS library_id,
    UPPER(SUBSTR(library_title, 1, 1)) AS library_title,
	'' AS library_desc,
	'' AS library_text,
	'' AS data_order,
	'' AS data_security,
	'' AS data_version,
	'' AS data_active,
	'' AS data_editor,
	'' AS data_created,
	'' AS data_modified,
	'' AS data_from,
	'' AS data_to,
	'' AS library_type
FROM
	tbl_library AS lib
WHERE
    lib.library_title != ''
GROUP BY
	lib.library_title 

UNION

SELECT /* Finds all required rows */
	lib.library_id,
	lib.library_title,
	lib.library_desc,
	lib.library_text,
	lib.data_order,
	lib.data_security,
	lib.data_version,
	lib.data_active,
	lib.data_editor,
	lib.data_created,
	lib.data_modified,
	lib.data_from,
	lib.data_to,
	libtype.list_label AS library_type
FROM
	tbl_library AS lib
LEFT JOIN	
	tbl_menu_list AS libtype
	ON (libtype.menu_list_id = lib.library_type)
WHERE
    lib.library_title != ''
ORDER BY
	library_title ASC

image
image1190×429 9.07 KB

My issue now is to sort out the A to Z list for the top of the page

image

Does anyone know if I can do it dynamically?
Something like…

  • RANGE(A,Z) to set the alphabet and then
  • Using the result from the above SQL to emphasis the matching used letters

Here is a screenshot of the resultant query, I have highlighted the Initial indexing letter. Note that there is no ‘O’ so this will be wanted in the ABC at the top of the screen but with no anchor link.

image
image772×298 28.7 KB