Most efficient ways to do connect to mysql database across multiple files (PHP)

Question

Where there are so many ways to achieve the same result, I am wondering which is the most efficient way of initializing a mysql connection.

Note: Recently I also discovered in the PHP documentation that mysql_connect is now DISCOURAGED, all my codes are currently using mysql_connect. Is there enough reason to switch?

What i'm trying to do is:

1. create a database.php.
2. PHP pages that are going to connect to database will include this file so that I don't have to write a lot of code to connect to the database.

So what should this database.php do, in order to be most efficient.

1. $con = mysql_connect("etc"...)
   mysql_select_db("databse")

// from here I can include this file and start making queries. - Works but I'm not sure if its the best way.

2. //database.php
   $mysqli = new mysqli('localhost', 'user', 'pwd', 'database');
   // in other files
   $mysqli->query("SELECT ...")

1. or should I use persistent connection? (The site is quite database-heavy, and there are going to be a lot of page generation, no more than 100 users simultaneously logged in)

Thank you in advance

Answer 1

I just changed all functions of MySQL extension in my code to PDO today.

In order to be most flexible, you should create "database.php" with a general connection like this:

<?php
$pdo = new PDO('mysql:host=example.com;dbname=database;charset=UTF-8', 
               'username', 
               'password');

Then in other page, use require() to include the file like this:

<?php
require('database.php');

// Example with 'query'
$queriedSQL = $pdo->query('SELECT * FROM table');

// Example with 'prepare'
$preparedSQL = $pdo->prepare('SELECT * FROM other_table WHERE id = ?');
$preparedSQL->setFetchMode(PDO::FETCH_ASSOC);

while($result = $queriedSQL->fetch(PDO::FETCH_ASSOC)) {
  $preparedSQL->execute(array($result['id']));
  $preparedSQLResult = $preparedSQL->fetch();
  // Process code here
}

In other way, you can make "index.php" as the core and then process the URL or $_GET[] to include the corresponding file (like all CMS system, Wordpress, etc).

That way you can avoid change amount of code when some data (host, username, password and/or database name) changed.

About persistent connection, PHP allows it, but there are drawbacks as you can found in here.