Populate a select box with the contents of a table (PHP/MySQL)

Posted by admin at December 29, 2016

Try populating a select box with the names of all the countries in the world. You will end up with more than 2000 entries and a very large HTML file, especially if you require this select box for several fields in a single form. Here is a simple solution to populating a select box with values from a MySQL database:

SQL:

First populate a MySQL table with the countries list. A complete SQL file can be downloaded here



--
-- Table structure for table `tbl_states`
--

DROP TABLE IF EXISTS `tbl_states`;
CREATE TABLE IF NOT EXISTS `tbl_states` (
    `state_id` int(11) NOT NULL AUTO_INCREMENT,
    `state_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name of the Country',
    `state_un_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'United Nation Code',
    `state_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'Country two-letter Code',
    PRIMARY KEY (`state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_states`
--

INSERT INTO `tbl_states` (`state_id`, `state_name`, `state_un_code`, `state_code`) VALUES
('1', '6', 'Afghanistan', 'AF'),
('2', '9', 'Albania', 'AL'),
('3', '12', 'Algeria', 'DZ'),
('4', '505', 'Andorra', 'AD'),
('5', '681', 'Angola', 'AO'),
('6', '', 'Antarctica', 'AQ'),
('7', '601', 'Antigua and Barbuda', 'AG'),
('8', '24', 'Argentina', 'AR'),
('9', '26', 'Armenia', 'AM'),
('10', '', 'Aruba', 'AW'),
('11', '27', 'Australia', 'AU'),
('12', '30', 'Austria', 'AT');

PHP function


/**
*
* function to generate the options of a select boxes
*
* $tbl          is the table to generate values from
* $opt_value    is the name of the column to use for the option value property
* $opt_text     is the name of the column to use for the option text property
* $rid          is the record id representing the selected option
* $conn         is the database connector resource
*
*/

function select_box($tbl, $opt_value, $opt_text, $rid, $conn)
{
    // build the read query
    $sql = get_read_sql($tbl);
    
    // submit the query to generate rows
    $rs = mysql_query($sql, $conn) or die(mysql_error());
    
    // fetch the first 
    $row = mysql_fetch_assoc($rs);
    
    // calculate total rows
    $total_rows = mysql_num_rows($rs);
    
    // determine the selected option
    $sel = ($rid == 0) ? 'selected' : '';
    
    // initiate the options html
    $html = '<option value="0" '.$sel.'>--select--</option>';
    
    // loop throw the result count
    for ($i=0; $i<$total_rows; $i++)
    {
        // if the current options is the selected option then set the option's selected property
        $sel = ($row[$opt_value] == $rid) ? 'selected' : '';
        
        // append the current options html
        $html .= '<option value="'.$row[$opt_value].'" '.$sel.'>'.$row[$opt_text].'</option>';
        
        // generate the next row
        $row = mysql_fetch_assoc($rs);
    }
    
    // return the html
    return $html;
}

Notice the function,

$sql = get_read_sql($tbl);

This function would typically be a SELECT query generating values from the countries table.

$sql = 'SELECT * FROM `tbl_states` WHERE 1'; 

However, for normalised tables this function would be a lot more complex. For more information on this function, check out this tutorial here

HTML:


Country: <select id="sel_id" name="sel_name">
<?php echo select_box('tbl_states', 'state_id', 'state_name', 0, $conn); ?>
</select>

Notice the “0” in the select_box(); function. This means that the first option in the select box. To select another option, put the selected index number in place of the “0”. You can decide to use the $_GET parameter or a value from a MySQL table.


<?php
{
    
    // get the state
    $tbl = 'tbl_states';
    
    // get the selected state from the GET parameter
    $get_stid = mysql_real_escape_string($_GET['stid']);
    $get_stid = (strlen($get_stid) > 0) ? $get_stid : 0;
    
    // OR use a value from the MySQL index :: $row['state_id'];
}
?>

Country: 
<select id="state_id" name="state_id">
    <?php echo select_box($tbl, 'state_id', 'state_name', $get_stid, $conn); ?>
</select>
   0 likes

Suggested Read