dependent dropdown list by using single table value

Question

I need to to create dependent dropdown. First select the client. In the next dropdown list it will shows the listof projects that belongs to the selected client. After selecting the project third dropdown list will filter tasks belongs to that project. I need to do this with a one table in the database without primary key foreign key relationship. My task table have 1.client name 2. project name 3. tasks name here one client has multiple project one project has multiple tasks

Maybe I'm asking very simple question.But I am unable to proceed with my project without doing this part.Please someone helps me. This is my table structure

Answer 1

If you are using same table to store Client, Projects & Tasks, So it's possible you will create more duplicate records e.g. Might be project name duplicate or task duplicate or client name ....

Although it's up to your choice and convenient.. Basically Ajax Dropdown (Dependent dropdown) is fully action & Ajax based. So you know what and when you want through Ajax then you can build this easy..

Here is an example for CodeIgniter Lovers!! :D

CodeIgniter Controller:

<?php
// security first always....
(defined('BASEPATH') or exit('No direct script access allowed'));
/**
 * Class Controller
 *
 * Class Dropdown Controller to handle login & logout
 */
class Dropdown extends CI_controller
{
    /**
     * Class Constructor
     */
    public function __construct()
    {
        // execute parent class constructor
        parent::__construct();
        // load model
        $this->load->model('Dropdown_model');
    }

    /**
     * Default method to execute if method name missing
     * @return [type] [description]
     */
    public function index()
    {
        $array_data = array();
        // only on Ajax Request
        if ($this->input->is_ajax_request()) {
            // if request for projects
            if ($this->input->post('action') && $this->input->post('action') == 'project') {
                // get client name
                $client     = $this->input->post('client', true);
                // get project data by client name
                $array_data = $this->Dropdown_model->get_dropdown_data(trim($client), 'project');
                // AjaxPOST JSON response
                echo json_encode($array_data);die();
            }
            // if request for task
            if ($this->input->post('action') && $this->input->post('action') == 'task') {
                // get project name
                $project    = $this->input->post('project', true);
                // get task data by project
                $array_data = $this->Dropdown_model->get_dropdown_data(trim($project), 'task');
                // AjaxPOST JSON response
                echo json_encode($array_data);die();
            }
        }
        // else get all client data
        $array_data = $this->Dropdown_model->get_dropdown_data(null, null);
        // send to view
        $this->load->view('dropdown', ['data' => $array_data]);
    }
}

/* End of file dropdown.php */
/* Location: ./application/controllers/dropdown.php */

CodeIgniter Model:

<?php
// No direct script execution
defined('BASEPATH') or exit('No direct script access allowed');
/**
 * Class Dropdown_model to handle all related information from MySQL
 */
class Dropdown_model extends CI_Model
{
    /**
     * MySQL table which contains all data about users
     * @var string
     */
    protected $table = 'task';
    /**
     * Returns, User First Name by Email ID
     * @param  [type] $email_addres   [description]
     * @return [type] [description]
     */
    public function get_dropdown_data($where_data = null, $type = null)
    {
        $query = '';

        // clients only
        if (is_null($type) && is_null($where_data)) {
            // desire column from table
            $this->db->select('client_name');
            // only unique clients
            $this->db->distinct('client_name');
            // mysql table
            $query = $this->db->get($this->table);
        }

        // projects by client
        elseif ($type == 'project' && !is_null($where_data)) {
            // desire column from table
            $this->db->select('project_name');
            // where clause
            $this->db->where('client_name', $where_data);
            // mysql table
            $query = $this->db->get($this->table);
        }

        // task by project
        elseif ($type == 'task' && !is_null($where_data)) {
            // desire column from table
            $this->db->select('task');
            // where clause
            $this->db->where('project_name', $where_data);
            // mysql table
            $query = $this->db->get($this->table);
        }

        // if record exist
        if ($query->num_rows() > 0) {
            // return all data as array
            return $query->result_array();
        } else {
            // error
            return false;
        }
    }
}

/* End of file Dropdown_model.php */
/* Location: ./application/models/Dropdown_model.php */

CodeIgniter View:

<!DOCTYPE html>
<html>
<head>
    <title>CodeIgniter: Dependent dropdown list by using single table value</title>
    <!-- load bootstrap css -->
    <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <!-- load jquery library -->
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <!-- load bootstrap js -->
    <script type="text/javascript" src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="form-group">
            <label for="name">Client</label>
            <select class="form-control" id="clients" name="clients">
                <option value="0">--Select Clients--</option>
                <?php if (isset($data)):?>
                    <?php foreach ($data as $key => $value): ?>
                        <option value="<?=$value['client_name']?>"><?=$value['client_name']?></option>
                    <?php endforeach ?>
                <?php endif ?>
            </select>
        </div>
        <div class="form-group">
            <label for="name">Projects</label>
            <select class="form-control" id="projects" name="projects">
                <option value="0">--Select Projects--</option>
            </select>
        </div>
        <div class="form-group">
            <label for="name">Tasks</label>
            <select class="form-control" id="tasks" name="tasks">
                <option value="0">--Select Tasks--</option>
            </select>
        </div>
        <div class="form-group">
            <input type="button" class="btn btn-primary" value="Submit">
        </div>
    </div>
    <script type="text/javascript">
        $(document).ready(function(){
            // client select box
            var $client     = $('select#clients');
            // project select box
            var $projects   = $('select#projects');
            // task select box
            var $tasks      = $('select#tasks');
            // on change client name, get projects
            $client.on('change', function () {
                // get selected client name
                var client = $(this).find('option:selected').val();
                // post data with CSRF token
                var data = {
                    action:'project',
                    client: client,
                    "<?=$this->security->get_csrf_token_name()?>" : "<?=$this->security->get_csrf_hash()?>"
                };
                // AjaxPOST to get projects
                $.post('./dropdown', data, function(json) {
                    projects_data = '<option value="0">--Select Projects--</option>';
                    $.each(json, function(index, obj){
                        projects_data += '<option value="'+obj.project_name+'">'+obj.project_name+'</option>';
                    });
                    // append all projects in project dropdown
                    $projects.html(projects_data);
                }, 'JSON');
            });
            // on change project, get task
            $projects.on('change', function () {
                // get selected project name
                var project = $(this).find('option:selected').val();
                // AjaxPOSt wit CSRF
                var data = {
                    action:'task',
                    project: project,
                    "<?=$this->security->get_csrf_token_name()?>" : "<?=$this->security->get_csrf_hash()?>"
                };
                $.post('./dropdown', data, function(json) {
                    task_data = '<option value="0">--Select Task--</option>';
                    $.each(json, function(index, obj){
                        task_data += '<option value="'+obj.task+'">'+obj.task+'</option>';
                    });
                    // append all task data in Task dropdown
                    $tasks.html(task_data);
                }, 'JSON');
            });
        });
    </script>
</body>
</html>

MySQL table:

    id  client_name          project_name       task         
------  -------------------  -----------------  -------------
     1  Man Powers & Morans  Web Micro Site     Development  
     2  Mango vs Trees       Brand Development  Pitch        
     3  Man Powers & Morans  Ecom Site          Maintenance  
     4  Light One            Ecom Site          Maintenance  
     5  Mango vs Trees       Siteground         Development  

MySQL table structure:

Field         Type         Null    Key     Default  Extra           
------------  -----------  ------  ------  -------  ----------------
id            int(11)      NO      PRI     (NULL)   auto_increment  
client_name   varchar(85)  YES             (NULL)                   
project_name  varchar(85)  YES             (NULL)                   
task          varchar(85)  YES             (NULL)       

Html View Screenshot:

Answer 2

that i understand from your question. You may need to make select fields like this

<label>Clints</label>
<select name="clint" id="clint" class="form-control" onchange="return get_projects(this.value)">
<option value="">Select Clint</option>                
<?php foreach($clients as $clint){?>
   <option value="<?php echo $clint->id?>"><?php echo $clint->name?></option>
<?php }?>
</select>
<label>Projects</label>
<select name="projects" id="projects" class="form-control" onchange="return get_tasks(this.value)">
   <option value="">All</option>
</select>
<label>tasks</label>
<select name="tasks" id="tasks" class="form-control">
   <option value="">All</option>
</select>

and Script like this

function get_projects(clint_id) {
$.ajax({
    url: '<?php echo base_url();?>your controllef/your function/' + clint_id,
    success: function(response)

        jQuery('#projects).html(response);
    }
}); 
}
function get_tasks(project_id) {
$.ajax({
    url: '<?php echo base_url();?>your controllef/your function/' +project_id,
    success: function(response)
    {
        jQuery('#tasks).html(response);
    }
}); 
}

then you need to make function in controller like this

public function get_projects($clint_id)
{
    $project = $this->your_model->get_projects($clint_id);
    echo '<option value="">Select Project</option>';
    foreach($project as $row){          
        echo '<option value="' . $row->project . '" >' . $row->name . '</option>';
        }
}

public function get_tasks($project_id)
{
    $tasks = $this->your_model->get_tasks($project_id);
    echo '<option value="">Select Project</option>';
    foreach($tasks as $row){            
        echo '<option value="' . $row->task . '" >' . $row->name . '</option>';
        }
}