sort varchar column codeigniter

Question

i first checked if there any same problems like mine i ddnt find anything. all are sorting alphanumeric column mixed with numeric data.

here is my problem. i have a table that contain column A datas like this.

WRG-01 WRG-39 WRG-22 WRG-45 WRG-43

need to sort that as

WRG-01 WRG-22 WRG-39 WRG-43 WRG-45

this is the code i using so far in codeigniter frame work

$data['products'] = $this->db->order_by('product_id', 'asc')->get('products');

in mysql i can use this query to get done my work

preg_replace("/[^\d]/", "",'product_id'), 'asc')

How to apply it to my above codeigniter code?

here is search funtion

public function search()
{
    $data['title'] = 'Search Product';
    $product_name       = $this->input->get('product_name');
    $product_id         = $this->input->get('product_id');
    $product_category   = $this->input->get('product_category');
    $secondCategory     = $this->input->get('secondCategory');
    $thirdCategory  = $this->input->get('thirdCategory');

$data['category'] = $this->db->order_by('id', 'asc')->get_where('categories', ['parent' => 0]);
if($product_category != '')
{
    $data['secondCategory'] = $this->db->get_where('categories', ['parent' => $product_category]);
}
if($secondCategory != '')
{
    $data['thirdCategory'] = $this->db->get_where('categories', ['parent' => $secondCategory]);
}


if($product_name != '')
    {
        $this->db->like('product_name', $product_name);
    }
    if($product_id != '')
    {
        $this->db->where('product_id', $product_id);
    }
    if($product_category != '')
    {
        $this->db->where('product_category', $product_category);
    }
    if($secondCategory != '')
    {
        $this->db->where('secondCategory', $secondCategory);
    }
    if($thirdCategory != '')
    {
        $this->db->where('thirdCategory', $thirdCategory);
    }



    $data['products'] = $this->db->order_by('product_id' 'asc')->get('products');


    theme('all_product', $data);

}

i can't use sql query here because products is result array from product table.

Have you tried $this->db->order_by(preg_replace("/[^\d]/", "",'product_id'), 'asc')->get('products'); — Jasbir Singh Sohanpal, Oct 30 '15 at 06:10
@JasbirSinghSohanpal thanks. but i added full search function for your consideration.. can't use sql query directly there. because it's data array containg search result. — hasan, Oct 30 '15 at 06:30

Abhishek Sharma · Accepted Answer · 2015-10-30T06:07:41.120

3

Use MySQL cast

cast(product_id as SIGNED)

or

cast(product_id as UNSIGNED)

Try query like that :-

select * from products cast(product_id as UNSIGNED) ASC|DESC

edited Oct 30 '15 at 06:07

answered Oct 30 '15 at 06:00

Abhishek Sharma

6,689
1
14
20

i tried it working on sql query and give the correct result. but how to implement that on my code. i gave full search function there. – hasan Oct 30 '15 at 06:31
$query = $this->db->query(" select * from products cast(product_id as UNSIGNED) ASC ") – Abhishek Sharma Oct 30 '15 at 06:34
use custom query http://stackoverflow.com/questions/16435390/how-to-execute-my-sql-query-in-codeigniter – Abhishek Sharma Oct 30 '15 at 06:34

Abdulla Nilam · Answer 2 · 2015-10-30T06:37:35.727

0

Try this

$query= $this->db->query("SELECT * FROM products WHERE ??==?? ORDER BY product_id ASC");
$result= $query->result_array();
return $result;

as default data will sort by Ascending Order

This is in model. So if you pass it to controller it will return data as Objective Array.

So in controller you can access

$result = $this->model_name->method_for_above_code();
$name = $result[0]['name'];
$id = $result[0]['id'];

if in View

$result['this_for_view'] = $this->model_name->method_for_above_code();
foreach ($this_for_view as $new_item) {
    echo "Name is ".$new_item['name'];
    echo "ID is ".$new_item['id'];
}

edited Oct 30 '15 at 06:37

answered Oct 30 '15 at 06:04

Abdulla Nilam

36,589
17
64
85

thank you. but there is another problem. products is result set data array. – hasan Oct 30 '15 at 06:22
ya its objective array. You know how to access it?? – Abdulla Nilam Oct 30 '15 at 06:26
@hasan Check **EDIT 01** – Abdulla Nilam Oct 30 '15 at 06:37

sort varchar column codeigniter

2 Answers2