MySQL Result Object as Class Member Variable??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
chopsmith
Forum Commoner
Posts: 56
Joined: Thu Nov 13, 2008 10:40 am
Location: Red Bank, NJ, USA

MySQL Result Object as Class Member Variable??

Post by chopsmith »

Hey, All. My first post:

require_once("class_db.php"); //class returning result object

class my_class {
var $connection;
var $sql;
var $sql_result;

function __construct() {
$this->connection = new db(); //in require_once statement above
$this->sql = "select * from my_table";
$this->execute_sql;
$this->use_result_A();
$this->use_result_B();
}

private function execute_sql() {
$this->sql_result = $this->connection->query_select($this->sql); //function in db class
}

private function use_result_A() {
while ($row = mysql_fetch_assoc($this->sql_result) {
echo "Hi";
}
}

private function use_result_B() {
while ($row = mysql_fetch_assoc($this->sql_result) {
echo "Hi";
}
}
}

Now, the question. I've written something like the above. use_result_A() works as expected. use_result_B() will never say "Hi". In order to get use_result_B() to say "Hi" the correct number of times (indeed, at all), I must re-perform $this->execute_sql(). Why is that? Shouldn't the MySQL result object persist? In fact, if I just put print_r($this->sql_result) at the beginning of use_result_B(), it prints something like "Resource ID #8". And if I put get_resource_type($this->sql_result), it prints "mysql_result". So, the object does still exist and it is a mysql result object, but it seems as though the object has been de-instantiated or something. All the rows are gone. Does this have to do with garbage collection? If so, how to I tell PHP not to perform collection on this variable?

Thanks in advance for any comments.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL Result Object as Class Member Variable??

Post by Eran »

When you iterated over the returned result resource, it reached the end of the buffer, terminating the loop. The next time you want to iterate over it, it is still at the end of the buffer.
You can use mysql_data_seek to move the buffer back to the beginning:

Code: Select all

 
mysql_data_seek  ($this -> sql_result,0);
 
User avatar
chopsmith
Forum Commoner
Posts: 56
Joined: Thu Nov 13, 2008 10:40 am
Location: Red Bank, NJ, USA

Re: MySQL Result Object as Class Member Variable??

Post by chopsmith »

Thanks, pytrin. I assume this is more efficient that re-executing the query. It's just weird. I could have sworn that, in the past, I iterated multiple times over the same result resource in procedural code without a problem. Perhaps I'm remembering incorrectly? Again, thanks for the help.
Post Reply