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

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

Answers


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.


The first thing you should do is create a mysql.config.php file.

<?php
define('MYSQL_HOST','');
define('MYSQL_USER','');
define('MYSQL_PASSWORD','');
define('MYSQL_DATABASE','');
?>

The second thing you should do is create a mysql.class.php file which handles all of the database connection details (emulating this behavior). Just add new functions to extend functionality such as the numRows function.

<?php
require_once 'mysql.config.php';
class MySQL {
private $query;
private $result;
public function __construct($host = MYSQL_HOST, $user = MYSQL_USER, $password = MYSQL_PASSWORD, $database = MYSQL_DATABASE) {
    if (!$con = mysql_connect($host,$user,$password)) {
        throw new Exception('Error connecting to the server');
    }
    if (!mysql_select_db($database,$con)) {
        throw new Exception('Error selecting database');
    }
}
public function query($query) {
    $this->query = $query;
    if (!$this->result = mysql_query($query)) {
        throw new Exception('Error performing query '.$query);
    }
}
public function numRows() {
    if ($this->result) return mysql_num_rows($this->result);
    return false;
}
}
?>

The final thing you need to do is use these files in your app.

<?php
require_once 'mysql.class.php';
try {
    $db = new MySQL();
    $db->query('SELECT username FROM login WHERE username = "'.$USERNAME.'" LIMIT 1');
    if ($db->numRows() == 1) print_json(array('user'=>true),true);
    else print_json(array('user'=>false),true);
} catch(Exception $e) {
    echo $e->getMessage();
    exit();
}
?>

As the site is database heavy, I would suggest you to use PDO for database connections. Also use transactions for multiple queries, to reduce PHP-MySQL traffic.

Details here: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html


In relation to PHP programming language, MySQL is the old database driver, and MySQLi is the Improved driver. MySQLi takes advantage of the newer features of MySQL 5. Lifted verbatim from the php.net site:

  • Object-oriented interface
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions
  • Enhanced debugging capabilities
  • Embedded server support You have the choice of using mysql, mysqli, or PDO essentially.

If you're simply looking for a relational database, you need look no further than MySQL, though there are many options available.


RE: "should I use persistent connection"

No. If your database is "heavy", persistant connections won't really help - in fact, they may hinder with PHP scripts that stay open a long time while processing data. Persistent connections only speed up the time to connect to the database IN SOME CIRCUMSTANCES only. You MAY be able to save a few ms per script run, but you'll lose more running clean up functions to release locks or transactions unless you write carefully. So unless you have lots of very, very short scripts getting short, sharp db connections and then moving on, or the DB server is a long way from the web server, don't sweat on this point.

PDO has been suggested in other answers, so I've marked them up as it's going to be the best for you.


Need Your Help

How do I sort an array of custom classes?

c# .net arrays sorting c#-2.0

I have a class with 2 strings and 1 double (amount).