Issue with custom database wrapper class

[sagnik]

Hi! I'm trying to create a wrapper class for database operations but the problem is when I'm trying to call the fetch_object() function after a subquery it breaks the while() loop, you can check the codes below:

 

public function query(string $sql = null, ?array $params = null, bool $return_result = false): bool|mysqli_result|mysqli_stmt|Result|PDOStatement|static {
    $return_result = $return_result ?? $this->return_result;
    $this->sql = $sql ?: $this->queryBuilder->build();

    // Generate a unique query ID to track different queries
    $queryKey = md5($this->sql);

    try {
        switch ($this->mode) {
            case SGNDatabaseMode::NATIVE:
                if ($this->driver == SGNDatabaseDriver::MYSQL) {
                    $this->results[$queryKey] = mysqli_query($this->con, $this->sql);
                } elseif ($this->driver == SGNDatabaseDriver::PGSQL) {
                    $this->results[$queryKey] = pg_query($this->con, $this->sql);
                } else {
                    $this->results[$queryKey] = db_query($this->sql, $this->con);
                }
                break;

            case SGNDatabaseMode::PDO:
                if ($this->driver instanceof SGNDatabaseDriver) {
                    $stmt = preg_match('/SELECT/i', $sql) ? $this->con->query($sql) : $this->con->prepare($sql);
                    $this->results[$queryKey] = $stmt;
                } else {
                    $this->results[$queryKey] = db_query($this->sql, $this->con);
                }
                break;

            default:
                $this->results[$queryKey] = db_query($this->sql, $this->con);
        }

        // Reset result pointer for repeated fetches
        if ($this->results[$queryKey] instanceof mysqli_result || $this->results[$queryKey] instanceof PDOStatement) {
            $this->result = $this->results[$queryKey]; // Update result pointer
        }

        $this->queryStatus = (bool)$this->results[$queryKey];
    } catch (Exception $e) {
        $this->queryStatus = false;
    }

    return $return_result ? $this->results[$queryKey] : $this;
}


public function fetch_object(string $class = 'stdClass', array $constructor_args = []): bool|null|object|string {
    try {
        // Get the unique query key for the current query
        $queryKey = md5($this->sql);  // Unique key for the current query

        // Check if the results for this query exist
        if (!isset($this->results[$queryKey])) {
            return false;
        }

        $result = $this->results[$queryKey];

        // Fetch object based on the query mode
        switch ($this->mode) {
            case SGNDatabaseMode::NATIVE:
                if ($this->driver == SGNDatabaseDriver::MYSQL && $result instanceof mysqli_result) {
                    return mysqli_fetch_object($result, $class, $constructor_args);
                } elseif ($this->driver == SGNDatabaseDriver::PGSQL && $result instanceof Result) {
                    return pg_fetch_object($result, null, $class, $constructor_args);
                }
                break;

            case SGNDatabaseMode::PDO:
                if ($this->driver instanceof SGNDatabaseDriver && $result instanceof PDOStatement) {
                    return $result->fetchObject($class, $constructor_args);
                }
                break;

            default:
                if ($result instanceof mysqli_result) {
                    return db_fetch_object($result, $class, $constructor_args);
                }
        }
    } catch (Exception $e) {
        // Handle exceptions if needed
    }

    return false;
}

 

The codes are from where the above functions are called. After calling the getCategory() from getProperties(), it breaks the while loop in getProperties(), resulting in only one iteration of the loop, while I have more than one row in the table 'properties'. Can anyone help me to address the issue?

function getProperties() {
    $s = "SELECT * FROM properties";
    $this->fpdb->query($s);  // Execute the main query

    $result = [];

    // Loop through all rows in the result set
    while ($r = $this->fpdb->fetch_object(Property::class)) {
        // Get the category for each property (this will be a subquery)
        $r->category = $this->getCategory($r->cid);
        $result[] = $r;  // Add to the result array
    }

    return $result;
}

function getCategory(int $id, string $get = null) {
    $s = "SELECT * FROM categories WHERE id='$id'";
    $this->fpdb->query($s);  // Execute the subquery
    $r = $this->fpdb->fetch_object(Category::class);  // Fetch the category object

    // Return the category or specific field based on the `$get` argument
    return ($get != '*') ? $r->$get : $r;
}