SQL Tip: Open Connection, Query, Close
Friday, August 22nd, 2008
A very simple tip to novice backend PHP (Hypertext Preprocessor) programmers is to understand that database connections have an overhead – a database connection should be opened, the tables queried with SQL (Structured Query Language), and then the connection is closed. Generally I keep the database connection in two functions within a discrete file that is re-usable throughout the website. Open, run my queries, and then close the connection. It’s hardly rocket science.
One novice mistake is to forget, or decide not to, close database connections. And another is to keep opening new database connections for every query.
A more seasoned approach to running queries is to open the door once, grab what you really need, and close it again as you leave. If you’re using those variables on another page then store them for ongoing use in a session, a cookie or pass them as a URL encoded string in name / value pairs.
So my small tip for the day is that if you’re going to run SQL queries in your PHP keep the process as simple and mechanical as possible. Open a connection, query the database, and close the connection on your way out.
