SQL to display HTML for Dropdown

Question

SQL to display HTML for Dropdown

Ten Centuries

Points: 1310

September 21, 2021 at 8:36 pm

I have created a webpage with a form that allows people to register for an event. The event will occur on the same day at three different times, 10am, 1pm, 3pm. The event is limited to 10 people per time slot. I'm trying to write a query that generates the HTML <option> tag based on the data present in the table and display the remaining number of slots AND to eliminate the option if the Max number for any timeslots = 10

Here is the create table script.

CREATE TABLE dbo.EAA_ZIPLINE_REG (
[Full Name] nvarchar(50) NULL,
[Registration Time] nvarchar(50) NULL,
INSERTDATE datetime NULL DEFAULT (getdate())
);
GO

Next here is an insert script to throw some records into the table for the sake of your assistance:

INSERT INTO dbo.EAA_ZIPLINE_REG
           ([Full Name],[Registration Time]
        )
     VALUES
           ('Kevin Brady','10am'
   )

INSERT INTO dbo.EAA_ZIPLINE_REG
           ([Full Name],[Registration Time]
        )
     VALUES
           ('Dave Perdue','10am'
   )


INSERT INTO dbo.EAA_ZIPLINE_REG
           ([Full Name],[Registration Time]
        )
     VALUES
           ('Shelly Hunt','1pm'
   )

INSERT INTO dbo.EAA_ZIPLINE_REG
           ([Full Name],[Registration Time]
        )
     VALUES
           ('Mike Smith','3pm'
   )

INSERT INTO dbo.EAA_ZIPLINE_REG
           ([Full Name],[Registration Time]
        )
     VALUES
           ('Joe Buck','3pm'
   )

Here is the script I have created so far. I like the fact that I can query and get the entire HTML <option> string to then create a record set to display my page. However, I would like to disable the option OR simply not show the time slot option if the Max number of ten has been reached per any of the time slots. Not sure if a case statement could be used but I'm looking for some help on how to code for this. Thank you!

DECLARE @Remaining10am int
DECLARE @Remaining1pm int
DECLARE @Remaining3pm int

SET @Remaining10am = 10 - (
SELECT COUNT([Registration Time])
FROM dbo.EAA_ZIPLINE_REG
WHERE [Registration Date] = '10am')

SET @Remaining1pm = 10 - (
SELECT COUNT([Registration Time])
FROM dbo.EAA_ZIPLINE_REG
WHERE [Registration Date] = '1pm')

SET @Remaining3pm = 10 - (
SELECT COUNT([Registration Time])
FROM dbo.EAA_ZIPLINE_REG
WHERE [Registration Date] = '3pm')

SELECT CONCAT('<option value="10am">Thursday Oct. 21st 10am, Remaining: ', @Remaining10am, '</option>',
'<option value="1pm">Thursday Oct. 21st 1pm, Remaining: ', @Remaining1pm, '</option>',
'<option value="3pm">Thursday Oct. 21st 3pm, Remaining: ', @Remaining3pm, '</option>'

) AS 'HTMLOptionValues'

This topic was modified 2 years, 7 months ago by netguykb.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

That kind of formatting would likely better be done in the GUI, especially if you want any kind of custom formatting if there's no matches etc.... but if you have to do it in SQL something like.

SELECT '<option value="10am">Thursday Oct. 21st ' + [Registration Time] + ', Remaining: ' + CAST(10 - COUNT(*) AS varchar(30)) + '</option>'
FROM EAA_ZIPLINE_REG 
GROUP BY [Registration Time]
HAVING COUNT(*) < 10

netguykb Ten Centuries Points: 1310 More actions · Answer 2

I hear ya but I still need to query the DB to know how many slots are available for each time session. The problem with the above code assumes there are records already in the DB. On day one when the person registered no options would be able based on the GROUP BY clause. Also each <option value=""> must include each time slot. 10,1,3 the above hard code only uses the value - 10 am. thus only 10am would be added for every insert.

One fix to the first issue is easy:

SELECT '<option value="'+[Registration Time]+'">Thursday Oct. 21st ' + [Registration Time] + ', Remaining: ' + CAST(10 - COUNT(*) AS varchar(30)) + '</option>'
FROM EAA_ZIPLINE_REG 
GROUP BY [Registration Time]
HAVING COUNT(*) < 10

However, the GROUP BY is now the concern because on day one there are no records.

This reply was modified 2 years, 7 months ago by netguykb.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

Well you're probably going to have to have something to handle issues in the GUI, for example when all the slots are full. But this should fix the other issue.

SELECT '<option value="'+TIMES.SLOT+'">Thursday Oct. 21st ' + TIMES.SLOT + ', Remaining: ' + CAST(10 - COUNT(EAA_ZIPLINE_REG.[Full Name]) AS varchar(30)) + '</option>' 
FROM (VALUES('3pm'), ('1pm'), ('10am')) TIMES(SLOT)
LEFT OUTER JOIN EAA_ZIPLINE_REG ON TIMES.SLOT = EAA_ZIPLINE_REG.[Registration Time]
GROUP BY TIMES.SLOT
HAVING COUNT(EAA_ZIPLINE_REG.[Full Name]) < 10

netguykb Ten Centuries Points: 1310 More actions · Answer 4

Yes, you are correct on GUI side and I think I have a way to handle it. The Query you provided is spot on.

I have broken the query out so now I can use the count value in an IF statement to indicate if value is NOT zero then display all three values in the HTML and loop through the recordset.

Perfect!!! Screen Shot 2021-09-22 at 11.07.22 AM

This reply was modified 2 years, 7 months ago by netguykb.