Home JavaScript

Move Options Between Two Select Boxes

Today we are going to do a code that can move options between two select boxes and save the changes to the database.

I think this functionality will improve the user experience since the interface is intuitive.

In this example, we are going to have the first select box (left side) which contains the list or data of available objects, the buttons that will make the options move either to the left or right, the second select box (right side) which contains the list of assigned objects, and of course, the save button.

See my video demo here:

assigned_objects table – this will be used to store the ids of assigned objects.

CREATE TABLE IF NOT EXISTS `assigned_objects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL COMMENT ‘1=user,2=location’,
  `object_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

locations table – this will be used to store the location objects

—
— Table structure for table `locations`
—
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

—
— Dumping data for table `locations`
—

INSERT INTO `locations` (`id`, `name`) VALUES
(1, ‘Cavite’),
(2, ‘Laguna’),
(3, ‘Batangas’),
(4, ‘Rizal’),
(5, ‘Quezon’),
(6, ‘Antiipolo’),
(7, ‘Capiz’),
(8, ‘Boracay’),
(9, ‘Mindoro’),
(10, ‘Novaliches’);

Our index.php code:

<html>
    <head>
        <title>Move Options Between Two Select Boxes And Save Changes To Database</title>
        <!– include some style –>
        <link rel=”stylesheet” type=”text/css” href=”css/style.css” />
    </head>
<body>
<?php
//connect to database
include ‘db_connect.php’;

//if the form was submitted
if( $_POST ){

    //delete previously assigned locations
    //in this example, type = 2 means “location”
    $sql = “delete from assigned_objects where type = 2”;
    
    //execute the sql
    if( $mysqli->query( $sql ) ){
    
        //if the query was executed successfully
        //save newly assigned names
        //$_POST[‘location_right’] is the select box on the right side
        foreach( $_POST[‘location_right’] as $value ){
            
            //write save query
            $sql = “insert into assigned_objects ( type, object_id ) 
                        values( 2, “ . $mysqli->real_escape_string( $value ) . ” )”;

            //execute save sql query
            if( !( $mysqli->query( $sql ) ) ){
                
                //if the query execution was unsuccessful
                echo “<div>Database Error: Unable to insert record.</div>”;
            }
            
        }
        
    }else{
        //if unable to delete previously assigned locations
        echo “<div>Database Error: Unable to delete assigned locations.</div>”;
    }
    
}

//get ids of currently assigned locations 
$sql = “select * from assigned_objects where type = 2”;
$result = $mysqli->query( $sql );

if( $result ){
    
    //$location_in will be our variable to accumulate currently assigned locations ids
    //example value will be “46, 49, 51”, 
    //which will be used to exempt currently assigned locations (right select box) 
    //to available locations list (left select box)
    $location_in = “”;
    
    //get number of rows found
    $num = $result->num_rows;
    
    if( $num ){ //if there are assigned locations in the database
    
        //set $x to 1, the first loop
        $x = 1;
        
        while( $row = $result->fetch_assoc() ){ //loop the accumulate values
        
            //object_id is actually the location id
            $location_in .= $row[‘object_id’];
            
            //when $x is less than the total number of rows, append a comma
            //else, do not append a comma
            if( $x < $num ){
                $location_in .= “, “;
            }
            
            //increment $x
            $x++;
        }
        
    }

}else{
    //when there’s an error selecting the assigned locations
    echo “Database Error: Unable to select assigned locations.”;
}

?>

<div>

<!–
    here on our form, the onsubmit value is very important,
    it will select all the values in the location_right select box,
    which is to be saved in the database.
    see the javascript section for details about selectAll()
–>
<form action=’index.php’ method=’post’ onsubmit=”return selectAll( new Array( ‘location_right’ ) );”>

    <!– left select box –>
    <div class=’select_box’>
        <div class=’select_title’>Available Locations</div>
        <div style=’clear:both;’></div>
        <select name=’location_left’ id=’location_left’ size=’7′ multiple=’multiple’>
            <?php
            
            if( $location_in ){ //if $location_id has a value, there are location ids to be exempted
                $sql = “select * from locations where id not in ( {$location_in} ) order by name”; 
            }else{
                //else, select all locations
                $sql = “select * from locations”; 
            }
            
            $result = $mysqli->query( $sql );

            if( $result ){
                //if it returns a result, loop through it with options tag
                while( $row = $result->fetch_assoc() ){
                    echo “<option value=’{$row[‘id’]}‘>{$row[‘name’]}</option>”;
                }
            }
            ?>
        </select>
    </div>

    <!– direction move buttons –>
    <div class=’btn’>
        <!– option move to right button –>
        <button type=”button” onclick=”move( ‘location_left’, ‘location_right’ )”> > </button>
        <br />
        <!– option move to left button –>
        <button type=”button” onclick=”move( ‘location_right’, ‘location_left’ )”> < </button>
    </div>

    <!– right select box –>
    <div class=’select_box’>
        <div class=’select_title’>Assigned Locations</div>
        <div style=’clear:both;’></div>
        <select name=’location_right[]’ id=’location_right’ size=’7′ multiple=’multiple’>
        
            <?php       
            //query joining assigned_objects and locations table
            //so we can easily get the location_name
            $sql = “select
                        l.id as location_id, 
                        l.name as location_name 
                    from 
                        assigned_objects ao, locations l
                    where 
                        ao.type = 2 
                        and ao.object_id in ( {$location_in} )
                        and ao.object_id = l.id”;
                    
            $result = $mysqli->query( $sql );

            if( $result ){
                //if query successful, loop through the values with option tag
                //so it will show up in the select list
                while( $row = $result->fetch_assoc() ){
                    
                    echo “<option value=’{$row[‘location_id’]}‘>{$row[‘location_name’]}</option>”;
                
                }
            }
            
            ?>
        </select>
        
    </div>
    
    <div style=’clear:both;’></div>
    <input type=’submit’ value=’Save’ />
    
</form>

<script type=’text/javascript’>
//this function will select all the values of the right select box, so you can post it
//this function can accept array of select boxes with id as its reference
function selectAll( obj_arr ){

    var obj_sel;
    for ( var i = 0; i < obj_arr.length; i++ ){
    
        obj_sel = document.getElementById( obj_arr[i] );
        
        for( var j = 0; j < obj_sel.options.length; j++ ){
            obj_sel.options[j].selected = true;
        }
        
    }
    
}

function move( id_1, id_2 ){
        
        //the box where the value will come from
        var opt_obj = document.getElementById( id_1 );
        
        //the box where the value will be locationd
        var sel_obj = document.getElementById( id_2 );
        
        for ( var i = 0; i < opt_obj.options.length; i++ ){ //loop to check for multiple selections
                
                if ( opt_obj.options[i].selected == true ){ //check if the option was selected
                        
                        //value to be transfered
                        var selected_text = opt_obj.options[i].text;
                        var selected_value = opt_obj.options[i].value;
                        
                        //remove from opt
                        opt_obj.remove( i );
                        
                        //decrease value of i since an option was removed 
                        //therefore the opt_obj.options.length will also decrease
                        i–;
                        
                        //process to sel
                        var new_option_index = sel_obj.options.length;
                        sel_obj.options[new_option_index] = new Option( selected_text, selected_value );
                        
                }
                
        }
}
        
</script>

</body>
</html>

I have another example here that uses two objects which are users and locations. See my video demo:

users table – this will be used to store the user objects.

—
— Table structure for table `users`
—
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=55 ;

—
— Dumping data for table `users`
—

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `modified`) VALUES
(28, ‘John’, ‘Dalisay’, ”, ‘john’, ‘john123’, ‘2012-01-15 15:26:14’),
(39, ‘Jemski’, ‘Panlilios’, ‘jemboy@gmail.com’, ‘jemboy09’, ‘jem123’, ‘2012-03-17 23:20:05’),
(40, ‘Darwin’, ‘Dalisay’, ”, ‘dada08’, ‘dada123’, ‘2012-01-15 15:25:34’),
(46, ‘Jaylord’, ‘Bitamug’, ”, ‘jayjay’, ‘jay123’, ‘2012-01-15 15:27:04’),
(49, ‘Justine’, ‘Bongola’, ”, ‘jaja’, ‘jaja123’, ‘2012-01-15 15:27:21’),
(50, ‘Jun’, ‘Sabayton’, ”, ‘jun’, ‘jun123’, ‘2012-02-05 18:15:14’),
(51, ‘Lourd’, ‘De Veyra’, ”, ‘lourd’, ‘lourd123’, ‘2012-02-05 18:15:36’),
(52, ‘Asi’, ‘Taulava’, ”, ‘asi’, ‘asi123’, ‘2012-02-05 18:15:58’),
(53, ‘James’, ‘Yap’, ”, ‘james’, ‘jame123’, ‘2012-02-05 18:16:17’),
(54, ‘Chris’, ‘Tiu’, ”, ‘chris’, ‘chris123’, ‘2012-04-11 17:16:29’);

The code download is included in the download link above of this post. I have written this code few years ago with MySQL, but you can easily convert it to MySQLi or PDO though. Here’s the mysql_index.php code:

<html>
        <head>
                <title>Move Options Between Two Select Boxes</title>
                
                <style type=’text/css’>
                        select{
                                width:200px;
                        }
                        
                        .select_box{
                                float:left;
                                margin:0 0 20px 0;
                        }
                        
                        .btn{
                                float:left;
                                margin:20px;
                        }
                </style>
        </head>
<body>
<?php
//connect to database
include ‘mysql_db_connect.php’;

if( $_POST ){

    //delete previously assigned names
    $sql = “delete from assigned_objects where type = 1”;
    mysql_query( $sql );
    
    //save newly assigned names
    foreach( $_POST[‘name_right’] as $value ){
        
        $sql = “insert into assigned_objects ( type, object_id ) values( 1, {$value} )”;
        mysql_query( $sql );
        
    }
    
    //delete previously assigned locations
    $sql = “delete from assigned_objects where type = 2”;
    mysql_query( $sql );
    
    //save newly assigned locations
    foreach( $_POST[‘location_right’] as $value ){
        
        $sql = “insert into assigned_objects ( type, object_id ) values( 2, {$value} )”;
        mysql_query( $sql );
        
    }
    
}

//(name) prepare id values for IN
$sql = “select * from assigned_objects where type = 1”;
$rs = mysql_query( $sql );

$in = “”;
$x = 1;
$num = mysql_num_rows( $rs );

if( $num ){
    while( $row = mysql_fetch_array( $rs ) ){
        extract( $row );
        $in .= $object_id;
        
        if( $x < $num ){
            $in .= “, “;
        }
        
        $x++;
    }
}

//(location) prepare id values for IN
$sql = “select * from assigned_objects where type = 2”;
$rs = mysql_query( $sql );

$location_in = “”;
$x = 1;
$num = mysql_num_rows( $rs );

if( $num ){
    while( $row = mysql_fetch_array( $rs ) ){
        extract( $row );
        $location_in .= $object_id;
        
        if( $x < $num ){
            $location_in .= “, “;
        }
        
        $x++;
    }
}
?>

<!–
    here on our form, the onsubmit value is very important,
    it will select all the values in the location_right select box,
    which is to be saved in the database.
    see the javascript section for details about selectAll()
–>
<form action=’#’ method=’post’ onsubmit=”return selectAll( new Array( ‘name_right’, ‘location_right’ ) );”>

    <!– left select box –>
    <div class=’select_box’>
        <div class=’select_title’>Available Users</div>
        <div style=’clear:both;’></div>
        <select name=’name_left’ id=’name_left’ size=’7′ multiple=’multiple’>
        
            <?php
            
            if( $in ){ //if $in has a value, there are location ids to be exempted
                $sql = “select * from users where id not in ( {$in} ) order by firstname”; 
            }else{
                //else select all users
                $sql = “select * from users”; 
            }
            
            $rs = mysql_query( $sql );
            
            while( $row = mysql_fetch_array( $rs ) ){
                extract( $row );
                echo “<option value=’{$id}‘>{$firstname} {$lastname}</option>”;
            }
            
            ?>
        </select>
    </div>

        <!– direction move buttons –>
        <div class=’btn’>
            <!– move to right button –>
            <button type=”button” onclick=”move( ‘name_left’, ‘name_right’ )”> > </button>
            <br />
            <!– move to left button –>
            <button type=”button” onclick=”move( ‘name_right’, ‘name_left’ )”> < </button>
        </div>

    <!– right select box –>
    <div class=’select_box’>
        <div class=’select_title’>Assigned Users</div>
        <div style=’clear:both;’></div>
        <select name=’name_right[]’ id=’name_right’ size=’7′ multiple=’multiple’>
        
            <?php       
            
            $sql = “select
                        u.id as user_id, 
                        u.firstname as fn,
                        u.lastname as ln
                    from 
                        assigned_objects ao, users u
                    where 
                        ao.type = 1
                        and ao.object_id in ( {$in} )
                        and ao.object_id = u.id”;
            $rs = mysql_query( $sql );
            
            while( $row = mysql_fetch_array( $rs ) ){
                
                echo “<option value=’{$row[‘user_id’]}‘>{$row[‘fn’]} {$row[‘ln’]}</option>”;
                
            }
            
            ?>
        </select>
        
    </div>

    <div style=’clear:both;’></div>

    <!– left select box –>
    <div class=’select_box’>
        <div class=’select_title’>Available Locations</div>
        <div style=’clear:both;’></div>
        <select name=’location_left’ id=’location_left’ size=’7′ multiple=’multiple’>
            <?php
            
            if( $location_in ){
                $sql = “select * from locations where id not in ( {$location_in} ) order by name”; 
            }else{
                $sql = “select * from locations”; 
            }
            
            $rs = mysql_query( $sql );
            
            while( $row = mysql_fetch_array( $rs ) ){
                extract( $row );
                echo “<option value=’{$row[‘id’]}‘>{$row[‘name’]}</option>”;
            }
            
            ?>
        </select>
    </div>

        <!– direction move buttons –>
        <div class=’btn’>
            <!– move to right button –>
            <button type=”button” onclick=”move( ‘location_left’, ‘location_right’ )”> > </button>
            <br />
            <!– move to left button –>
            <button type=”button” onclick=”move( ‘location_right’, ‘location_left’ )”> < </button>
        </div>

    <!– right select box –>
    <div class=’select_box’>
        <div class=’select_title’>Assigned Locations</div>
        <div style=’clear:both;’></div>
        <select name=’location_right[]’ id=’location_right’ size=’7′ multiple=’multiple’>
        
            <?php       
            $sql = “select
                        l.id as location_id, 
                        l.name as location_name 
                    from 
                        assigned_objects ao, locations l
                    where 
                        ao.type = 2 
                        and ao.object_id in ( {$location_in} )
                        and ao.object_id = l.id”;
            $rs = mysql_query( $sql );
            
            while( $row = mysql_fetch_array( $rs ) ){
                
                echo “<option value=’{$row[‘location_id’]}‘>{$row[‘location_name’]}</option>”;
                
            }
            
            ?>
            
        </select>
    </div>
    
    <div style=’clear:both;’></div>
    <input type=’submit’ value=’Save’ />
        
</form>

<script type=’text/javascript’>

function selectAll( obj_arr ){

    var obj_sel;
    for ( var i = 0; i < obj_arr.length; i++ ){
    
        obj_sel = document.getElementById( obj_arr[i] );
        
        for( var j = 0; j < obj_sel.options.length; j++ ){
            obj_sel.options[j].selected = true;
        }
        
    }
    
}

function move( id_1, id_2 ){
        
        //the box where the value will come from
        var opt_obj = document.getElementById( id_1 );
        
        //the box where the value will be locationd
        var sel_obj = document.getElementById( id_2 );
        
        for ( var i = 0; i < opt_obj.options.length; i++ ){ //loop to check for multiple selections
                
                if ( opt_obj.options[i].selected == true ){ //check if the option was selected
                        
                        //value to be transfered
                        var selected_text = opt_obj.options[i].text;
                        var selected_value = opt_obj.options[i].value;
                        
                        //remove from opt
                        opt_obj.remove( i );
                        
                        //decrease value of i since an option was removed 
                        //therefore the opt_obj.options.length will also decrease
                        i–;
                        
                        //process to sel
                        var new_option_index = sel_obj.options.length;
                        sel_obj.options[new_option_index] = new Option( selected_text, selected_value );
                        
                }
                
        }
}
        
</script>

</body>
</html>

If you have many object types stored in your database, you have to use a loop to generate the pair of select boxes and buttons.

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

THANK YOU!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

Thank you for learning from our post about: Move Options Between Two Select Boxes.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.