I have a form with a dropdown selection whose option are determined from a table on a database. Periodically I will (manually) add more options to the dropdown and the database. Either I can make the dropdown selection get the options from the database like this:
<select class="form-control" name="category" id="category">
<?php
$sql = "SELECT id, category FROM categories;";
$result = $GLOBALS['conn']->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$category = $row['category'];
$id = $row['id'];
echo "<option value='$id'>$category</option>";
}
}
?>
</select>
Which will give me less work because all I have to update now is the database and the form will update itself.
Or I can manually input the options into the form like this:
<select class="form-control" name="category" id="category">
<option value='1'>option1</option>
<option value='2'>option2</option>
<option value='3'>option3</option>
</select>
This way will require more manual work but has the advantage of not having to connect to the database each time (less work for the servers).
My question is which method should I use? The method that gives me more work or the method that gives the server more work?
There are arguments pro and against using a database for select. As for the pro arguments, it is relatively easy to update - you just insert a new value and it will appear in your form. The problem with this approach is that your application will do a query each time that you render your form. In websites with millions of clicks this is not a good idea. So, the alternative used is that the query result is cached in some intermediary layer, especially when these values are not changing a lot. From my experience, the best way to handle these values is to load them from configuration files. These are values that rarely change and a new value can be added there. These can be still cached and a database hit is not required.