How do you efficiently connect to mysql in php without reconnecting on every query


Problem :

I'm pretty sick of having to rewrite my code every time I learn something new about php (like the fact that mysql connections cannot be passed around in a session as a handle).

How do you implement mysql connection in your projects? A lot of people have proposed "connection pooling", but after reading the manual i'm still lost. It's like: "connection pooling is mysql_pconnect!" - me: "and...? how is that any different in reality? can you pass around a mysql_pconnect in a session? why is this seemingly mysterious aura??"

Let me explain my situation. I have a function called "query1":

function query1($query)
{
    $db = new mysql(HOST,USER,PASS,DBNAME);
    $result = $db->query($query);
    $db->close();
    return $result;
} 

This is seems like a squanderous and inefficient way of querying a db (especially since you need a mysql handle for functions like mysql_real_escape_string). What is the correct form to do it? Can someone please help me?

Thank you I'd really appreciate a good honest answer.



Solution :

Normally connections happen once a page load. AKA

class Database{
    public function connect()
    {
         $this->connection = mysql_connect();
    }

    // This will be called at the end of the script.
    public function __destruct()
    {
        mysql_close($this->connection);
    }

    public function function query($query)
    {
        return mysql_query($query, $this->connection);
    }
}
$database = new Database;
$database->connect();

$database->query("INSERT INTO TABLE (`Name`) VALUES('Chacha')");

Basically, you open the connection in the beginning of the page, close it at the end page. Then, you can make various queries during the page and don't have to do anything to the connection.

You could even do the mysql_connect in the constructor as Erik suggest.


To use the above using global variables (not suggested as it creates global state), you would do something like

Global $db;

$db = new Database;
// ... do startup stuff

function doSomething()
{
    Global $db;
    $db->query("Do Something");
}

Oh, and no one mentioned you don't have to pass around a parameter. Just connect

mysql_connect();

Then, mysql_query will just use the last connection no matter what the scope is.

mysql_connect();

function doSomething()
{
    mysql_query("Do something");
}

Per the comments:

I think you should use mysql_pconnect() instead of mysql_connect(), because mysql_connect() doesn't use connection pooling. – nightcoder

You might want to consider whether you use mysql_connect or mysql_pconnect. However, you should still only connect once per script.


Mysql Tutorials

Mysql Howto..

How to use jQuery UI Accordion with Loop

how to get the sum of values in the different field in mysql?

How to add 0's infront with auto increment in a mysql database

How to use Java to encode/decode/transcode byte array using a charset directly

mysql how to know db's name from which i search

Mysql in PHP - how to update only one row in table but with greatest id number

How can I optimize my query (rank query)?

how to correct font error of mysql

How do I restore only 1 mySQL value row for an entire table?

MySQL: how to group the sum of two columns that return more than one value?

How to give foreign key to MySQL event in phpMyAdmin with table field id

How to compare and sum different columns in a single table mysql

How to search a MySQL table for the most accurate matching

how to store big text data in two tables with an id link using mysql and php

MySQL Database: How far to Normalize / Queries VS Join / Unique Index

How to apply CSS to dynamically generated PHP database results

How to give a special format to a number with auto_increment in MYSQL?

How to build a “services” BD with prices

How to convert this sql to sqlalchemy query? (YEAR, MONTH, COUNT in MySQL)

how to list items of a mysql aggregate function within the column

how to save tree data on database to analyze

How to creat a temporary table using existing tables with their records in MySQL?

MySQLMembershipProvider and MySQLRoleProvider not showing in ASP.NET Web Site Administration Tool

How to use mysql IN comparison function with JDBC

mysql how to update a field only if new value is lower then the existing value

How update a database table record in Zend?

How to extract the rows that contain only 1 “=” by using REGEXP in MySQL?

How to use data reader in vb.net

MYSQL: how to find entries corresponding to MIN() of costly function

How to get MySQL service status of remote machine?