CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Jc John
Jc John

Reputation: 1859

Error in my query getting the maximum value of a varchar

i am using Codeigniter framework, and i dont know why i got error in my query. When i tried it in query builder navicat, my query runs successfully and returns the maximum number of my field varchar. But when i tried it in my model, it gives me error of :

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1

Here is my query in model : 

public function checkupID() {
        $query = $this->db->query(' SELECT tbl_check_up.check_up_id FROM tbl_check_up ORDER BY substring_index(tbl_check_up.check_up_id, '-', 1) + 0, 
                    substring_index(tbl_check_up.check_up_id, '-', -1) + 0 DESC LIMIT 1 ');
        return $query->result();
    }

Upvotes: 0

Views: 103

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Perhaps you are looking for the length of the first substring (prior to a dash) as the way to order?

SELECT tbl_check_up.check_up_id 
FROM tbl_check_up 
ORDER BY
  length(substring_index(tbl_check_up.check_up_id, '-', 1)) 
LIMIT 1
;

However I am not sure what was intended for the second part of the order by.

+EDIT, correction. -1 is valid as third parameter to substring_index

Upvotes: 1

Related Questions