mysql_fetch_assoc while loop issue

[dabro]

I am having a very strange issue with looping through a record set using a while loop and mysql_fetch_assoc. Basically, only first row of the data set is returned, after which nothing else happens as if it has got to the end of the data set (which I know is false, because the query generates more results).

I have run the query using an SQL browser with the database and it returns the expected results (3 in this case to be exact). This is also corroburated by the fact that I can call mysql_fetch_rows on the resource and correctly get the 3 rows returned as the number of results. However, mysql_fetch_assoc in PHP is only ever giving me the first result as described above.

This is the MYSQL class I am using:

<?php
class Mysql extends Database {
private $connection;
private $schema;
private $query;
private $reservedFunctions;

public function __construct($id) {
$this->reservedFunctions = array(
"now()"
);

$config = Configuration::getInstance();

if(isset($id)) {
$host = $config->configuration->database->$id->host;
$port = $config->configuration->database->$id->port;
$user = $config->configuration->database->$id->user;
$password = $config->configuration->database->$id->password;
$db = $config->configuration->database->$id->schema;
} else {
$host = $config->configuration->database->default->host;
$port = $config->configuration->database->default->port;
$user = $config->configuration->database->default->user;
$password = $config->configuration->database->default->password;
$db = $config->configuration->database->default->schema;
}

if(!$this->connection = mysql_connect($host.":".$port, $user, $password)) exit(mysql_error());
if(!$this->schema = mysql_select_db($db, $this->connection)) exit(mysql_error());
}

public function delete($table, $where) {
if(!is_array($where)) throw new Exception("Where clauses needs to be an array");

$query = "DELETE FROM $table";
$query .= " WHERE ";
$query .= join(" AND ", $where);

if(!mysql_query($query, $this->connection)) throw new Exception(mysql_error());

return true;
}

public function fetchResults($ref) {
if(!isset($this->query[$ref])) throw new Exception("No query exists with reference #$ref");

return mysql_fetch_assoc($this->query[$ref]);
}

public function select($ref, $table, $fields = null, $where = null, $order = null, $limit = null, $debug = null) {
if(isset($this->query[$ref])) throw new Exception("A query using reference #$ref already exists");

$query = "SELECT ";

if(isset($fields)) {
if(!is_array($fields)) throw new Exception("Fields clauses need to be an array");
$query .= join(",", $fields);
} else {
$query .= "*";
}

$query .= " FROM ".$table;

if(isset($where)) {
if(!is_array($where)) throw new Exception("Where clauses needs to be an array");
$query .= " WHERE ";
$query .= join(" AND ", $where);
}

if(isset($order)) {
if(!is_array($order)) throw new Exception("Order clause needs to be an array");
$query .= " ORDER BY ";
$query .= join(",", $order);
}

if(isset($limit)) {
$query .= " LIMIT ".$limit;
}

if($debug) var_dump($query);

if(!$this->query[$ref] = mysql_query($query, $this->connection)) throw new Exception(mysql_error());

return true;
}

public function save($id = null, $table, $fields) {
if(is_null($id)) {
$this->insert($table, $fields);
} else {
$this->update($table, $fields, $id);
}
}

public function insert($table, $data) {
foreach($data as $field => $value) {
$fields[] = $field;
$values[] = in_array($value, $this->reservedFunctions) ? $value : "\"".Functions::escape($value)."\"";
}

$query = "INSERT INTO $table (".join(", ", $fields).") ";
$query .= "VALUES (".join(",", $values).")";

if(!mysql_query($query, $this->connection)) throw new Exception(mysql_error());

return true;
}

public function update($table, $values, $conditions) {
$query = "UPDATE $table SET ";
foreach($values as $field => $value) {
if(!eregi("^[0-9]+$", $field)) {
if(in_array($value, $this->reservedFunctions)) {
$val = $value;
} else {
$val = "\"".Functions::escape($value)."\"";
}

$query .= "$field = $val, ";
}
}

$query = substr($query, 0, -2);
if(is_array($conditions)) $query .= " WHERE ".join(" AND ", $conditions);

if(!mysql_query($query, $this->connection)) throw new Exception(mysql_error());

return true;
}

public function query($ref, $query) {
if(!$this->query[$ref] = mysql_query($query, $this->connection)) throw new Exception(mysql_error());

return true;
}

public function countResults($ref) {
if(!isset($this->query[$ref])) throw new Exception("No query exists with reference #$ref");

return mysql_num_rows($this->query[$ref]);
}

public function freeResults($ref = null) {
if(isset($ref)) {
$this->query[$ref] = null;
} else {
foreach($this->query as $ref) {
$this->query[$ref] = null;
}
}
}

public function getLastId() {
return mysql_insert_id($this->connection);
}
}
?>

And here is the class using the while loop:

<?php
class PermissionCollection extends Collection {
public function __construct(&$parentRef, $protected = null) {
$this->initialise($parentRef, $protected);
$this->singleton = "Permission";
$this->table = "permissions";
$this->callBackVar = "permissions";
}

public function loadPermissionsForAdmin($userID) {
$db = Database::getInstance();

$db->freeResults(0);
$db->select(0, "administratorpermissions", null, array("administratorid = $userID"), array("id ASC"));
var_dump("Rows: ".$db->countResults(0)."<br>");
while ($row = $db->fetchResults(0)) {
$this->addObject($row["permissionid"]);$count++;
}var_dump("Loops: $count");
}

public function checkPermission($code) {
if ($this->getObjectCount() > 0) {
foreach ($this->objects as $permission) {
if ($permission->getField("code") == $code) return true;
}
}

return false;
}
}
?>

I have left my debugging var_dumps in, so its easier to see the affected section. The first dump, returning the rows, comes out as 3 (correct). But the secound dump (loop iterations) comes out as 1 (incorrect).

I have been up all night with this one and am completely stumped. Hopefully, I have missed somethign really obvious through being tired and someone will point it out straight away.

If you need any more information, just let me know.

[Inigoesdr]

Initialize $count to 0 in case you're using it somewhere else in the scope. Are the objects in the loop being added? Try placing an echo in the loop to make sure each iteration is visible.

[psykx]

you need a == rather than a = to test in your while loop

[dabro]

I have set the counter to zero to be sure, also the var is only available inside that function. I have also tried == instead of = which results in zero loops.

[dabro]

Initialize $count to 0 in case you're using it somewhere else in the scope. Are the objects in the loop being added? Try placing an echo in the loop to make sure each iteration is visible.

The object is being added, yes. Everything works, just that mysql_fetch_assoc is returning false after the first loop as if there was only 1 row in the record set.

[Inigoesdr]

you need a == rather than a = to test in your while loop
Not in this case. He's not trying to compare the variables, he's assigning the return value from the function to $row. See the examples in the manual page (http://php.net/mysql_fetch_assoc).

[dabro]

Out of curiosity, I gave the readdir style loop syntax a whirl:

false !== ($row = $db->fetchResults(0))

Unfortunately, it results in the exact same scenario, only 1 loop iteration. This is such a bizarre problem, its almost as if there is a bug with mysql_fetch_assoc. I have tried mysql_fetch_array too, which results in the same thing. My gut feeling is that there is something wrong with the implementation of storing and calling the mysql results in the mysql class. But then again, its a pretty basic system and nothing jumps out as obvious.

[dabro]

I have solved the problem now, without knowing exactly what is happening.

As a long shot, I changed the query reference from 0 to 1 (so far everything is running off reference 0 - which I use for all queries done from abse classes) and it works now.

It would appear to be an issue where freeing up results is not resetting the data pointer.

[aWishResigned]

Not too sure if this will help you but it might help someone. Here's a cutout from a sql class I built a few months back, the function returns a result set as an array of associative arrays.

The code:
public function fetchAssoc($sql) {
$arResult = array();

$this->executeQuery($sql);
$this->setNumRows(mysql_num_rows($this->getResult()));

if (is_resource($this->getResult())) {
while ($row = mysql_fetch_assoc($this->getResult())) {
$arResult[] = $row;
}
return $arResult;
} else {
return false;
}
}

And an example:

<?php
include("class.sql.php");
$sql = new sql("connect", "to", "database", "info");

$widgets = $sql->fetchAssoc("SELECT * FROM `widgets`");

foreach ($widgets as $widget) {
echo $widget['id'] . "<br />";
}
?>

It makes the whole process of manipulating data a lot easier to code and read after the fact.