Do I need a php mysql connection in each function that uses database?

I am creating a php restful API and currently I have the database connection information in each function.

//Connect To Database
    $hostname=host;
    $username=username;
    $password=password;
    $dbname=dbname;

    mysql_connect($hostname, $username, $password) OR DIE('Unable to connect to database! Please try again later.');
    mysql_select_db($dbname);
mysql_query($sqlApiAccess) or die('Error, insert query failed');

What is the best way of doing this, Can I have one database connection per php file? Or do I need to do it per function that uses the database.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To avoid creating a new database connection each time, we can use Singleton design pattern–

we need to have a database class- to handle the DB connection-

Database.class.php

<?php
        class Database
        {
            // Store the single instance of Database
            private static $m_pInstance;

            private $db_host='localhost';
            private $db_user = 'root';
            private $db_pass = '';
            private $db_name = 'databasename';

            // Private constructor to limit object instantiation to within the class
            private function __construct() 
            {
                mysql_connect($this->db_host,$this->db_user,$this->db_pass);
                mysql_select_db($this->db_name);
            }

            // Getter method for creating/returning the single instance of this class
            public static function getInstance()
            {
                if (!self::$m_pInstance)
                {
                    self::$m_pInstance = new Database();
                }
                return self::$m_pInstance;
            }

            public function query($query)
            {
               return mysql_query($query);
            }

         }
?>

& we can call it from other files-

other.php

<?php
       include 'singleton.php';
       $pDatabase = Database::getInstance();

       $result = $pDatabase->query('...');
?>

Method 2

Create a config.php And add the code:

config.php:

$hostname = 'host';
$username = 'username';
$password = 'password';
$dbname   = 'dbname';

$conn = mysqli_connect($hostname, $username, $password) OR die('Unable to connect to database! Please try again later.');
mysqli_select_db($conn, $dbname);

Then in any file you wish to use mysql, add the following:

script2.php

<?php
require_once 'config.php';

mysqli_query($sqlApiAccess) or die('Error, insert query failed');
?>

Method 3

There is no need to make connection in each function. you need to make a connection file like conn.php and make the connection queries.

<?php
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
?>

in any other file where you want to connect database just write this line

<?php include("conn.php");?>

On this file you are able to run any query.

Method 4

do this:

$db_connection= mysql_connect($hostname, $username, $password) OR DIE('Unable to connect to database! Please try again later.');

And every time you want to query:

mysql_query("my_query",$db_connection);

Note, if you connect to the DB in a function, you will need to global $db_connection.

And when you want to close the DB connection:

mysql_close($db_connection);

Method 5

Why won’t you move out the connection information into config and the call to mysql_connect into some factory?

E.g.

class ConnectionFactory {
    public static MysqlConnection CreateConnection() {
        $connection = mysql_connect(Config::$host, Config::$port etc);
        mysql_select_db($connection, Config::$schema);
        return $connection;
    }
}

and then in your code

$connection = ConnectionFactory::CreateConnection();
mysql_query($connection, $sqlApiAccess) or die('Error, insert query failed');

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0