Opening and closing a mysqli connection for multiple queries

Question

I have a question regarding when and where a connection to the database has to be made and closed. I have an array of rows which are to be inserted into or deleted from the database, it's a mixture.

(1)This is my code where i'm just testing out inserting to the database:

include('connect.php');

foreach($array as $row)
{
    if($row['insert'])
    {
       insertRow();
    }else
    {
        //deleteRow();
    }
}

function insertRow()
{
    $sql = "insert sql statement";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

(2)This doesn't work. So I move my include file here:

foreach($array as $row)
{
    include('connect.php');
    if($row['insert'])
    {
       insertRow();
    }else
    {
        //deleteRow();
    }
}

(3)And it still doesn't work so I move my include here which finally works:

function insertRow()
{
    include('connect.php');
    $sql = "insert sql statement";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

And so my question is: Must I make a connection for every sql query and then close it afterwards? So for what I want to do it'll look something like this?

foreach($array as $row)
{

    if($row['insert'])
    {
       insertRow();
    }else
    {
        deleteRow();
    }
}

function insertRow()
{
    include('connect.php');
    $sql = "insert sql statement";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    conn->close();
}

function deleteRow()
{
    include('connect.php');
    $sql = "delete sql statement";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    conn->close();
}

However this seems inefficient especially if I have an array of 100s of rows to insert/delete. Is there a better way of doing this? Thanks a lot.

you don't need to include `connect.php` multiple time. Include `connect.php` file in top then pass $conn variable in function argument like `deleteRow($conn)` or you can use it like `deleteRow(){global $conn;......}` — Payer Ahammed, Sep 28 '15 at 07:30

score 3 · Accepted Answer · answered Sep 28 '15 at 07:33

Adding the include multiple times is certainly wrong. Make the connection once at the top of the script, and then it can be used throughout.

Your problem is that $conn is not available from inside your functions. You need to declare that $conn exists 'outside' your functions, ie: Global Scope by adding global $conn; inside each function.

// Create $conn in Global Scope
include('connect.php');

function insertRow()
{
    //include('connect.php');
    global $conn;

    $sql = "insert sql statement";
    ...

}

function deleteRow()
{
    global $conn;

    $sql = "delete sql statement";
    ...

}

Opening and closing a mysqli connection for multiple queries

1 Answers1