结果在DB :: getInstance中重复

Question

I am new to php and I am trying to develop a class. I am connected to sqlsrv and the connection is fine. Its when I run the query the results are repeating almost like it's in a loop. I can not find the problem and I was hoping an extra pair of eyes can help. Any help is appreciated!

class DB{
    private static $_instance = null;
    private $_connection, 
            $_query, 
            $_error = false, 
            $_results, 
            $_count = 0;

private function __construct(){
    $mssqlInfo = array( "Database"=>"db");  
    $mssqlServer = "server";
    $this->_connection = sqlsrv_connect($mssqlServer, $mssqlInfo); 
    if($this->_connection === false ) {  
     echo '<h2>Unable to connect to database</h2><br/>'; 
        die( print_r(sqlsrv_errors(), true)); 
    }else{
        //echo 'Connected';
    }
}

    public static function getInstance(){
        if(!isset(self::$_instance)){
            self::$_instance = new DB();
        }
        return self::$_instance;
    }

    public function query($sql, $params = array()){ 
        $this->_error = false;
        $options = array( "Scrollable" => SQLSRV_CURSOR_STATIC );
        $this->_query = sqlsrv_query($this->_connection, $sql, $params,$options);
        if($this->_query){
            $this->_results = sqlsrv_fetch_object($this->_query);
            $this->_count = sqlsrv_num_rows($this->_query);
        }else{
            $this->_error = true;
            echo 'Error in statement execution.
'; 
            die( print_r( sqlsrv_errors(), true));

        }           
        return $this;          
    } 
public function action($action, $table, $where = array()){
    if(count($where) === 3){
        $operators = array('=', '>', '<', '>=', '<=');

        $field      = $where[0];
        $operator   = $where[1];
        $value      = $where[2];

        if(in_array($operator, $operators)){
            $sql = "{$action} FROM {$table} WHERE {$field} {$operator} (?)";

            if(!$this->query($sql, array($value))->error()){
                return $this;
            }
        }
    }
    return false;
}
public function get($table, $where){
    return $this->action('SELECT *', $table, $where);
}
public function delete($table, $where){
    return $this->action('DELETE', $table, $where);
}

public function results(){
    return $this->_results;
}

public function first(){
    return $this->results()[0];
}

public function error(){
    return $this->_error;
}

public function count(){
    return $this->_count;
}


 }

here is the instance on the index page that I am using to test my functions and to retrieve the query.

    require_once 'core/init.php';

    $users = DB::getInstance()->query("SELECT * FROM users");
    $user = DB::getInstance()->get('users', array('username', '=', 'mike'));

 if(!$user->count()){
    echo 'No user';
}else{
    $obj = $users->results();
    foreach($obj as $users){
        echo $users->username;
        echo '<br/>';
    }
}

I forgot to included the table.

CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [username] [varchar](20) NOT NULL,
    [password] [varchar](64) NOT NULL,
    [salt] [varchar](32) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [joined] [datetime] NOT NULL,
    [group] [int] NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Answer 1

You fetch only one row of the results in function query() and save it to member _results: $this->_results = sqlsrv_fetch_object($this->_query);

Then you use function results() in a while loop. results() returns an object saved in member _results which will never change. That's why you got an endless loop.

Solution for your problem: Change

public function query($sql, $params = array()){ 
        [...]
        $this->_query = sqlsrv_query($this->_connection, $sql, $params,$options);
        if($this->_query){
            $this->_results = sqlsrv_fetch_object($this->_query);
            $this->_count = sqlsrv_num_rows($this->_query);
        [...]

to

public function query($sql, $params = array()){ 
        [...]
        $this->_query = $sql;
        $this->_results = sqlsrv_query($this->_connection, $sql, $params,$options);
        if($this->_query){
            $this->_count = sqlsrv_num_rows($this->_query);
        [...]

And change

public function results(){
    return $this->_results;
}

to

public function results(){
    return sqlsrv_fetch_object($this->_results);
}