display data from database to dropdown CodeIgniter

Question 1

I'm having difficulty with display data from the db to dropdown.

This is what I have tried:

Model.php

        public function __construct()
        {
            parent::__construct();
        }

        function getAllGroups()
        {
            /*
            $query = $this->db->get('location');

            foreach ($query->result() as $row)
            {
                echo $row->description;
            }*/

            $query = $this->db->query('SELECT description FROM location');

            foreach ($query->result() as $row)
            {
                echo $row->description;
            }

            //echo 'Total Results: ' . $query->num_rows();
        }

Controller.php

    <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
    class Delivery_controller extends CI_Controller{
        public function __construct()
        {
            parent::__construct();
            $this->load->model('delivery_model');

        }
        public function index()
        {

            $data['title']= 'Warehouse - Delivery';
            $this->load->view('include/header',$data);
            $this->load->view('include/navbar',$data);
            $this->load->view('delivery_view', $data);
            $this->load->view('include/sidebar',$data);
            $this->load->view('include/footer',$data);
        $data['groups'] = $this->delivery_model->getAllGroups();
        }


    }

View.php

           <select class="form-control">
                <?php 
                        $data = $this->delivery_model->getAllGroups();
                foreach($description as $each)
                { ?><option value="<?php echo $each['description']; ?>"><?php echo $each['description']; ?></option>';
                <?php }
                ?>
                </select>

But the results appear on top of my page. It's not appearing on the dropdown list. What am I doing wrong in here? Help is pretty much appreciated. Thanks.

Question 2

You should not be calling your model from your view. Instead try calling you model and setting $data['groups'] before you load your views.

Also do not echo the row results in your model unless you want it displayed on your page.

Controller:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Delivery_controller extends CI_Controller{
    public function __construct()
    {
        parent::__construct();
        $this->load->model('delivery_model');

    }
    public function index()
    {

        $data['title']= 'Warehouse - Delivery';
        $data['groups'] = $this->delivery_model->getAllGroups();
        $this->load->view('include/header',$data);
        $this->load->view('include/navbar',$data);
        $this->load->view('delivery_view', $data);
        $this->load->view('include/sidebar',$data);
        $this->load->view('include/footer',$data);

    }


}

Model:

    public function __construct()
    {
        parent::__construct();
    }

    function getAllGroups()
    {
        /*
        $query = $this->db->get('location');

        foreach ($query->result() as $row)
        {
            echo $row->description;
        }*/

        $query = $this->db->query('SELECT description FROM location');


        return $query->result();

        //echo 'Total Results: ' . $query->num_rows();
    }

View:

       <select class="form-control">
            <?php 

            foreach($groups as $row)
            { 
              echo '<option value="'.$row->description.'">'.$row->description.'</option>';
            }
            ?>
            </select>

Question 3

To order the result by the order in your array, you can do the following:

$array_of_ordered_ids = array(4,5,2,6);

As you already know the order of the numbers, you can use the Mysql FIELD()^Docs function:

ORDER BY FIELD(id, 4, 5, 2, 6);

To create such a string, you can use implode^Docs:

$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));

Give it a try:

$array_of_ordered_ids = array(4,5,2,6);
$this->db->where_in('id', $array_of_ordered_ids);
$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));
$this->db->order_by($order);

Question 4

After going through the ChartJS documentation, I found and utilized the .update(config) method after previously initializing my chart with dummy data. Here's what the JavaScript file looks like now:

$(function() {
if ($("#subChart").length) {
    var chart = $("#subChart");
    var fontFamily = '"Proxima Nova W01", -apple-system, system-ui, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif';
    
    // set defaults
    Chart.defaults.global.defaultFontFamily = fontFamily;
    Chart.defaults.global.tooltips.titleFontSize = 14;
    Chart.defaults.global.tooltips.titleMarginBottom = 4;
    Chart.defaults.global.tooltips.displayColors = false;
    Chart.defaults.global.tooltips.bodyFontSize = 12;
    Chart.defaults.global.tooltips.xPadding = 10;
    Chart.defaults.global.tooltips.yPadding = 8;
    var data = {
        labels: ["Processed", "Pending", "Cancelled"],
        datasets: [
            {
                data: [1, 1, 1],
                backgroundColor: ["#85c751", "#6896f9", "#d97b70"],
                hoverBackgroundColor: ["#85c751", "#6896f9", "#d97b70"],
                borderWidth: 0
            }
        ]
    };

    // -----------------
    // init donut chart
    // -----------------
    var subsChart = new Chart(chart, {
        type: 'doughnut',
        data: data,
        options: {
            legend: {
                display: false
            },
            animation: {
                animateScale: true
            },
            cutoutPercentage: 80
        }
    });

    // donut chart data
    jQuery.ajax({
        type: "GET",
        url: 'dashboard/get/subscriptions',
        success: function(response) {
            console.log(response);

            var obj = JSON.parse(response);
            console.log(obj);
            console.log(obj[0].count);

            var active = obj[0].count;
            var pending = obj[1].count;
            var cancelled = obj[2].count;

            console.log('Initial value:' + subsChart.data.datasets[0].data);
            subsChart.data.datasets[0].data[0] = active;
            subsChart.data.datasets[0].data[1] = pending;
            subsChart.data.datasets[0].data[2] = cancelled;
            subsChart.update(); 

            console.log('Updated value:' + subsChart.data.datasets[0].data);
        }
    })
 }
});

and here's the output:

Many thanks to @briangottier

Question 5

Use $this->Skill->find("list"); to return an array of primary key-display field values that can be used in your dropdown. You might need to specify skill as the displayField for this to work correctly.

CakePHP 2

In CakePHP 2 the displayField is an attribute of the model:-

class Skill extends AppModel {

    public $displayField = 'skill';

}

Alternatively you can define the columns you want to be returned in the list when you perform your find():-

$this->Skill->find('all', [
    'fields' => [
        'Skill.id',
        'Skill.skill'
    ]
]);

CakePHP 3

In CakePHP 3 you can define the displayField in the model:-

class SkillsTable extends Table
{

    public function initialize(array $config)
    {
        $this->displayField('skill');
    }
}

If you want to do this on the find() query in CakePHP 3 you need to define the keyField and valueField:-

$this->Skill->find('list', [
    'keyField' => 'id',
    'valueField' => 'skill'
]);

Question 6

Jon is right. Here's an example:

$this->db->select('movies.id, 
                   movies.title, 
                   movies.year, 
                   movies.runtime as totaltime,  
                   posters.poster_url');
$this->db->from('movies');
$this->db->join('posters', 'movies.id= posters.id');
$this->db->where('movies.id', $id);
$q = $this->db->get();

This will return objects that have ->id, ->title, ->year, ->totaltime, and ->poster_url properties. You won't need the additional code to fetch the data from each row.

Don't forget, if the Active Record syntax gets a little unwieldy, you can use full SQL queries and get the same results:

$sql = "SELECT movies.id,
        movies.title,
        movies.year,
        movies.runtime as totaltime,
        posters.poster_url
        FROM movies
        INNER JOIN posters ON movies.id = posters.id
        WHERE movies.id = ?"

return $this->db->query($sql, array($id))->result();

Both forms will ensure that your data is escaped properly.

CodeIgniter Active Record

Query Binding in CodeIgniter

display data from database to dropdown CodeIgniter

Answers

php,mysql,codeigniter