PHP/MySQL performance question

Question

Just had a quick question for those of you knowledgeable about performance. I have created a "MySQL" class, and every time I perform a query, I create a new MySQL object. See below

    public function get_am_sales() {
        $mysql = new MySQL();
        $mysql->connect();
        $query = "some query";
        $mysql->query($query);
        $result = $mysql->return_assoc();
        unset($mysql);
        return $result;
    }

    public function get_pm_sales() {
        $mysql = new MySQL();
        $mysql->connect();
        $query = "some query";
        $mysql->query($query);
        $result = $mysql->return_assoc();
        unset($mysql);
        return $result;
    }

    public function get_comp_sales() {
        $mysql = new MySQL();
        $mysql->connect();
        $query = "some query";
        $mysql->query($query);
        $result = $mysql->return_assoc();
        unset($mysql);
        return $result;
    }

$sales = new Sales();
$amSales = $sales->get_am_sales();
$pmSales = $sales->get_pm_sales();
$compSales = $sales->get_comp_sales();

The code above obviously works, but I was wondering if this is a performance hit since I open and close a connection with every function call. I have tried to implement the class using one connection, but I get errors. See below

    public function connect() {
        $this->mysql = new MySQL();
        $this->mysql->connect();
    }

    public function get_am_sales() {
        $query = "SELECT site_id, dly_sls AS am_sales, cov_cnt AS am_covers
                  FROM v_sales_yesterday
                  WHERE am_pm = 'AM'
                  GROUP BY site_id
                  ORDER BY site_id ASC";
        $this->mysql->query($query);
        $result = $this->mysql->return_assoc();
        return $result;
    }

    public function get_pm_sales() {
        $query = "SELECT site_id, dly_sls AS pm_sales, cov_cnt AS pm_covers
                  FROM v_sales_today
                  WHERE am_pm = 'PM'
                  GROUP BY site_id
                  ORDER BY site_id ASC";
        $this->mysql->query($query);
        $result = $this->mysql->return_assoc();
        return $result;
    }

    public function get_comp_sales() {
        $query = "SELECT business_date, site_id, dly_sls AS comp_sales
                  FROM v_sales_today_comp 
                  WHERE am_pm = 'PM'
                  GROUP BY site_id
                  ORDER BY site_id ASC";
        $this->mysql->query($query);
        $result = $this->mysql->return_assoc();
        return $result;
    }

    public function disconnect() {
        unset($this->mysql);
    }

$sales = new Sales();
$sales->connect();
$amSales = $sales->get_am_sales();
$pmSales = $sales->get_pm_sales();
$compSales = $sales->get_comp_sales();
$sales->disconnect();

Does the mysql connection close after the "connect" function executes? If deemed necessary, what would be the best way to keep the connection open (using an object oriented approach)? Please let me know if you need anymore details, and I appreciate the help in advance.

What is the error you are getting? Normally, you mustn't re-connect. To prevent multiple connections, you should also use the singleton-pattern for your DB-class. — feeela
– feeela, Commented Aug 25, 2011 at 15:22
I get an error when attempting to query, which leads me to believe the connection is closing before the query. — Brett
– Brett, Commented Aug 25, 2011 at 15:24
OK, I ask it a second time: what/which error you are getting? Stop supposing, try testing or debugging. — feeela
– feeela, Commented Aug 25, 2011 at 15:26
Warning: mysql_query() [function.mysql-query]: Access denied for user 'www-data'@'localhost' (using password: NO) — Brett
– Brett, Commented Aug 25, 2011 at 15:31
Warning: mysql_query() [function.mysql-query]: A link to the server could not be established — Brett
– Brett, Commented Aug 25, 2011 at 15:32

swordfish · Accepted Answer · 2011-08-25 15:25:42Z

3

This is like totally unnecessary. You dont have to open and close connections for each query. Are you using some kind of a framework? If not you must as all frameworks have active records and DB helpers to make interacting with DB easier. THis would be a great article for you

http://net.tutsplus.com/tutorials/php/real-world-oop-with-php-and-mysql/

has just what youre lookng for.

answered Aug 25, 2011 at 15:25

swordfish

5,0255 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Noah Goodrich · Accepted Answer · 2011-08-25 15:25:50Z

In general, you only want to create your MySQL instance once during the execution of the application.

If you have an overarching Application object then you could just create your MySQL instance inside of the Application object and access it from there.

The other alternative is to create a Singleton object for your MySQL instance that is then accessed via an 'instance()' method.

This way you only open a single connection during the execution cycle of your php script and it will be discarded at the end of script execution.

So now your code looks like so:

$query = "SELECT * FROM users";

$rs = Mysql::instance()->query($query);

If you need working examples of how to setup a Singleton instance for your purposes you can look at:

Collectives™ on Stack Overflow

PHP/MySQL performance question

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related