July 09, 2007 WEBDEV: Build an option list using SQL 
After converting another site’s option list to SQL to insert the values into my database, I want to get those values back out into an option list with IDs.
I started with:
<option value="Acura" >Acura</option>
<option value="Alfa Romeo" >Alfa Romeo</option>
<option value="American Motors" >American Motors</option>
<option value="Audi" >Audi</option>
<option value="BMW" >BMW</option>
...
and ran:
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Acura', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Alfa Romeo', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'American Motors', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Audi', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'BMW', NOW(), NOW());
...
and now have my own set of Answers with their own IDs. I want a select list where each option has an Answer and its ID from my database. To build this list in MySQL*, I use:
SELECT CONCAT('<option value="' , Id , '">' , Text , '</option>') FROM Answer
which results in:
<option value="1">Acura</option>
<option value="2">Alfa Romeo</option>
<option value="3">American Motors</option>
<option value="4">Audi</option>
<option value="5">BMW</option>
...
* In SQL Server you could write:
SELECT '<option value="' + Id + '">' + Text + '<option>' FROM Answer
