Correct way of having multiple php functions to query a single (mysql) database

Question

Working on a PHP website and I've encountered an efficiency issue that I can not solve on my own.

I have a couple of separate php files:

connection.php - connects to the database.
sqlFunctions.php - couple of functions that execute different sql (mysqli) queries, manipulate data and return it.
index.php - file that executes some of the functions from sqlFunctions.php and uses the returned values to display something in the page.

connection.php:

$servername = "DATA"; //Replaced to "DATA" for posting on stackoverflow
$username = "DATA";
$password = "DATA";
$dbname = "DATA";

$con = new mysqli($servername, $username, $password, $dbname);

if ($con->connect_error) {
  die("Connection failed: " . $con->connect_error);
}

sqlFunctions.php:

<?php
    function query1(){
       require('connection.php');
       //PDO Query to DB, fetch, store, modify data etc.
       mysqli_close($con);
       //Return modified data
    }
    function query2(){
       require('connection.php');
       //PDO Query to DB, fetch, store, modify some other data etc.
       mysqli_close($con);
       //Return modified data
    }
?>

index.php:

//Simplified version
require('sqlFunctions.php');
<?php echo query1();?>

So I was thinking - initiating a new connection to the db on every function call is not a good idea. And if I would initiate a connection to the db in a function in sqlFunctions.php - I would need to pass another variable/reference/pointer (you know what I mean) to every single function in that file and that is something that I don't want to do.

So what is the best approach to accomplish what I need?

TL;DR;:

Main file calls a function in a separate file
That function executes an sql query and returns data
Returned data is displayed
Without reopening/closing the db connection on every function call.

Food for thought: https://www.youtube.com/watch?v=3_alwb6Twiw — mickmackusa, Feb 22 '17 at 00:30

paulz · Accepted Answer · 2017-02-22T00:28:20.143

-1

There are several options.

Option 1. Declare your database connection global inside each function.

sqlFunctions.php:

<?php

    require('connection.php');

    function query1(){
       global $con;
     // mysqli code with $con   
    }
    function query2(){
       global $con;
       // mysqli code with $con
    }
?>

Option 2. Use GLOBALS.

connection.php:

...

$GLOBALS['con'] = new mysqli($servername, $username, $password, $dbname);

...

sqlFunctions.php:

<?php

    require('connection.php');

    function query1(){
     // mysqli code with $GLOBALS['con']
    }
    function query2(){
       // mysqli code with $GLOBALS['con']
    }
?>

Option 3. Wrap all functions into a class (note capital S).

SqlFunctions.php:

class SqlFunctions {
    protected $con;

    public function __construct() {
        global $con; 
        // can also pass $con as parameter or init db here
        $this->con = $con;
    }

    public function query1(){
        // mysqli code with $this->con
    }

    public function query2(){
        // mysqli code with $this->con
    }
}

index.php:

require('SqlFunctions.php');
$sqlFunctions = new SqlFunctions();

<?php echo $sqlFunctions->query1();?>

In this case you can also initialize the connection right inside the class or pass it as a parameter to __construct().

edited Feb 22 '17 at 00:28

answered Feb 22 '17 at 00:16

paulz

124
4

Thanks! Options 1 and 2 are just what i was looking for! Also, how would I go about closing the connection? I pressume it would work if I write another function to close the connection since I would have the connection as the global variable? – simplicity Feb 22 '17 at 00:50
You are welcome! You can close the connection after all calls to it are done. Otherwise, it will be closed automatically at the end of the script. Unless you have multiple other connections opening later on the same page (which I doubt), I'd just let the server close it for you. – paulz Feb 22 '17 at 00:53
This is a peculiar answer where you managed to give 3 solutions actually offering only one – Your Common Sense Feb 22 '17 at 05:27
@your-common-sense Can you explain what you mean? Which of the options is not valid and why? – paulz Feb 22 '17 at 11:16
globals is exactly the same as global. whereas object solution will give you the same problem again, but now not with db object but with your own one. – Your Common Sense Feb 22 '17 at 11:18
@your-common-sense GLOBALS and global calls per function represent totally different syntax, and these were exactly the options the OP was looking for. With the class, I was addressing the OP main concern that in his code a new connection was open with every function call. How will it "give the same problem again" if it is instantiated only once per page/per connection as per my version of index.php? – paulz Feb 22 '17 at 11:31
Imagine you will need to call your shiny new $sqlFunction inside another function... – Your Common Sense Feb 22 '17 at 11:46
@your-common-sense What "another function"? Is is part of the OP question? The OP was not much interested in OOP/PDO solutions. All 3 of my options solved the exact OP's problem in a simple and pragmatic way. None of them correspond to your "only proper PDO tutorial" (which I respect), but that does not make them invalid, and certaily does not warrant a downvote. – paulz Feb 22 '17 at 12:01
like I said, 1 and 2 are actually the same. It's the same approach different syntax. And you cannot use 3 without 1 or 2 anyway. The Op will have exactly the same problem accessing $sqlFunction like he had with $con – Your Common Sense Feb 22 '17 at 12:06
@your-common-sense Different syntax = different code = different options, even if they use the same construct under the hood. By your logic, all software in the world is the same, as it compiles down to the same machine code commands. $sqlFunctions→query1(), $sqlFunctions→query2() etc. can be called as many times as needed without reopening the connection. And this exactly addresses OP’s original question. Please show where exactly in index.php the OP would have “problem accessing $sqlFunction like he had with $con”. – paulz Feb 22 '17 at 12:25
I suppose, his code is not limited to the index.php shown. – Your Common Sense Feb 22 '17 at 12:30
@your-common-sense So, you do agree that there are no problems accessing $sqlFunctions with the actual code as presented in the actual quesion? – paulz Feb 22 '17 at 12:36

Correct way of having multiple php functions to query a single (mysql) database

1 Answers1