Do I need to open a Mysql connection for each PHP query?

Question

I need to get several variables based on my bank to mount a full Dashboard. For example, I need the number of lines, the average value of a given field and the same average value only per FU.

The strategy that has worked with me is to ask for access through the function msqli_connect() and right after the request answered I close with msqli_close(). With this I’m only getting one action, IE, only the average, or only the list of all users, or just the sum. I couldn’t, for example, get the average and the sum within the same request. Is that right? how can I request more data in the same request?

The code I’ve been using is:

// Para conseguir a média
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "SELECT AVG(q1) AS avg FROM nome_da_tabela";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        // output data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo $row["avg"];
        }
    } else {
        echo "0 results";
    }


    mysqli_close($conn);

    // agora para conseguir a soma: 

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "SELECT SUM(q1) AS soma FROM nome_da_tabela";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        // output data of each row
        while($row = mysqli_fetch_assoc($result)) {
            echo $row["soma"];
        }
    } else {
        echo "0 results";
    }


    mysqli_close($conn);

Answer 1

No, preferably you should not do this, in PHP it is even easy to control it because in almost 100% of the cases it is only running one script ephemeral, so the limit is basically the duration of it, may have some exception, but almost always this is the appropriate.

It is not the end of the world to do differently, if you do it right, which complicates more, but it has no advantage, even if it were good and would not need to require opening and closing, every query would open and close the connection on its own. The case shown in the question is clearly wrong, even if it works.

On the other hand the question has another important issue which is the abuse of consultations when only one resolves. This yes causes quite a problem because it contacts the server twice and has almost repeated processing to do the same thing. This is a case that should not only have a connection but a single query. She asks for two things on the same table under the same conditions, so she does it all at once:

SELECT AVG(q1) AS avg, SUM(q1) AS soma FROM nome_da_tabela

I put in the Github for future reference.

Then take the two returned fields in the same processing. This whole code can be reduced to just over half a dozen lines, which is much simpler.

Answer 2

Hi, Pedro, all right?

You can create a file to contain the connection to Mysql and embed it within it. This way, the connection to the database will already be open and you can make your queries. Finish with mysqli_close($Conn) only when you have completed all queries.

Ex:

Arquivo: connection.php
.
$servername = "";
$username= "";
$password = "";
$dbname= "";

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

With this, you can make a include of this file on any page and make the queries, with a single connection. ;)

I hope I’ve helped.