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

Question

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.

By the way, take a look at `PDO` library. It provides the nice object-oriented way to work with DB (as opposed to `mysql` and `mysqli`); also it allows for prepared statements (while `mysql` IIRC does not support these, leaving all the escaping stuff to developer). — penartur, Apr 18 '12 at 12:11
I am new to PHP and was not sure on the requirements on db connections. I will look at the PDO library — Moltra, Apr 18 '12 at 12:23

score 13 · Answer 1 · edited May 23 '17 at 12:00

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('...');
?>

I was using this pattern, but sometimes I got an error saying `Too many database connections`..where should I close the database connection. — ELITE, May 11 '16 at 09:47

score 12 · Accepted Answer · edited Oct 29 '19 at 08:36

12

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');
?>

edited Oct 29 '19 at 08:36

Spej

31
3
6

answered Apr 18 '12 at 11:51

Menztrual

40,867
12
57
70

2

Also; Message from the pros :P Be sure to sanitise your input ;) – Menztrual Apr 18 '12 at 11:52
This works great. I run all my api parameters through htmlspecialchars and mysql_real_escape_string. That is probably an over kill, but I want to protect my db. – Moltra Apr 18 '12 at 11:58
Dont worry about html_entities() on input, just output. And be sure to use ENT_QUOTES as well with the entities/specialchars ;) – Menztrual Apr 18 '12 at 12:00
so you are saying I do not have to worry about he htmlspecialchars on inputs? – Moltra Apr 18 '12 at 12:09
2

@tehlulz First, your `config.php` does some work on include, which should be avoided (by e.g. moving the call to `mysql_connect` out from `config.php`). The name *config.php* won't tell the future maintainer that it actually establishes a connection. Second, relying on a global state is harmful; `mysql` supports working with handles as well as storing the handle as a global state. – penartur Apr 18 '12 at 12:09
This doesn't really answer the question, because requiring the file from different places will execute `mysql_connenct` multiple times, and part of the question was - how to avoid (if needed) connecting to the DB multiple times. – The Godfather Jul 07 '19 at 12:36
@TheGodfather from memory (as this was answered 7 years ago) the answer is suggesting to move the db connection code outside of each function call into one file. The main script you load (eg: /news.php) includes the db connection code first. Any files included afterwards by the PHP script would then have access to the database, as the connection has already happened (once). This means if OP's API had 6 functions, it no longer makes 6 connections (as each function made a new connection previously) which answers the OP's original question. – Menztrual Jul 13 '19 at 04:55

Waheed Ur Rehman · Answer 3 · 2013-01-22T10:15:08.807

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.

edited Jan 22 '13 at 10:15

answered Apr 18 '12 at 11:51

Waheed Ur Rehman

100
8

score 1 · Answer 4 · answered Apr 18 '12 at 11:51

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);

score 0 · Answer 5 · answered Apr 18 '12 at 11:52

0

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');

answered Apr 18 '12 at 11:52

penartur

9,792
5
39
50

2

For someone who is just asking if he needs to put that same code in every file, I think having a ConnectionFactory is waay overkill and if anything, will have him scratching his head even more. – Menztrual Apr 18 '12 at 11:53
@tehlulz Well maybe. I read Allen Carr's *The Easy Way to Stop PHPing* a couple of years ago, and already forgot how bad software written in PHP could be. – penartur Apr 18 '12 at 12:04
1

There's a difference between learning how to use a language and writing a full blown application :P – Menztrual Apr 18 '12 at 12:05

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

5 Answers5

Linked