A proper way to open and close mysqli queries

I have this simple yet confusing question, special for people that are self taught programmer like my self... I have read different documentation in PHP.NET and MYSQL.COM, they all explain how to open and how closed it but for this question that doesn't really help, at least for me.

The way that I've learn this is as follow I require of a file to my conecction such as dbconnection.php or db.php the name doesn't mater but the content so here is what I always do...

<?php
// con.php
$host = 'localhost';
$us = 'root'; // or whatever name for the user
$ps = 'abcd'; // your password
$db = 'abc'; // The name of your DB

$con =  mysqli_connect ($host, $us, $ps, $db);

if (mysqli_connect_errno()) {
  echo "DB server offline: ". $mysqli->connect_error;
  exit();
}

So there is my conection to my DB, now lets fetch some watermelons

<?php
include ('con.php');
// Lets get our varibales $_GET or $_POST
// Lets clean our variables
// Lets make sure that the data is what we expect, numbers and letters
// because thats how I roll!

$query_one = "SELECT * FROM table 1 WHERE id = 1";
$r_one = mysqli_query($con, $query_one);
$rows = mysqli_fetch_assoc($r_one); 
// now lets echo the results, or print_r() or json()...
mysqli_close($con); // this is pretty straight forward i think...

Now here is my confusion...

        <?php
        include ('con.php');
        // Lets get our varibales $_GET or $_POST
        // Lets clean our variables
        // Lets make sure that the data is what we expect, numbers and letters
        // because thats how I roll!

        $query_one = "SELECT * FROM table_1 WHERE id = '$ids'";
        $r_one = mysqli_query($con, $query_one);
        $rows = mysqli_fetch_assoc($r_one); 
   // If I closed here the second query doesn't get executed...
    if ($rows['publish'] == 1) {
       $query_two = "SELECT * FROM table_2 WHERE id_user = '$ids' AND items = '$items'";
       // lets do some foreach or while
   // If I closed here the first query still open...
    }
   // If I close here 
    mysqli_close($con); // Am I closing both queries?
// Let get some more data
// Should I close my third query
mysqli_close($con);
// hasn't this been close already? which mean that the third query never got
the change to execute... correct?

So as you can see, in MYSQL.com it tell you how to close it, and PHP.net is almost the same, but it doesn't tell you when to close it..

if a = 1 do a query then close it... else if a = 5 do another query then close it, this is very simple I think, but what if one of my queries relay on the information from another query...

if a = 1 do a query do some stuff and more stuff and a ton of IF's then if the result from the second query was B do another query... and so on... Maybe I'm just over-thinking it, then there is the other way to open or closed MySQLi

$con_one = conectTodb();
// queries
$con_one ->close();

if you have 3 queries, con_one, con_two, con_three, then you have to close one by one, like con_one->close(), con_two->close(), con_three->close()... and so on... correct?

I'm asking this because a few hrs. ago I had an error telling me that there was too many connections to the data base and for that it was not possible to process the information... it turnout it was a server miss-configuration, that got me thinking, what am I going to do if that was was a real situation!??? what if a user is buying something and then POW!!! too many connections, data lost... just the though of that will give some nightmares

I don't know... my brain is steaming right now... can some one please! explain this to me if is not much to ask please provide some examples...

thank you very much!

pd. on a local host the maximum number of connections is 10(IIS, WAMP, XAMP)... on a share host I think is 25...

Answers


mysqli_close() closes a connection not a query. You should only close the connection if you know it will not be needed once again for the current request. The connection will automatically close at the end of the PHP execution if you don't explicitly close it in your code.

Usually there is no need to explicitly close the connection and obviously there are some special circumstances where you might want to, for example if your script does some huge processing after using the connection where it would be beneficial to free up the connection during the processing.


Need Your Help

conditionally bootstrap app in angularjs

angularjs google-maps-api-3

I have an angularJs web app that uses google maps api v3. I'm using ui-map from angular-ui.

Creating a directory in linux assembly language

c linux assembly directory

I am trying to create a small assembly program to create a folder. I looked up the system call for creating a directory on this page. It says that it is identified by 27h. How would I go about