CODEWITHSUNDEEP
phpmysqlcodeigniter
hasan
hasan

Reputation: 27

sort varchar column codeigniter

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.

Upvotes: 0

Views: 2013

Answers (2)

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

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'];
}

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

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

Upvotes: 3

Related Questions