Jonas Jonas - 3 months ago 12
MySQL Question

How to ORDER BY 'name + 0' in codeIgniter (mysql)

I have a table in a MySQL database that looks like this:

id | name
1 | 1 some words
2 | 2 some other words
3 | 1.1 some other words
...
10 | 10 some other words


If I sort the table using following method:

$this->db->select('*')
->order_by('name', 'ASC')
->get('table_name');


I receive the table in following order:

id | name
1 | 1 some words
3 | 1.1 some other words
10 | 10 some other words
...
2 | 2 some other words


But I actually want to receive the table in this order:

id | name
1 | 1 some words
3 | 1.1 some other words
2 | 2 some other words
...
10 | 10 some other words


This is possible using following SQL statement:

SELECT * FROM database_name.table_name ORDER BY name + 0 ASC;


But if I use codeIgniters query builder like this I get a database error:

$this->db->select('*')
->order_by('name + 0', 'ASC')
->get('table_name');


Note that it is not possible in my situation to either store the numbers in a different column or order by id.

So is there a way to make this SQL statement work in CodeIgniters query builder?

SELECT * FROM database_name.table_name ORDER BY name + 0 ASC;


Thanks in advance

EDIT:
I am very sorry for the confusion but the '.' in 1.1 was not meant to be a floating point but as a dot like in: 1.1.1, 1.1.2, 1.1.3
I have found a solution using @Marc B solution and put it into the query builder like this:

$query = $this->db->select('name+0 AS name', FALSE)
->order_by('name', 'ASC')
->get('table_name');


Thank you all very much for your answers

Answer

Use a derived field and an alias?

SELECT name+0 AS fakename ...
ORDER BY fakename