Database connection through class

Question

i want to crete a class that will contain various general variables and functions to be used throughout the website. One of these things will be a database connection. I am trying the following code:

class Sys {
  public $dbc = new mysqli('localhost', 'user', 'pass', 'db');
  $dbc->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
}

$system = new Sys;

It is giving me a syntax error in the first line of the class... What am i doing wrong? Thanks

Answer 1

//Create mysql.php and paste following code

   <?php

    class MySQL {
     private $set_host;
     private $set_username;
     private $set_password;
     private $set_database;

     public function __Construct($set_host, $set_username, $set_password) {
        $this->host = $set_host;
        $this->username = $set_username;
        $this->password = $set_password;
     $con = mysql_connect($this->host, $this->username, $this->password);
    if (!$con) {
      die("Couldn't connect to the server");
    }
    }
    //end of __construct
    //connect to database
    public function Database($set_database) {
    $this->database = $set_database;
    mysql_query("set character_set_server='utf8'");
    mysql_query("set names 'utf8'");
    mysql_select_db($this->database) or die("Unable to select database");
    }

  //fetch data from any table
    public function fetch_data($sql) {
    $this->sql = $sql;
    $query = mysql_query($this->sql);
    $result = array();
    while ($record = mysql_fetch_array($query)) {
      $result[] = $record;
    }
    return $result;
    }

     //fetch all columns from any table to be used for INSERT INTO.
     public function get_all_columns($table_name) {
    $this->table_name = $table_name;
    $sql = "SHOW COLUMNS FROM $this->table_name";
    $result = mysql_query($sql);
    while ($record = mysql_fetch_array($result)) {
      $fields[] = $record['0'];
    }
    $val = '';
    foreach ($fields as $value) {
      $val .= $value . ',';
    }
    $vals = rtrim($val, ',');
       return $vals;
      }

    //insert data to any table by $_POST or set of variables separated by ,
    public function insert_data($tbl_name, $tbl_value) {
    $this->tbl_name = $tbl_name;    
    $this->tbl_value = $tbl_value;
//use mysql_real_escape_string($tbl_value) to clean & insert data.
    $this->tbl_col = $this->get_all_columns($this->tbl_name);
    $sql = "INSERT INTO $this->tbl_name ($this->tbl_col) VALUES ($this->tbl_value)";
    $query_result = mysql_query($sql);
  }    
//end of insert data

    public function delete_data($del_id, $table_name) {
    $this->del_id = $del_id;
    $this->table_name = $table_name;
    if (isset($this->del_id) && is_numeric($this->del_id) && !empty($this->del_id)) {
      $sql = "DELETE FROM $this->table_name WHERE id=$this->del_id LIMIT 1";
      $del_result = mysql_query($sql);
      $aff_row = mysql_affected_rows();
      return $aff_row;
    }
  }

}
// class ends here

//call class to connect to server and db as well.
$connect = new MySQL('localhost', 'root', '');
$connect->Database('db_name');
?>

 //include file mysql.php and call your class object as :

//fetching data from any table use :
$variable = $connect->fetch_data("SELECT * FROM table_name");
for($i=0;$i<count($variable);$i++){
$result = $variable[$i]['col_name'];     
}
//insert INTO values in any table
$result = $connect->insert_data($tbl_name, $tbl_value);
if($result)
echo 'inserted';
else{
echo 'failed';
}
//delete record from any table
$result = $connect->delete_data($del_id, $table_name)

Answer 2

Try this:

$db = new DB;
$link = $db->connect()->getLink();

class DB {

    public $connection = array();

    public function __construct() {
        return $this;
    }

    public function connect($host=null, $user=null, $pass=null, $database=null) {
        $this->connection = new Connection($host, $user, $pass, $database);
        $this->connection->connect();
        $this->link = $this->connection->getLink();
        if ($this->connection->ping()) {
            if (!is_null($database)) {
                if (!$this->connection->databaseConnect($database)) {
                    throw new\Exception('Unable to connect to the server.');
                }
            }
        }else{
            throw new \Exception('Unable to connect to the server.');
        }
        return $this;
    }

    public function getLink() {
        return $this->link;
    }

}

class Connection {

    protected $chost='localhost';
    protected $cuser='guest';
    protected $cpass='password';
    protected $cdatabase='PROFORDABLE';

    function __construct($host=null, $user=null, $pass=null, $database=null) {

        $host = !is_null($host) ? $host : $this->chost;
        $user = !is_null($user) ? $user : $this->cuser;
        $password = !is_null($pass) ? $pass : $this->cpass;
        $database = !is_null($database) ? $database : $this->cdatabase;

        $this->set('host', $host)->set('user', $user)->set('password', $password)->set('database', $database);
        return $this;
    }

    public function connect() {
        $link = mysqli_connect($this->host->getHost(), $this->user->getUser(), $this->password->getPassword());
        if (!is_object($link)) {
            throw new \Exception("An error has occurred while connecting to the server.");
        }
        $this->link = $link;
    }

    public function databaseConnect($database) {
        if (!mysqli_select_db($this->getLink(), $database)) {
            throw new \Exception("Unable to select the database.");
        }
    }

    public function getLink() {
        return $this->link;
    }

    public function ping() {
        if (mysqli_ping($this->link)) {
            return TRUE;
        }
        return FALSE;
    }

    public function set($name, $param) {
        if (!isset($name) || !isset($param)) return $this;
        $class = __NAMESPACE__.'\\'.ucwords($name);
        $this->$name = new $class($param);
        return $this;
    }

    public function get($name) {
        $getfunc = 'get'.ucwords($name);
        return $this->$name->$getFunc();
    }

}

class Host {
    public function __construct($host) {
        $this->setHost($host);
    }
    public function setHost($host) {
        $this->host = $host;
    }
    public function getHost() {
        return $this->host;
    }
}

class User {

    public function __construct($user) {
        $this->setUser($user);
    }

    public function setUser($user) {
        $this->user = $user;
    }

    public function getUser() {
        return $this->user;
    }

}

class Password {

    public function __construct($password) {
        $this->setPassword($password);
    }

    public function setPassword($password) {
        $this->password = $password;
    }

    public function getPassword() {
        return $this->password;
    }

    public function sha($value) {
        return sha1($value);

    }

}

class guestPassword extends Password {
    const PASSWORD='password';
    public function __construct() {
        return PASSWORD;
    }
}

class Database {

    public function __construct($database) {
        $this->setDatabase($database);
    }

    public function setDatabase($database) {
        $this->database = $database;
    }

    public function getDatabase() {
        return $this->database;
    }

}

class Query {


}

Answer 3

you should do things like this in the constructor. You can only set primitives in the initial declaration. Also you need braces when creating the object.

class Sys {
  private $dbc;
  private $someInteger = 4; // you can do this
  private $someArray = array(); // and this.

  public function __construct()
  {
    $this->dbc = new mysqli('localhost', 'user', 'pass', 'db');
    $this->dbc->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
  }

  public function getDbc()
  {
    return $this->dbc;
  }

}

$system = new Sys();
//$system->getDbc()->soSomethingWithMyDb(); 

i would advise you to read up on using objects: http://php.net/manual/en/language.types.object.php

Answer 4

You can use a wonderful class ezSQL

Answer 5

check out the use of magic methods http://php.net/manual/en/language.oop5.magic.php in php - as mentioned above the __construct method is needed for your class to work. Below is an example from Peter Lavin's book Object Oriented PHP http://objectorientedphp.com/ in chapter 9.

class MySQLConnect {

// Data Members
private $connection;
private static $instances = 0;

// Constructor
public function __construct($hostname, $username, $password) {
    if(MySQLConnect::$instances == 0) {
        $this->connection = new mysqli($hostname, $username, $password);
        // Check for Errors then report them
        if ($this->connection->connect_error) {
            die("Connect Error ($this->connection->connect_errno) $this->connection->connect_error");
        }
        // Set the Class variable $instances to 1
        MySQLConnect::$instances = 1;
    } else {
        $msg = "There's another instance the MySQLConnect Class with a connect open already. Close it";
        die($msg);

    }

}
}

Answer 6

class Sys {
  var $mysqli;
  function DB($database,$server,$user,$pass) {
    $this->mysqli = mysqli_connect($server, $user, $pass, $base) or die('Server connection not possible. '. mysqli_connect_error());
  }
}

include("db.class.mysqli.php"); // db handler class
// Open the base (construct the object):
$db = new Sys(DB_NAME, SERVER_NAME, USER_NAME, PASSWORD);

This is how I do it

Answer 7

You should just declare a public $dbc.

And then have a constructor function function __construct() { ...... } where you initialize it/ set it up.

class Sys {
    public $dbc;

    function __construct() {
        $this->dbc = new mysqli('localhost', 'user', 'pass', 'db');
        $this->dbc->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
    }
}

$system = new Sys;