CODEWITHSUNDEEP
phpmysqlcodeignitersubqueryexists
Nacho
Nacho

Reputation: 2057

CodeIgniter query builder syntax for WHERE EXISTS (subquery)

I'm trying to do a subquery with

$this->db->where(" EXISTS (SELECT * FROM myTable)");

But it doesnt work, the output of this is: myquery + WHERE 'EXISTS (SELECT * FROM myTable);

That quote before the EXISTS makes the query unresolvable!

Does anyone knows how to solve it?

Upvotes: 2

Views: 7194

Answers (4)

mickmackusa
mickmackusa

Reputation: 47992

To avoid writing raw SQL and to ensure escaping/quoting in your subquery, use get_compiled_select() to render the subquery. I assume that you'll have more logic in your subquery, but for the asked question you can pass the table name into get_compiled_select() and SELECT * will be the default SELECT clause applied.

Once the subquery is safely rendered, you just turn off the escaping in its parent WHERE clause by passing false as the third parameter of where().

$this->db->where('EXISTS (' . $this->db->get_compiled_select('myTable') . ')', null, false);

This WHERE clause of your parent query will be rendered as:

WHERE EXISTS (SELECT * FROM `myTable`)

Upvotes: 0

adesst
adesst

Reputation: 307

Maybe you could try to set the escape to false by using

$this->db->where(" EXISTS (SELECT * FROM myTable)", null, false);

This is the snippet of where() in DB_active_rec.php

public function where($key, $value = NULL, $escape = TRUE)

Upvotes: 4

Kack
Kack

Reputation: 86

please remove space before and after EXISTS keyword.that does not display any error.

$this->db->where("EXISTS(SELECT * FROM myTable)");

Upvotes: 6

g4GG
g4GG

Reputation: 11

Just try this.

Instead of using 'where' clause, please write down the complete query string & execute the query using $this->db->query();

    $qry_string= $yourquery . "WHERE EXISTS (SELECT * FROM myTable)";
    $this->db->query($qry_string);

Upvotes: 1

Related Questions