fixing the error in query codeigniter model syntax error

Question

Why is this i am getting error in my Model in codeigniter where it says :

A Database Error Occured
Error Number: 1064 
You have error in your SQL syntax;

when i tried run it in mysql it works fine. But when i put it in the model of my CI it gives me Error Number: 1064

Here is my full model code:

public function checkupID() {
        $query = $this->db->query(' SELECT check_up_id FROM tbl_check_up JOIN (SELECT MAX(CAST(SUBSTRING_INDEX(check_up_id, '-', -1) AS DECIMAL)) AS max_right FROM tbl_check_up) AS x
                    ON SUBSTRING_INDEX(check_up_id, '-', -1) = max_right ');
        return $query->result();
    }

my table structure

+-------------+----------+---------+--------+
|  Field      |  Type    |  NULL   | Key    |
+-------------+----------+---------+--------+
| check_up_id | varchar  |  NO     | PRI    |
+-------------+----------+---------+--------+
| note        | varchar  |  YES    |        |
+-------------+----------+---------+--------+

Can you please add x.max_right at the end instead of max_right — Alex Mac
– Alex Mac, Commented Jun 27, 2017 at 5:00
@ShyamShingadiya THANK YOU for your reply sir.. . Why is it , that it appears error in codeigniter while in mysql works fine? — Jc John
– Jc John, Commented Jun 27, 2017 at 5:29
@ShyamShingadiya thanks for fast reply sir. In CI it doesn't work and alerting an error of A Database Error Occured, it says check the syntax. but in mysql . it works fine. — Jc John
– Jc John, Commented Jun 27, 2017 at 5:32
Let me gives you proper solution for this, Can you share your table schema for tbl_check_up? — Alex Mac
– Alex Mac, Commented Jun 27, 2017 at 5:34

Alex Mac · Accepted Answer · 2017-06-27 06:17:29Z

1

Please find my answer describe below. You can create stored procedure in mysql like give below.

DELIMITER $$

DROP PROCEDURE IF EXISTS `getCheckUpId`$$

CREATE PROCEDURE `getCheckUpId`()
BEGIN
    SELECT 
        t1.check_up_id 
    FROM tbl_check_up t1 
    JOIN (SELECT MAX(CAST(SUBSTRING_INDEX(t2.check_up_id, '-', -1) AS DECIMAL)) AS max_right FROM tbl_check_up t2) AS x ON SUBSTRING_INDEX(t1.check_up_id, '-', -1) = x.max_right;
    END$$

DELIMITER ;

Now in your model you can create like described below.

public function checkupID() {
    $query = $this->db->query('call getCheckUpId()');
    return $query->row();
}

I hope this will help you. Let me know if it not works.

answered Jun 27, 2017 at 6:17

Alex Mac

2,9931 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Mac Over a year ago

@JcJohn : Glad to help you out from this hurdle,

438sunil Over a year ago

Why you need to write procedure? you can simple put Douable Quations to the query. $query = $this->db->query(" SELECT check_up_id FROM tbl_check_up JOIN (SELECT MAX(CAST(SUBSTRING_INDEX(check_up_id, '-', -1) AS DECIMAL)) AS max_right FROM tbl_check_up) AS x ON SUBSTRING_INDEX(check_up_id, '-', -1) = max_right ");

Alex Mac Over a year ago

@438sunil : Do you think with double quotes (") this can be resolved..?

Collectives™ on Stack Overflow

fixing the error in query codeigniter model syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related