How to reset active record database query?

[eluser]ZachWills[/eluser]
As the title says! I run a query once in my function, then when I run it again but with a different where() parameter it outputs as though I had both parameters! I want it to reset... here is the example:

Code:

$this->CI->db->select('*')->from($this->_table_name)->where("which_reply", $row->id);

Code:

$replyQuery = $this->CI->db->get();

The above code is running through a loop so it gets used multiple times, each time it is supplied with a new $row->id value... but the output treats it as though I am compounding my where statement.

Does anybody have any clue as to how to reset the database query so I get the correct results?

[eluser]noTime[/eluser]
Your code simply keeps overwriting query clauses until the query gets sent ($this->CI->db->get()). You should do consecutive get() calls OR, if your $this->_table_name is the same everywhere, put all row IDs into an array and do a single get() call:

Code:

$this->CI->db->where_in('which_reply', $row_ids);
$this->CI->db->get('some_table');

[eluser]CroNiX[/eluser]
The query doesn't get reset until you db::get() the data. Until that point it is compiling all of the AR statements into a single query. So if you don't have a get() in your loop, you are just continually building upon the existing query until get() gets executed outside of the loop.

[eluser]ZachWills[/eluser]
Hmm.. I do have a get in my loop. Here is the long explanation of what I am trying to accomplish:

I have comments in a table. I loop through each comment, and if one of those comments has replies to it (threaded comments) I create a new query to get all comments that reply to that comment. I have a query within another query... here is the code for my query that runs if there are replies... I stripped out all of the unnecessary specifics:

Code:

//if comment has reply
if( $row->has_replies == 1 ){

//query to get all of the post replies for this id
$this->CI->db->select('*')->from($this->_table_name)->where("which_reply", $row->id);
//actual command that gets it
$replyQuery = $this->CI->db->get();

//loop through each reply
foreach( $replyQuery->result() as $reply_row ){

  //create an array containing all the replies

}


//create a comment array and include the reply array if the comment has a reply


//append the comment array ^^ to my result

}

This function returns an array of all the comments and their replies.

I took out a ton of the specifics like the arrays themselves and what they contain because I want to get this as simple as possible so I don't waste your time if you are kind enough to take a look and help me out!

[eluser]john_j[/eluser]
http://ellislab.com/forums/viewthread/212023/#983295

[eluser]ZachWills[/eluser]
I eagerly checked that link, but none of those suggested solutions. If I'm not mistaken, they didn't help that guy either... he had a completely different problem in the end (he was misinformed as to how to write the function)

I am still suffering from compounding where queries... they end up doing something like

Code:

SELECT * FROM table_name WHERE id = 1 OR WHERE id = 2 //OR WHERE all the other previous queries

I don't know how to see what the active record is writing, but from the results I am getting it has to be doing something like the above.

I want it to only have 1 where statement for each query.. but it seems through each loop it just adds and adds where clauses.

[eluser]CroNiX[/eluser]

Code:

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

[eluser]ZachWills[/eluser]
Well- now I feel stupid. I wonder how many problems come up on these boards where the OP determined it was definitely somethings fault when in the end it was something unrelated!

In my case- this had absolutely nothing to do with the query compounding! The query was working perfectly fine and had no reason to be reset... that is why all of the different techniques to reset the query did not work! The problem was that an array that I kept adding to was compounding... I unset() the array and poof! Problem solved.

Many thanks to those who took time out to help me! :-)