CODEWITHSUNDEEP
phpcodeigniter
Adria Ciurana
Adria Ciurana

Reputation: 934

Multiple query at "same time" in CodeIgniter

I'm trying to make multiple queries at the same time, these queries are being built in pieces (I can not change that), the problem is that codeigniter remembers the status and tries to build a single query.

The problem is the following:

$queryA = $this->db->select("SELECT fieldA")
->from("tableA");

$queryB = $this->db->select("SELECT fieldB")
->from("tableB");

$queryC = $this->db->select("SELECT fieldC")
->from("tableC");

/* Group bys */
$queryA->group_by("groupbyfieldA");
$queryB->group_by("groupbyfieldB");

/* Limits */
$queryB->limit(10);
$queryC->limit(30);

$rA = $queryA->get()->result();
$rB = $queryB->get()->result();
$rC = $queryC->get()->result();


/* Desired output */
SELECT fieldA FROM tableA GROUP BY groupbyfieldA
SELECT fieldB FROM tableB GROUP BY groupbyfieldB LIMIT 10
SELECT fieldC FROM tableC LIMIT 30

/* CodeIgniter output :( */
SELECT fieldA, fieldB, fieldC FROM (tableA, tableB, tableC) GROUP BY groupbyfieldA, groupbyfieldB LIMIT ?

The only solution I have found is to change the flow of the creation of the queries but there must be a simpler solution provided by CodeIgniter. Can not avoid that $ this-> db remember the previous query and that $queryA, $queryB, $queryC are the only ones that save the information?

Thank you very much!

Upvotes: 2

Views: 1072

Answers (1)

Francisco Hahn
Francisco Hahn

Reputation: 435

Try it like this

 $resultA= $this->db->select("SELECT fieldA")
 ->from("tableA")->groupby()->get()->result();

 $resultB= $this->db->select("SELECT fieldB")
 ->from("tableB")->groupby()->get()->result();

 $resultC= $this->db->select("SELECT fieldC")
 ->from("tableC")->groupby()->get()->result();

The "problem" is the $this->db codeigniter uses everytime the same object, and anytime you are executing the query is taking all the values (every select(), groupby(), whatever()) on that query execution unless it's reseted via get().

One way to do it is executing each query, remember the method get() refreshes the object $this->db, and allows u to execute any other query you need (it also frees the statement and if i recall correctly it closes the db conection).

And well, in order to obtain the results just call result(),result_array() or row($someNumberForTheRow).

Also if you need to reset the query reset_query() can be used.

Reference

Upvotes: 2

Related Questions