Codeigniter and MYSQL - Query using CASE WHEN returning different result

Asked

Viewed 320 times

1

I’m using the Code Igniter 3 and I’m doing a database query with the following code in the file Model:

        $this->db->select("*");
                $this->db->where("CASE WHEN code = '001' THEN '1'
                                    WHEN code = '002' THEN '2'
                                    WHEN code = '003' THEN '3'
                                    WHEN code = '004' THEN '4'
                                    WHEN code = '005' THEN '5'
                                    ELSE title END",FALSE);
                $this->db->from("lik");
                $query = $this->db->get();

It returns only the data I put in CASE WHEN but does not return the rest of the table. I need you to return all the results and in 1 those 5 that are in the CASE WHEN.

php mysql codeigniter

  • "It returns only the data I put in CASE WHEN but does not return the rest of the table", this rest of the table you speak are the other columns of the table?

  • 4

    You’re not doing it wrong? Shouldn’t it be on SELECT?

  • Exact André Felipe, I have a table and this table returns only the data from CASE WHEN but does not return the rest of the table data

1 answer

0

The method where() of Query Builder Class is used to work with the clause WHERE of SQL.

According to the documentation:

This Function Enables you to set WHERE clauses using one of four methods

Already the command WHEN is a statement and not a clause.

I tested your code here and the result of SQL compiled using the method get_compiled_select() was exactly that (I just indented to improve the reading):

SELECT *
FROM `lik`
WHERE
    CASE WHEN code = '001' THEN '1'
         WHEN code = '002' THEN '2'
         WHEN code = '003' THEN '3'
         WHEN code = '004' THEN '4'
         WHEN code = '005' THEN '5'
         ELSE title
    END 0

And just as @Virgilionovic commented, the CASE should be next to the SELECT. So do something like this:

$this->db->select('*, CASE WHEN `code` = '001' THEN '1'
                           WHEN `code` = '002' THEN '2'
                           WHEN `code` = '003' THEN '3'
                           WHEN `code` = '004' THEN '4'
                           WHEN `code` = '005' THEN '5'
                           ELSE `title`
                      END');
$this->db->from('lik');

And the compiled result will be:

SELECT *,
    CASE WHEN `code` = '001' THEN '1'
         WHEN `code` = '002' THEN '2'
         WHEN `code` = '003' THEN '3'
         WHEN `code` = '004' THEN '4'
         WHEN `code` = '005' THEN '5'
         ELSE `title`
    END       
FROM `lik`;

I hope I’ve helped.

Browser other questions tagged php mysql codeigniter

You are not signed in. Login or sign up in order to post.