Codeigniter - Implode-generated Query returns "Unknown column"

Question

I'm currently trying to write a query in codeigniter, I'm having a problem including and implode inside the query. This is my code.

$permitedCodeList = ["ELEC-22", "ELEC-100", "ELEC-200", "999873"];

$query = $this->inventario_db->query("
    SELECT *
    FROM pt_inv_num_id_equipo
    WHERE numID = '123456789' AND codigo IN ('" . implode("','", $permitedCodeList). "')
    LIMIT 1
");
$result = $query->row();

I'm getting the following error message: Unknown column 'ELEC' in 'where clause'. I know it has todo with the implode returning the data as ELEC-22,ELEC-100,ELEC-200,999873 but it doesn't seem to add a single quote to each value and include it in the query.

Any help is greatly appreciated.

I copy-pasted your code (just the part generating the SQL, not the query itself) and the generated SQL looks fine - no bust quotes, no truncated codigo values. Is there really nothing else to your code than what you've shown? — Don't Panic
– Don't Panic, Commented Oct 10, 2019 at 10:28

ascsoftw · Accepted Answer · 2019-10-10 05:26:18Z

1

It would be better if you could use CI Query Builder It specifically has where_in method to handle such scenario.

Your query can be constructed as below:

        $permitedCodeList = ["ELEC-22", "ELEC-100", "ELEC-200", "999873"];

        $this->db->from('pt_inv_num_id_equipo');
        $this->db->where('numID ', 1234 );
        $this->db->where_in('codigo', $permitedCodeList );
        $result = $this->db->get()->row_array();

You could also debug the last query using below code

echo $this->db->last_query(); die();

answered Oct 10, 2019 at 5:26

ascsoftw

3,4842 gold badges17 silver badges23 bronze badges

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

2 Comments

Brian Moreno Over a year ago

Thanks, yeah, I was thinking of using the CI Query Builder, but I wanted to see if I could get it working without the query builder. But I guess the best thing to do is use the builder in this scenario. Thanks for your answer, it worked. :)

ascsoftw Over a year ago

@BrianMoreno Glad it worked. Thanks for accepting the answer. Could you please also upvote the answer. Cheers!

Collectives™ on Stack Overflow

Codeigniter - Implode-generated Query returns "Unknown column"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related