#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    11
    Rep Power
    0

    Why more than one connection is creating


    Below are some file structure of my website. I have removed unnecessary codes from these example codes in order to avoid confusions and larger code chunk.

    class: connection.php (collected from internet)

    PHP Code:
       <?php
        
    class connection {
            private 
    $_connection;
            private static 
    $_instance//The single instance
            
    private $_host "localhost";
            private 
    $_username "username";
            private 
    $_password "password";
            private 
    $_database "database";
        
            
    /*
            Get an instance of the Database
            @return Instance
            */
            
    public static function getInstance() {
                if(!
    self::$_instance) { // If no instance then make one
                    
    self::$_instance = new self();
                }
                return 
    self::$_instance;
            }
        
            
    // Constructor
            
    private function __construct() {
                
    $this->_connection = new mysqli($this->_host$this->_username,
                    
    $this->_password$this->_database);
                }
        
            
    // Magic method clone is empty to prevent duplication of connection
            
    private function __clone() { }
        
            
    // Get mysqli connection
            
    public function getConnection() {
                return 
    $this->_connection;
            }
        }
        
    ?>
    class: login.php (this class authorize users in the website)

    PHP Code:
      class login extends connection{
        private 
    $conn;
        private 
    $userid;
        public 
    $loggedin;
        
        function 
    __construct($id){
            
    $this->userid $id;
            
    $this->set_login();
        }
        
        private function 
    set_login(){
            
    $this->connect();
            
    $result $this->conn->query("SELECT status FROM users WHERE userid='".$this->userid.'"');
            if(
    $result){
               
    $this->loggedin true;
            }else{
               
    $this->loggedin false;
            }
        }
        
        private function 
    connect(){
            
    $this->conn parent::getInstance()->getConnection();
        }
        } 
    phppage: index.php (this page is for testing number of connection)

    PHP Code:
      <?php
        spl_autoload_register
    (function ($class) {
            include 
    '../classes/' $class '.php';
        });
        
    $user = new login("75");
        if(
    $user->logged_in){
            
    $db connection::getInstance();
            
    $conn $db->getConnection();
            
    $query 'SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections"';
            
    $result $conn->query($query);
            while(
    $row=$result->fetch_assoc()){
              echo 
    $row['Variable_name'].'  -  '.$row['Value'].'<br />';
            }
        }
        
    ?>
    the index.php will show the following information about connection:

    Code:
       Connections - 1026572
        Threads_cached - 7
        Threads_connected - 9
        Threads_created - 42943
        Threads_running - 2
    I am referencing two `$conn` in the above examples, one is inside `login.php` class and the another is in `index.php` page, but from the same `connection.php` class.

    I can know the total active connection from `Threads_running` value. But sometimes I can see that the `Threads_running - 2` and sometimes it becomes `Threads_running - 1` while I reload the page.

    So, my question to expert is, according to the classes above, is there any possibility to have more than one database connection at a time?

    If no, why it is showing `Threads_running - 2` sometimes?
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,406
    Rep Power
    24
    You can have numerous database connections.

    A reason to hold more than one may be due to an internal request within a loop for data an outer portion has fetched. I'm not sure if I would say one way is better than the other. This is probably more of a preference type option to the scripter.

    I personally prefer to stick with 1 at a time, and if I do need to build 'internal' data for items, I will do it 1 of 2 ways.
    1.) JOIN the multiple tables to run the desired request in more a single shot.
    2.) Run my initial request, building a valid PHP array to store the data, and add the internal request's results to that array appropriately.

    EDIT: Since I think this question is assuming the STATUS fetch is in reference to simply that request, I just want to note that this is the status of the overall server, and not just your script. I also noticed you are not ending/closing your connections at all, so they will remain open, even though they are done running. Unless it was just removed to keep your posted code short, try to close/end your connections when your done with them.
    Last edited by Triple_Nothing; December 26th, 2016 at 07:31 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo