Page 1 of 1
MySQL Database connection strategy
Posted: Mon Aug 29, 2011 4:34 am
by eazyGen
Hi guys.
I am generally familiar with databases (mostly DB2), but I am a newbie when it comes to PHP and MySQL.
I notice that in order to use a MySQL databases, a connection must first be made to the database and, somewhere along the line, that connection needs to be closed.
I am thinking that to open a connection at the start of a script and then to close it at the end will work. However, if I do this for each and every script that accesses a database table, then there will be numerous open and close statements running through my application.
I am wondering therefore is there is a generally accepted strategy for handling a database that minimises the amount of connection statements; or if the above is essential given the static state nature of a web page.
Any assistance much appreciated.
S
Re: MySQL Database connection strategy
Posted: Mon Sep 12, 2011 12:04 pm
by akuji36
Hello
Closing a connection is important to individuals who share a connection with others.
Nice practice to close connection to mysql .
Also use your connection script as an include.
In your connection script be sure to sanitize any variables coming into mysql using:
mysql_real_escape_string, stripslashes, htmlentitles, magic quotes (these should be off), and trim white space.
Re: MySQL Database connection strategy
Posted: Mon Sep 12, 2011 12:17 pm
by AbraCadaver
eazyGen wrote:Hi guys.
I am generally familiar with databases (mostly DB2), but I am a newbie when it comes to PHP and MySQL.
I notice that in order to use a MySQL databases, a connection must first be made to the database and, somewhere along the line, that connection needs to be closed.
I am thinking that to open a connection at the start of a script and then to close it at the end will work. However, if I do this for each and every script that accesses a database table, then there will be numerous open and close statements running through my application.
I am wondering therefore is there is a generally accepted strategy for handling a database that minimises the amount of connection statements; or if the above is essential given the static state nature of a web page.
Any assistance much appreciated.
S
If using mysql_connect(), the connection is closed automatically when the running script ends so you have to open on each script run. You can use mysql_pconnect() which is a persistent connection that will not close when the script ends and mysql_close() has no effect on this connection. Depending on your app and expected traffic you might want to look for some info on persistent versus non-persistent connections.
Also pconnect is only available when PHP is running as a module.
Re: MySQL Database connection strategy
Posted: Thu Sep 29, 2011 2:42 am
by eazyGen
Thanks guys. I appreciate your thoughts and comments.
I am anticipating needing a persistent connection as the app will be using Ajax in time. However, I am still doing my testing. I am also looking at MySQLI, which has some benefits over base MySQL it seems to me, not least in the way that it seems to follow an OO type paradigm.
I do have one question and it concerns "cleaning" input data so it can be used consistently and also stored in a database safely.
What would you say is the full and complete process by which input data should be cleansed, but ensuring that the process is cross platform and also not dependant on any given PHP Apache etc settings.
As ever, and further help much appreciated.
S