Correct way to use mysql database in multiple php files

HI everyone, I was just wondering what the best way to make multiple queries against tables in a mysql databases is. Should I be making a new mysqli object for every different .php page ($mysqli = new mysqli("localhost", "root", "root", "db");)?

Or is there a way to reuse this开发者_如何学Go one time over all php files in my website? Any suggestions would be pretty cool

---

My vote would be to take an OOP approach. I would have one script that has a DB conn class in it and a method in that class to check if a connection exists and if it does returns the connection object. You could have that db class script referenced [ include_once(); ] on the pages that need to access the database. Then it would be a matter of instantiating the db object, firing the "if-exists" method and if it returns true then just utilize the existing connection within the object.

You could also take a look at utilizing persitent connections to the DB

Persistent connections

However honestly you will be better off in the long run and scalability of your application to handle the db connection management yourself rather then leaving a connection constantly open.

Here is an example of how I would structure that class:

As a note, made by @alex, the mysql_error() should not be echoed to the page in an environment where the display_errors() is set to display all warnings. (e.g error_reporting(E_WARNING);)

class dbconn {
protected $database;

function __construct(){
    $this->connect();
}

protected function connect() {
    $this->database = mysql_connect('host', 'user', 'pass')  or die("<p>Error connecting to the database<br /><strong>" . mysql_error() ."</strong></p>" );
    mysql_select_db('databasename') or die("<p>Error selecting the database<br />" . mysql_error() . "</strong></p>");
}

function __destruct(){
    mysql_close($this->database);
}

function db(){
     if (!isset($this->database)) { 
        $this->connect(); 
     }
     return $this->database;
  }

  }

---

You need to create the connection for each page, as each PHP script's lifetime is that of the request.

However, you can place the connection code in one file and then include it from all pages.

---

You could create a connect.php that validates it's being included by your application, and then creates a DB connection.

You could then include that file at the beginning of your application's init, or the beginning of any independent script that needs a connection =)

---

Depends on structure of website. If you have:

<a herf='login.php'>login</a>
<a herf='register.php'>register</a>
<a herf='about.php'>about</a>

..., then you'll have to connect in every PHP file, i.e., in login.php, in register.php and in about.php. To make it easier, I would either create config.php file which holds user/pass, or even do like Shad said.

---

You might also have index.php that contains something like this:

if ( !isset($_GET['module']) ) {
    $_GET['module'] = 'about';
}
switch ( $_GET['module'] ) {
    default:
    case 'about':
        include 'about.php';
        break;
    case 'login':
        include 'login.php';
        break;
    case 'register':
        include 'register.php';
        break;
}

And HTML code:

<a herf='?module=login'>login</a>
<a herf='?module=register'>register</a>
<a herf='?module=about'>about</a>

In this case you can connect in index.php and then pass the connection to all other involved files.

---

The 2nd way seems to be more common to me, i.e., it feels more intuitive, more handy and that's what I always do.

I believe that in some cases it might be worthy (performance-wise) to use persistent connections and reserve/release connection when needed (either for transaction or even for single query). For example, simple system that I'm now working with takes 70ms-100ms to generate, and it takes only 40ms-50ms to do SQL queries. If using "single connection" approach, it means that connection is wasted for about 50% of time, while "reserve/release connection when needed" with persistent connections would not have such issue.

One more thing - I would advise you to create some wrapper, i.e., some DBConnection class that connects to DB in constructor and has methods like select() (returns array of data), selectValue() (returns single value, e.g., $db->selectValue('select count(*) from user') would return (int)$numberOfUsers), some exec() for inserts and updates etc.