PHP OOP database connect

Question

I have just started learning the concept of Object oriented programming and I've written this class with functions.

It works fine, but I'm interested in knowing if I have done this correctly...

Here is my code:

class Database{
    const DB_HOSTNAME = 'localhost';
    const DB_USERNAME = 'root';
    const DB_PASSWORD = 'password';
    const DB_NAME = 'shop';
    protected $_db_connect;
    protected $_sql;
    protected $_result;
    protected $_row;

    function db_connect(){
        $this->_db_connect = mysql_connect(self::DB_HOSTNAME,self::DB_USERNAME,self::DB_PASSWORD) or die(mysql_error());
    }

    function slect_db(){
        mysql_select_db(self::DB_NAME) or die(mysql_error());
    }

    function sql(){
        $this->_sql = 'SELECT * FROM users';
    }

    function query(){
        $this->_result = mysql_query($this->_sql);
    }

    function fetch_array(){
        while($this->_row = mysql_fetch_array($this->_result)){
            $username = $this->_row['user_USERNAME'];

            echo "<ul>";
                echo "<li>".$username."</li>";
            echo "</ul>";
        }
    }

    function db_close(){
        mysql_close($this->_db_connect);
    }
}

$database = new Database();
$database->db_connect();
$database->slect_db();
$database->sql();
$database->query();
$database->fetch_array();
$database->db_close();

Answer 1

Please use PDO or MySQLi as it's more secure and mysql_* functions are deprecated as stated above, I've provided some generic code using PDO to help you with that new venture. As stated in the comments, you really shouldn't be echoing out the data if you're looking for an object oriented design, what you should be doing is after you do the query, return the rows that you fetched and from there use something such as a foreach loop to then display your data. Doing your database class this way will also make sure that multiple database connections aren't open at once! Note, this code is just for you to reference, and should be tested before use in production or anything live.

config.php:

<?php
    //Enter your database connection details here.
    $host = 'localhost'; //HOST NAME.
    $db_name = 'databasename'; //Database Name
    $db_username = 'root'; //Database Username
    $db_password = ''; //Database Password

    try
    {
        $pdo = new PDO('mysql:host='. $host .';dbname='.$db_name, $db_username, $db_password);
    }
    catch (PDOException $e)
    {
        exit('Error Connecting To DataBase');
    }
?>

database.class.php:

<?php
    class database
    {
        function __construct($pdo)
        {
            $this->pdo = $pdo;
        }

        function getData()
        {
            $query = $this->pdo->prepare('SELECT * FROM database');
            $query->execute();
            return $query->fetchAll();
        }
    }
?>

index.php:

<?php
    require_once 'config.php';
    require_once 'database.class.php';
    $db = new database($pdo);
    $rows = $db->getData();
?>

Answer 2

$objConn = new mysqlconnect(); $Conn = $objConn->setobjConnect("localhost","root","P@ssw0rd");

Answer 3

$database = new Connection();
class Connection
{
 function __construct()
 {
    switch($_SERVER['DOCUMENT_ROOT'])
    {
        case 'path':
            $this->host = 'hostname';
            $this->user = 'username';
            $this->passwd = 'password';
            $this->database = 'dbname';
            break;
        default :
            $this->host = 'localhost';
            $this->user = 'root';
            $this->passwd = 'root';
            $this->database = 'dbname';
            break;
    }
    $this->clink = @mysql_connect($this->host,$this->user,$this->passwd);
    @mysql_select_db($this->database,$this->clink);         
 }
}

Answer 4

![<?php
class mysqlconnect{
private $server=null;private $user=null;private $password=null;private $objConnect=null;
function setobjConnect($s,$u,$p){
$this->server = $s;
$this->user = $u;
$this->password = $p;
return $this->objConnect = mysql_connect($this->server,$this->user,$this->password);
}
}
$objConn = new mysqlconnect();
$Conn = $objConn->setobjConnect("localhost","root","P@ssw0rd");
if($Conn)
    {
        echo "Database Connect";
    }
    else
    {
        echo "Database Connect Failed.";
    }
mysql_close($Conn); 

?>]

1

Answer 5

The answer by Sieu Phan is okay but it does not actually really ensure you only have one connection opened (would you include the config.php file multiple times it would create multiple connections).

It is possible to improve the way you connect to databases using autoloading and dependency injection containers. Here is a way of using Auryn to connect to your database while being sure there is only one connection opened and not having to manually require files throughout your application.

I will cover only PDO and Auryn here. There are other dependency injection containers and notably the mysqli extension to connect to database, but the content should help you using another container if you wish to do so.

The database class

Having a database class is superfluous. The \PDO class is already providing all necessary methods to query the database. Having a database class makes you actually repeat all the functions it already provides, and actually limits your actions (or makes you create many functions) when you want to for example use multiple different fetch styles depending on your needs in a specific method.

Dependency Injection

If you haven't already, have a read on dependency injection. The point is that when a class needs to access the database, it should not have to bother constructing the \PDO object, it should be constructed with it:

class Mapper {
    private $pdo;
    public function __construct(\PDO $pdo) {
        $this->pdo = $pdo;
    }
    public function createFromId($id) {
        $stmt = $this->pdo->prepare("SELECT name FROM foo WHERE id=:id");
        $stmt->execute([
            ":id" => $id,
        ]);
        return $stmt->fetchObject();
    }
}

Notice that I directly pass the \PDO object, not a wrapper class. That way, I always have access to all of its capabilities, not only a subset of user-defined functions.

Dependency Injection Container

A dependency injection container helps build your classes, giving them the objects they need, and giving you great flexibility on how to actually build those objects. Here I'll only focus on configuring and sharing a \PDO object through the use of Auryn.

I assume you have installed the required Auryn class, the easier way is using composer. This is out of the scope of this answer, there are multiple resources on how to use it.

• Create the injector

  $injector = new \Auryn\Injector();
  

• Define the \PDO class parameters

  $injector->define("PDO", [
      ":dsn" => "mysql:host=localhost;charset=utf8;dbname=dbname",
      ":username" => "user",
      ":passwd" => "passwd",
      ":options" => [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false,
      ],
  ]);
  

  You can either write the configuration parameters directly here, or get them from a config file. I like having a config.ini file and using parse_ini_file() to get my configuration options, as I can easily switch databases by editing a config file.

• Share the \PDO object

  $injector->share("PDO");
  

  This part is really important. This lines makes the injector give the same \PDO object every time it is tasked with constructing a class that needs a connection. Note that the order of the lines is not important, you can share the class before defining it, only make sure to create you database needing classes after writing both those lines.

• Create your objects

  $mapper = $injector->make("Mapper");
  

  That's it. The injector will create your mapper object, creating the \PDO object if it haven't already been, passing the existing instance if it has.

Autoloading

Assuming you have used composer, you can make use of it's great autoloader. Otherwise you can also roll you own autoloader.

The point here is to stop having require() everywhere in your code, especially if you have complex class hierarchies, which you should have in a single responsibility compliant class system.

Wrapping up

With this set up, you now can use the \PDO object in your classes while being assured there will only be one instance per request, without the need to require files everywhere, and without using a singleton anti-pattern.

Answer 6

class Database{
    var $last_query; //Saved result of the last query made
    var $last_result; //Results of the last query made
    var $func_call; //A textual description of the last query/get_row/get_var call
    var $link; //database link
    var $lastquery; //last query
    var $result; //query result

    // Connect to MySQL database
    function database() {
        $this->link=mysql_connect(DB_HOST, DB_USER, DB_PASS) or die('Server connexion not possible.');
        //Set All Charsets to UTF8
        mysql_query("SET character_set_results=utf8 , character_set_client=utf8 , character_set_connection=utf8 , character_set_database=utf8 , character_set_server=utf8");
        mysql_select_db(DB_NAME) or die('Database connection not possible.');
    }
    /** Query the database.
      * @param $query The query.
      * @return The result of the query into $lastquery, to use with fetchNextObject().
      */
    function query( $query ){
        $this->lastquery=$query;
        $this->result=@mysql_query( $query, $this->link );
        return $this->result;
    }
    /** Do the same as query() but do not return nor store result.
      * Should be used for INSERT, UPDATE, DELETE...
      * @param $query The query.
      * @param $debug If true, it output the query and the resulting table.
      */
    function execute($query)
    {
      @mysql_query($query);
    }
    /** Convenient method for mysql_fetch_object().
      * @param $result The ressource returned by query().
      * @return An ARRAY representing a data row.
      */
    function fetchArray($result){
    if ($result == NULL)
        $result = $this->result;
    if ($result == NULL || mysql_num_rows($result) < 1)
        return NULL;
    else
        return mysql_fetch_assoc($result);
    }

    /** Close the connecion with the database server.
      * It's usually unneeded since PHP do it automatically at script end.
      */
    function close()
    {
      mysql_close($this->link);
    }
    /** Get the number of rows of a query.
      * @param $result The ressource returned by query(). If NULL, the last result returned by query() will be used.
      * @return The number of rows of the query (0 or more).
      */
    function numRows($result = NULL)
    {
      if ($result == NULL)
        return @mysql_num_rows($this->result);
      else
        return mysql_num_rows($result);
    }
}