How to Use MySQL SET Type Field to Populate the DOM

Much like my previous post How to Use MySQL ENUM Type Field to Populate the DOM, I will show you how to do the same type of thing with the SET() column type.

1) Make your table:

CREATE TABLE IF NOT EXISTS `users` (
    `sex` SET('Male', 'Female') DEFAULT 'Male,Female'
) ENGINE=INNODB;

2) The logic of the PHP to prepare DOM population:

<?php
// Database Connection Script (PDO) here

$genderstmt = $conn->query( "SHOW COLUMNS FROM `users` LIKE 'sex'" );
while ( $row = $genderstmt->fetch() ) {
  $columntype = $row[ 'Type' ];
  $columndefault = $row['Default'];
}

$output = str_replace( "set('", "", $columntype );
$output = str_replace( "')", "", $output );
$results = explode( "','", $output );

$default = explode(',', $columndefault); // this will determine the options that are pre-selected / pre-checked
?>

3) Fill the DOM: a) SELECT

echo '<select class="form-control" id="gender" name="gender[]" multiple>';
for ( $i = 0; $i < count( $results ); $i++ ) {
  echo '<option value="' . $results[ $i ] . '"'.($results[$i]==$default?' selected':'').'>' . $results[ $i ] . '</option>';
}
echo '</select>';

b) INPUT[type="checkbox"]

for ( $i = 0; $i < count( $results ); $i++ ) {
  echo '<input type="checkbox" name="gender[]" value="' . $results[ $i ] . '"' . ( $results[ $i ] == $default ? ' checked' : '' ) . '>';
  echo '<label for="' . $results[ $i ] . '">' . ucwords( $results[ $i ] ) . '</label><br>'; // the <br> is optional if you want everything on the same line
}

And much much like my post showing how to do this with ENUM, for SET;

-- To add more genders
ALTER TABLE `users` MODIFY COLUMN `sex` set('Male', 'Female', 'X') DEFAULT 'Male,X';

-- To remove genders
ALTER TABLE `users` MODIFY COLUMN `sex` set('Female') DEFAULT 'Female';