1

I'm a fairly new php programmer. I've been learning for several weeks and over those weeks i've tinkered from hello world scripts to taking other scripts online and installing them and modifying them. Now i'm writing my own and in the course of writing it, I had a bit of an epiphany.. because the scripts ive been modifying and toying with were done a certain way so i started that way as well. But now im thinking it could be inefficient coding or my idea is just wrong for some reason however..

my script is say index.php it's an interactive site and has users. i have 4 different databases that store some form of data. the first database is focused on users and their data. the second on something else, and so on.

The way I have been writing it is that anytime i need to do a query to the database, at the beginning of the script i have my database connection variables all defined such as

$servername = "127.0.0.1";
$dbusername = "dbusername";

    $dbpassword = "passwordhere";
    $dbname = "database1";
    $roomdb = "database2";

etc etc

so at all sorts of different points in my script there are needs to run database queries so i've been opening a connection like so

$link = mysqli_connect("$servername", "$dbusername", "$dbpassword", "$roomdb");

then running the query

then closing the database connection.

But then i was having a shower and thought suddenly.. why can't i just at the top of my script where i define the database connection info, just open ONE connection to each of the databases. then run the entire script, and just use queries where i need to use them.. and just name the queries differently if i need to have multiple queries to one database. like $query1 , $query2 etc where a query would be like 

$query = "INSERT INTO `$room` (room, message, color) VALUES ('$room', '$randomvariable', '$randomvariable')";

does this make sense? it just seems to me that if i almost assuredly have to make multiple connections to each of these databases each time the script is run, and even if theres a chance i dont need one of the connections once in a while, that it would be more efficient to just connect it once at the beginning, have it available then use unique query names for each different query, and then close the connections at the end of the script?

any insights as to why i might be wrong or why it might be better to open the full connection right where you need it then close it again would be appreciated. i want to know how it functions best so i can make the best coding practice as possible....

but lets pretend i even have one database. the same logic applies. is it more efficient to just open one db connection at the top of my script, keep it open the whole run of the script and just use different $querynames to execute queries to the same db rather than open it, run query, close it, and do that 10 different times for all the queries. it seems pointless to me to open and close the connection so many times and adding overhead. did the examples i learn from just code bad or is there a reason for that

cjackson
  • 93
  • 8
  • do you actually have multiple databases, or multiple tables in 1 database? if multiple databases, what is your reasoning behind that format? – Sean Apr 20 '15 at 18:41
  • i actually have multiple individual databases, and each database has several different tables. – cjackson Apr 20 '15 at 18:43
  • but lets pretend i even have one database. the same logic applies. is it more efficient to just open one db connection at the top of my script, keep it open the whole run of the script and just use different $querynames to execute queries to the same db rather than open it, run query, close it, and do that 10 different times for all the queries. it seems pointless to me to open and close the connection so many times and adding overhead. did the examples i learn from just code bad or is there a reason for that – cjackson Apr 20 '15 at 18:48

1 Answers1

2

We need to save each connection handler in different variable 

$link1 = mysqli_connect("$servername", "$dbusername", "$dbpassword", "$roomdb");    
$link2 = mysqli_connect("$servername", "$dbusername", "$dbpassword", "$dbname");

Then use the $link_identifier separately for each, so that we know what are connecting to

mysqli_query($link1,"INSERT INTO `$room` (room, message, color) VALUES ('$room', '$randomvariable', '$randomvariable'");

mysqli_close($link1);

Note : Opening a connection for too long is not good coding standard. We would only connect when we are needed to query anything in that database, and close the connection immediately after the transaction is over.

mysqlrockstar
  • 2,536
  • 1
  • 19
  • 36
  • right, i get how to do the connection, and how to run the queries but im just wondering is it more efficient to run $link1 and $link2 at the beginning of the script and THEN leave them open until they are needed later on in the script? the examples ive been using have only executed $link1 and $link2 when they need to update or insert something. then using mysqli_close($link) almost immediately.. then later in the script initiating $link1 again and running a query then closing it again.. thats just bad coding or is there a reason? – cjackson Apr 20 '15 at 18:54
  • see in your example you use mysqli_close($link1) right away. but pretend 20 lines further down in code i have another $query i need to send to that same database connection. doesnt it make sense to leave that $link1 open and not close it after the first query so i can use $link1 again 20 lines later instead of running $link1 first then accessing it – cjackson Apr 20 '15 at 18:58
  • handling multiple databases itself is painful, meaningless and complicated. We should always have one db connection and multiple table inside as @Sean commented. For the above case if you need $link1 again, better to close and reopen. Its not advisable to keep connection open as that will consume more memory and also there is a chance that data might get corrupted by wrong UPDATE / INSERT – mysqlrockstar Apr 20 '15 at 19:18
  • okay so if i move it all to one database, it is actually advisable to terminate the db connection immediately then re open it later in the script if i need it later? – cjackson Apr 20 '15 at 19:26
  • Correct, that will be more efficient way of handling multiple databases – mysqlrockstar Apr 20 '15 at 19:26
  • what if i move everything to one single database and just make lots of tables. could i then leave the $link open if im careful with my $querynames to not update the wrong things? my whole script is going to be about 300 lines long in total (not counting html forms) so it doesnt seem super long enough to me that i should have to worry about terminating the link or the memory usage from it. i guess i could be wrong. – cjackson Apr 20 '15 at 19:38
  • It would be great to have a single database with lots of tables. mysqli_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. Refer [this](http://stackoverflow.com/questions/14822291/when-should-i-use-mysqli-kill-and-mysqli-close) – mysqlrockstar Apr 20 '15 at 19:55
  • alright that tells me what i need to know.. i'll reorganize my script and move everything to one database so i can just keep one connection open. thank you! – cjackson Apr 20 '15 at 20:13