#native_company# #native_desc#
#native_cta#

MySQL and PHP: How to make it work without killing your server Page 2

By PHP Builder Staff
on May 13, 2009

MySQL
MySQL queries probably slow down your pageloads more than anything
else. A big server-killer comes from queries inside loops–sometimes
unavoidable but in most cases there are things that could be done
to lighten the server load.

A simple class
When you have built enough websites you will see that you use many
things over and over again. At some point it just becomes simpler to
chuck that into a class and store it in some sort of archive that way you can
dip into whenever you lay out the structure of a new site. Being an
OOP junkie, I have a simple class that first defines the array
which creates my database connection.


File: dbConfig.php

  <?php

  Class dbConfig

  {

    public $config = array(

    'db_server'    => 'server_name',

    'db_name'      => 'database_name',

    'db_username'  => 'username',

    'db_password'  => 'password'

    );

  }

And then I keep a database class in my site library that references
these configs and in turn supplies the site with a database connection
as well as a few inbuilt functions that make sure my code is always
perfect no matter what I do. The simplest version of my database class
follows here:


File: db.php

  <?php

  Class db extends dbConfig

  {

    public $connection;

    public function __construct()

    {

      $this->connect();

    }

    public function connect()

    {

      $this->connection=mysql_connect($this->config['db_server'],$this->config['db_username'],$this->config['db_password']);

      if($this->connection):

        @mysql_select_db($this->config['db_name'],$this->connection);

      endif;

    }

    function updateTable($table, $field, $value, $id)

    {

      return mysql_query("update {$table} set {$field} = '{$value}' where id = {$id}", $this->connection);

    }

    function deleteFromTable($table, $id)

    {

      return mysql_query("delete from {$table} where id = {$id} limit 1", $this->connection);

    }

    function getArray($query)

    {

      if($result = @mysql_query($query, $this->connection))

      {

        while($tmp = @mysql_fetch_array($result))

          $dbarray[]= $tmp;

        if (isset($dbarray))

          return $dbarray;

        else return NULL;

      }

      return;

    }

    function query($query)

    {

      return mysql_query($query, $this->connection);

    }

  };

  $db = new db;

In essence, this gives me a very simple database framework that allows
me to manage the simple queries I need to do on the site. Since I have
started working with MVC Frameworks, however, I have found that this
way is not really workable. But for procedural coding it still works well.
Simply include both the dbConfig.php and db.php files in your includes
file or make sure your autoloader knows where to find them and you are
set.