通过使用CodeIgniter单击链接按字母顺序对列表进行排序

Question

I am pulling information from a database and displaying it on my page. I want to sort the data from one of the database table alphabetically by clicking on a link. I was able to achieve this using pure PHP but unable to do so with CodeIgniter because of the MVC structure. Using pure PHP the code was like:

<?php
if(isset($_GET['let']))
    $let = $_GET['let'];
else
    $let = '';
$query = "SELECT supplier, contact, telephone, email FROM suppliers WHERE supplier LIKE '$let%'";
// other codes

And a separate sort.php with the code:

<a href="sort.php"> All </a> |
<a href="sort.php?let=A"> A </a> | 
<a href="sort.php?let=B"> B </a>

This is how my page looks like:

Here is what my model looks like by tring to use a function called getSort()

class Supplier_model extends CI_Model
{
    public function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
    }

    function getCity()
    {
        $query = $this->db->query('SELECT cityidd,city FROM citys');
        return $query->result_array();
    }

    function getPaymentMode()
    {
        $query = $this->db->query('SELECT id,paymentmode FROM paymentmodes');
        return $query->result_array();
    }

    function getBank()
    {
        $query = $this->db->query('SELECT id,bankname FROM banks');
        return $query->result_array();
    }

    function getCategories()
    {
        $query = $this->db->query('SELECT id,supplycategory FROM supplycategories');
        return $query->result_array();
    }

    function getStaff()
    {
        $query = $this->db->query('SELECT firstname,lastname FROM employees');
        return $query->result_array();
    }

    function getBankBranch($bank_id)
    {
        $query = $this->db->query('SELECT id,bankbranch FROM bankbranches WHERE bank='.$bank_id);
        return $query->result_array();
    }

    function getPosts()
    {
        $this->db->select("supplier,contact,telephone,email");
        $this->db->from('suppliers');
        $this->db->order_by("supplier", "asc");
        $query = $this->db->get();
        return $query->result();
    }

    function getSort()
    {
        $query = $this->db
                ->select('supplier,contact,telephone,email')
                ->from('suppliers')
                ->where('supplier')
                ->like('supplier', 'let', 'after')
                ->get();
        return $query->result();
    }

    function addSupplier()
    {
        $this->load->database();
        $supplier = $this->input->post('supplier');
        $taxpin = $this->input->post('taxpin');
        $contact = $this->input->post('contact');
        $addresss = $this->input->post('addresss');
        $citys = $this->input->post('citys');
        $telephone = $this->input->post('telephone');
        $email = $this->input->post('email');
        $website = $this->input->post('website');
        $paymentmodes = $this->input->post('paymentmodes');
        $kesaccount = $this->input->post('kesaccount');
        $usdaccount = $this->input->post('usdaccount');
        $banks = $this->input->post('banks');
        $bankbranches = $this->input->post('bankbranches');
        $bankcode = $this->input->post('bankcode');
        $swiftcode = $this->input->post('swiftcode');
        $mobilepaymentnumber = $this->input->post('mobilepaymentnumber');
        $mobilepaymentname = $this->input->post('mobilepaymentname');
        $chequeddressee = $this->input->post('chequeddressee');
        $status = $this->input->post('status');
        $categorysuppliers = $this->input->post('categorysuppliers');
        $staff = $this->input->post('staff');

        $data = array(
                'supplier' => $supplier, 'taxpin' => $taxpin, 'contact' => $contact, 'addresss' => $addresss,
                'citys' => $citys, 'telephone' => $telephone, 'email' => $email, 'website' => $website,
                'paymentmodes' => $paymentmodes, 'kesaccount' => $kesaccount, 'usdaccount' => $usdaccount,
                'banks' => $banks, 'bankbranches' => $bankbranches, 'bankcode' => $bankcode,
                'swiftcode' => $swiftcode, 'mobilepaymentnumber' => $mobilepaymentnumber,
                'chequeddressee' => $chequeddressee, 'status' => $status,
                'categorysuppliers' => $categorysuppliers, 'staff' => $staff);

        $this->db->insert('suppliers', $data);
    }

}

This is how my controller looks like by using a function called sort_suppliers:

<?php

class Supplier extends CI_Controller
{
    function __construct()
    {
        parent::__construct();
        $this->load->helper('url', 'form');
        $this->load->model('supplier_model');
        $this->load->database();
    }

    public function index()
    {
        $this->load->helper('form', 'html');
        $data['cities'] = $this->supplier_model->getCity();
        $data['modes'] = $this->supplier_model->getPaymentMode();
        $data['banks'] = $this->supplier_model->getBank();
        $data['categories'] = $this->supplier_model->getCategories();
        $data['staffs'] = $this->supplier_model->getStaff();
        $this->load->view('supplier_add', $data);
    }

    public function save()
    {
        $this->load->model('supplier_model');
        if($this->input->post('submit'))
        {
            $this->supplier_model->addSupplier();
        }
        redirect('supplier/view_suppliers');
    }

    public function view_suppliers()
    {
        $this->load->helper('form');
        $this->data['posts'] = $this->supplier_model->getPosts();
        $this->load->view('suppliers_view', $this->data);
    }

    public function sort_suppliers()
    {
        $this->load->helper('form');
        $this->data['sorts'] = $this->supplier_model->getSort();
        $this->load->view('suppliers_view', $this->data);
    }

    public function get_branches()
    {
        $bank_id = $this->input->post('bank_id');
        $bankbranch = $this->supplier_model->getBankBranch($bank_id);
        foreach($bankbranch as $branch)
        {
            echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
        }
    }

}

I am so confused of how my view should look like because I am not even sure if my model and controller is right. I want when the user clicks on the href link ALL to get all the list from the database and when the user clicks on any alphabet letter the list from the database should only display the sorted list based on the letter clicked by the user. Here is my mixed up view:

<!DOCTYPE html>
<html>
    <head>
        <title>Supplier</title>

        <style>
            table {
                border-collapse: separate;
                border-spacing: 0;
            }
            th,
            td {
                padding: 10px 15px;
            }

            tbody tr:nth-child(even) {
                background: #f0f0f2;
            }a:link {
                text-decoration: none;
            }

        </style>

    </head>
    <body>
        <strong>Suppliers</strong><br><br>
        <a href="sort.php?let=Y"> Active </a> |
        <a href="sort.php?let=Y"> Disabled </a> <br><br>
        <a href="<?php echo site_url('supplier/sort_suppliers') ?>">All</a> |
        <a href="sort.php?let=A"> A </a> | 
        <a href="sort.php?let=B"> B </a> |
        <a href="sort.php?let=C"> C </a> | 
        <a href="sort.php?let=D"> D </a> | 
        <a href="sort.php?let=E"> E </a> | 
        <a href="sort.php?let=F"> F </a> | 
        <a href="sort.php?let=G"> G </a> | 
        <a href="sort.php?let=H"> H </a> | 
        <a href="sort.php?let=I"> I </a> | 
        <a href="sort.php?let=J"> J </a> | 
        <a href="sort.php?let=K"> K </a> | 
        <a href="sort.php?let=L"> L </a> | 
        <a href="sort.php?let=M"> M </a> | 
        <a href="sort.php?let=N"> N </a> | 
        <a href="sort.php?let=N"> O </a> | 
        <a href="sort.php?let=P"> P </a> | 
        <a href="sort.php?let=Q"> Q </a> | 
        <a href="sort.php?let=R"> R </a> | 
        <a href="sort.php?let=S"> S </a> | 
        <a href="sort.php?let=T"> T </a> | 
        <a href="sort.php?let=U"> U </a> | 
        <a href="sort.php?let=V"> V </a> | 
        <a href="sort.php?let=W"> W </a> | 
        <a href="sort.php?let=X"> X </a> | 
        <a href="sort.php?let=Y"> Y </a> |
        <a href="sort.php?let=Z"> Z </a> |<br><br>
        <hr>
        <table>

            <thead>
                <tr>
                    <th align='left'>Supplier</th>
                    <th align='left'>Contact</th>
                    <th align='left'>Telephone</th>
                    <th align='left'>Email</th>
                    <th align='left'>LPO</th>
                    <th align='left'>Invoice</th>
                    <th align='left'>Profile</th>
                </tr>
            </thead>

            <?php foreach($posts as $post)
            { ?>

                <tr>
                    <td><?php echo $post->supplier; ?></td>
                    <td><?php echo $post->contact; ?></td>
                    <td><?php echo $post->telephone; ?></td>
                    <td><?php echo $post->email; ?></td>
                </tr>
<?php } ?>
        </table>
    </body>
</html>

Answer 1

Both your model and controller have the right idea though they could be a little more trick. This answer shows one possible approach.

Throughout this answer many functions shown in the question are not included because they don't affect the answer. There are a few of exceptions that are included because I want to comment on your code. (Sorry, can't help myself.)

First the controller.

Suppliers.php

class Suppliers extends CI_Controller
{
    function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->helper('url', 'form');
        $this->load->model('supplier_model');
    }

You have tendency to load things multiple times. CodeIgniter classes (helpers, libraries, models) are mostly "singletons". As such, if they are already loaded any attempt to load them again returns a reference to the previously loaded instance and does not create a new instance.

The first line in the next method has you loading a helper that's already loaded.

    public function index()
    {
        //$this->load->helper('form', 'html'); 'form' helper already loaded in constructor
        $this->load->helper('html');
        $data['cities'] = $this->supplier_model->getCity();
        $data['modes'] = $this->supplier_model->getPaymentMode();
        $data['banks'] = $this->supplier_model->getBank();
        $data['categories'] = $this->supplier_model->getCategories();
        $data['staffs'] = $this->supplier_model->getStaff();
        $this->load->view('supplier_add', $data);
    }

The next function in the Suppliers class combines view_suppliers() and sort_suppliers() functions into one. Giving the function's parameter a default value allows it to be called without passing a value.

    public function view($filter = "All")
    {
        if($filter === "All")
        {
            $filter = NULL;
        }
        $data['filter'] = $filter;
        $data['suppliers'] = $this->supplier_model->get_suppliers($filter);
        $this->load->view('suppliers_view', $data);
    }
}

Using the procedural approach to creating links like this

<a href="sort.php?let=A"> A </a>

won't work in the object oriented world of Codeigniter.

You cannot just link to a file, you have to link to a controller and possibly a function of that controller. CodeIgniter uses a segment-based approach to URLs. (Documentation)

example.com/controller/function/parameter

Directing a browser to example.com/suppliers will call suppliers->index() and example.com/suppliers/view calls suppliers->view().

You can pass a param to suppliers->view by using the "param" segment of the URI in the anchor tag, e.g. <a href='example.com/suppliers/view/K'> which would call suppliers->view("K").

Supplier_model.php

class Supplier_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    function getCity()
    {
        $query = $this->db->query('SELECT cityidd,city FROM citys');
        return $query->result_array();
    }

You never check if you actually get data back from the query which can bite you at times. But if you are not going to check if you got any rows you don't really need $query. The function could be simplified into this.

    function getCity()
    {
        return $this->db->query('SELECT cityidd,city FROM citys')->result_array();
    }

Getting the list of suppliers can be simplified into one function that will handle both sorted (a.k.a. filtered) and "All" records.

(Filtering and sorting database results are two different but related chores in my mind. I had to name the search variable $filter because I kept getting confused about what I was working on.)

This code illustrates one of the cool things about Query Builder - you don't have to run the various function in the order required for a valid SQL statement. Query Builder doesn't actual "build" the statement until get() is called so there is no problem with setting the like phrase before select or any other Query Builder method.

    public function get_suppliers($filter)
    {
        if(isset($filter))
        {
        // $filter is not NULL 
            $this->db->like('supplier', $filter, 'after');
            // Uncomment the next line of code and remove the other call 
            // to order_by if you only want filtered results sorted
            //$this->db->order_by("supplier", "ASC");
        }

        $query = $this->db
                ->select("supplier,contact,telephone,email")
                ->from('suppliers')
                // Remove (or comment) the next line of code
                // if you do not want the suppliers in alphabetical order.
                ->order_by("supplier", "ASC")
                ->get();

        return $query->num_rows() > 0 ? $query->result() : NULL;
    }

If you're not familiar with PHP's ternary operator this line

return $query->num_rows() > 0 ? $query->result() : NULL;

does exactly the same thing as this if/else block.

if($query->num_rows() > 0)
{
    return $query->result();
}
else
{
    return NULL;
}

Which would you rather type?

(If you know what the ternary operator is I apologize for being pedantic.)

This next class function is included because I am once again being pedantic.

There is no point in assigning posted values to variables and then using those variables to set values in an array. Save a lot of typing and processing power by using $this->input->post while populating the $data array.

    function addSupplier()
    {
        //$this->load->database(); do this in the constructor (or in autoload.php)

        $data = array(
                'supplier' => $this->input->post('supplier'),
                'taxpin' => $this->input->post('taxpin'),
                'contact' => $this->input->post('contact'),
                'addresss' => $this->input->post('addresss'),
                'citys' => $this->input->post('citys'),
                'telephone' => $this->input->post('telephone'),
                'email' => $this->input->post('email'),
                'website' => $this->input->post('website'),
                'paymentmodes' => $this->input->post('paymentmodes'),
                'kesaccount' => $this->input->post('kesaccount'),
                'usdaccount' => $this->input->post('usdaccount'),
                'banks' => $this->input->post('banks'),
                'bankbranches' => $this->input->post('bankbranches'),
                'bankcode' => $this->input->post('bankcode'),
                'swiftcode' => $this->input->post('swiftcode'),
                'mobilepaymentnumber' => $this->input->post('mobilepaymentnumber'),
                'mobilepaymentname' => $this->input->post('mobilepaymentname'),
                'chequeddressee' => $this->input->post('chequeddressee'),
                'status' => $this->input->post('status'),
                'categorysuppliers' => $this->input->post('categorysuppliers'),
                'staff' => $this->input->post('staff')
        );

        $this->db->insert('suppliers', $data);
    }

}

Here's the <body> section of the view file modified to use the variables passed in the controller. A foreach loop makes building the filter links easy. Documentation on the anchor function HERE.

suppliers_view.php

<body>
    <strong>Suppliers</strong><br><br>
    <strong>Suppliers</strong><br><br>
    <!-- These next two lines would cause the list to be suppliers that start with "Y". What do you really want? These links wouldn't work anyway. -->
    <!-- <a href="sort.php?let=Y"> Active </a> |-->
    <!-- <a href="sort.php?let=Y"> Disabled </a> <br><br>-->

    <?php
    $filter_values = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
            'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
    echo anchor("suppliers/view", "All");
    //make <a> tags for "A" through "Z"
    foreach($filter_values as $value)
    {
        echo anchor("suppliers/view/{$value}", " | $value");
    }
    ?>
    <br><br>
    <hr>
    <table>

        <thead>
            <tr>
                <th align='left'>Supplier</th>
                <th align='left'>Contact</th>
                <th align='left'>Telephone</th>
                <th align='left'>Email</th>
                <th align='left'>LPO</th>
                <th align='left'>Invoice</th>
                <th align='left'>Profile</th>
            </tr>
        </thead>

        <?php
        if(isset($suppliers)):
            foreach($suppliers as $supplier):
                ?>
                <tr>
                    <td><?= $supplier->supplier; ?></td>
                    <td><?= $supplier->contact; ?></td>
                    <td><?= $supplier->telephone; ?></td>
                    <td><?= $supplier->email; ?></td>
                </tr>
                <?php
            endforeach;
        else:
            $msg = isset($filter) ? "No supplier names starting with the letter $filter." : "No Suppliers found.";
            echo "<td>$msg</td>";
        endif;
        ?>
    </table>
</body>

Sorry for the long-winded answer. I hope it is helpful.