How do I make this "database connection" code more efficient to run?

Question

I have this code:

 function Perubahan($a = '`Ubah`') {
        $con = mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database

        $syntax = 'SELECT' . $a . ' FROM `table 1` WHERE `No`= 6';
        $naik = mysqli_query($con, $syntax);
        while ($row = mysqli_fetch_array($naik)) {
            echo round($row[0], 3);
        }
    }

    function Jenis($b = 1) {
        $con = mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database

        $syntax = 'SELECT `Jenis` FROM `table 1` WHERE `No`= ' . $b;
        $naik = mysqli_query($con, $syntax);
        while ($row = mysqli_fetch_array($naik)) {
            echo $row[0];
        }
    }

    function Andil($b = 1) {
        $con = mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database

        $syntax = 'SELECT `Andil` FROM `table 1` WHERE `No`= ' . $b;
        $naik = mysqli_query($con, $syntax);
        while ($row = mysqli_fetch_array($naik)) {
            echo round($row[0], 3);
        }
    }

    function Kelompok($b = 1) {
        $con = mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database

        $syntax = 'SELECT `Andil` FROM `table 1` WHERE `No`= ' . $b;
        $naik = mysqli_query($con, $syntax);
        while ($row = mysqli_fetch_array($naik)) {
            echo round($row[0], 3);
        }
    }

So I call each function (still in the same PHP file). But when I start running it, it took too long to show the result (but it worked). I'm guessing the problem is because I repeat the database connection in each function. How do I make avoid connecting to the same database?

I tried to create the database connection to different file and call the file in each function, but it didn't work. I also tried to pass the $con variable into the function (doing global $con in each function), but it also didn't make it run faster.

So am I missing something here?

Ill ask you one counter question to help you answer the question, in part, yourself: What if the `database name` or the `password` or the `username` were to change?. Do you go around all your functions and update all of them? That's one of many problems — Hanky Panky, Sep 04 '14 at 06:33
Why can't you make just one function for all of these? You repeat the same code (or near identical) on each function. — Rasclatt, Sep 04 '14 at 06:37
Apache can handle persistent database connections. Even if you connect multiple times doesnt mean there is a reconnection. If the script doenst run faster, you probably need an index on `No` — JaMaBing, Sep 04 '14 at 06:39
@Rasclatt: That's what I'm trying to do, but they don't seem to work faster than the previous code. — Safira, Sep 04 '14 at 06:39

Raphael Müller · Accepted Answer · 2014-09-04T08:33:26.050

2

You can use a class:

class NameService
{
    private $con = null;

    public function __construct()
    {
        $this->con = new mysqli("localhost", "root", "", "nofriani");
        if ($this->con->connect_error) {
            die('Connect Error (' . $this->con->connect_errno . ') '
             . $this->con->connect_error);
        }
    }

    public function __destruct()
    {
        $this->con->close();
    }

    function Andil($b = 1) {
        $syntax = 'SELECT `Andil` FROM `table 1` WHERE `No`= ' . $b;
        $result= $this->con->query($syntax);
        while ($row = $result->fetch_array()) {
            echo round($row[0], 3);
        }
    }

    ...
}

and use it:

$nameService = new NameService();
$nameService->Andil(23);

EDIT: now it's with OOP style

edited Sep 04 '14 at 08:33

answered Sep 04 '14 at 06:34

Raphael Müller

2,180
2
15
20

can I close the connection in the end? I don't seem to be able to.. I mean after I use the class, or is it fine not to close the connection? – Safira Sep 04 '14 at 06:51
you can use the __destruct method if you want. but normally the connection get's closed after script execution. please see my updated class. i also changed everything with mysqli to OOP – Raphael Müller Sep 04 '14 at 08:26
Thank you very much. (y) Accepted your answer just now. Upvoted as well. – Safira Sep 05 '14 at 03:09

score 1 · Answer 2 · answered Sep 04 '14 at 06:36

If you can use Classes, than you can create global variable for class, that holds connection to database, than each function will use same connection and there will be no reconnect.

Use prepared statements, because now your code is very easy to hack.

Functions Jenis, Andil and Kelompok does exactly same as Perubahan that is prepared for core reuse, so just rename it to getData($select = '*') and use only one function.

score 0 · Answer 3 · answered Sep 04 '14 at 06:44

0

As a rule of DRY you'd better extract this part of code :

mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database

and put in one place (a class or a function)where all other codes can access it. For example you can have a function like this :

function getConnection()
{
return mysqli_connect("localhost", "root", "", "nofriani"); //koneksi ke database
}

And then you can do something like this :

function Jenis($b = 1) {
        $con = getConnection();

        $syntax = 'SELECT `Jenis` FROM `table 1` WHERE `No`= ' . $b;
        $naik = mysqli_query($con, $syntax);
        while ($row = mysqli_fetch_array($naik)) {
            echo $row[0];
        }
    }

This way if your ConnectionString is changed you should change one place and all other functions work as before.(BTW it's better not to hard code the ConnectionString)

answered Sep 04 '14 at 06:44

Beatles1692

5,214
34
65

1

And maybe one could also check if a connection is already there then return that one, instead of creating a new one – Hanky Panky Sep 04 '14 at 06:47
It depends on your strategy.I think it's not a best practice to have a singleton connection for an application but a connection / transaction per request sounds fine to me. – Beatles1692 Sep 04 '14 at 07:14
One connection per function call? How would that be useful when you already have a connection established and its the same HTTP request and the same database, just a different query? – Hanky Panky Sep 04 '14 at 07:15
I didn't said one connection per function but per web request I meant. – Beatles1692 Sep 04 '14 at 07:16
But this code suggests one connection per function call if its implemented that way. Every function that calls `getConnection()` will start a new connection to mysqli which is not really useful – Hanky Panky Sep 04 '14 at 07:17
I did not suggest anything but to refactor the code so that DRY is applied.The code is merely the same as it was before.But now that we have it in one place we can think of making it better (phase 2 of refactoring) to have a connection per request.(I always prefer to refactor a code one step at a time) – Beatles1692 Sep 04 '14 at 07:20

How do I make this "database connection" code more efficient to run?

3 Answers3