Reusing a mysql connection in oop php

Question

Solution taken from comment so I can't accept an answer for this to be closed. But I did post the actual solution that works for me below

I'm new to OOP and I just can't figure out, even after reading through quite few examples, how use the same mysql connection without using $GLOBALS.

If someone can explain it like I'm a two year old that would be super helpful.

This is my connection file.

$hostname = 'hostname';
$username = 'db';
$password = 'password';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=db", $username, $password);
}
 catch(PDOException $e)
    {
    echo $e->getMessage();
 }

but then to use this in a class or a function I do this:

class basic {

function simple($id) {

$query = $GLOBALS['dbh']->query("SELECT * FROM table WHERE id = $id");
$row = $query->fetch(PDO::FETCH_OBJ);
$thing = $row->partoftable;
echo $thing;
 }
}

$first = new basic();
$first->simple(12);

This of course will return what I'm looking for the $thing with the id of 12. But how do I do this without the GLOBALS['dbh'] to connect to the db?

Also feel free to rip anything else apart but just keep in mind this was the easiest example of what I'm talking about.

Thanks in advance.

This is the solution that works for me based on the comment below.

class basic {

function __construct($dbh)
    {
        $this->dbh = $dbh;
    }

function simple($id) {

$query = $this->dbh->query("SELECT * FROM table WHERE id = $id");
$row = $query->fetch(PDO::FETCH_OBJ);
$thing = $row->partoftable;
echo $thing;
 }
}

$first = new basic($dbh);
$first->simple(12);

Thanks. hope this helps someone else.

Basic shoud know about `$dbh`, passit through `__constructor` read more [Dependecy Injection](http://fabien.potencier.org/article/11/what-is-dependency-injection) — cske, Apr 24 '15 at 11:09
@cske Thanks! that was so simple I just couldn't initially get my brain around it. I just have to pass it through in the $first = new basic($dbh) and then use the __constructor to make it happen in the class. Just trying to be more explicit in case someone else is having this problem. Thanks again! — mikescorelle, Apr 24 '15 at 11:27

score 2 · Answer 1 · answered Apr 24 '15 at 11:12

class basic {

var $CONNECTION;

function __construct($dbh) {
    $this->CONNECTION = $dbh;
  }

function simple($id) {
$conn = $this->CONNECTION;
$query = $conn->prepare("SELECT * FROM table WHERE id = $id");
$query->execute();
$row = $query->fetch(PDO::FETCH_OBJ);
$thing = $row->partoftable;
echo $thing;
 }
}

//class ends you can use thae class like this

$hostname = 'hostname';
$username = 'db';
$password = 'password';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=db", $username, $password);
}
 catch(PDOException $e)
    {
    echo $e->getMessage();
 }

$first = new basic($dbh);
$first->simple(12);

score 0 · Answer 2 · answered Apr 24 '15 at 11:20

You can create a class for database connection :

class MysqlDB
{
    private $conn;
    public function __construct($hostName, $userName, $passWord, $databaseName)
    {
        $this->conn = new PDO("mysql:host=$hostName;dbname=$databaseName", $userName, $passWord);
    }

    public function query($id)
    {
        //This is just a sample query
        $this->conn->query("SELECT * FROM table WHERE id = $id");
        return $query->fetch(PDO::FETCH_OBJ);
    }
}

And then you can use in another class like:

class basic {
  private $dbConn;
  function __construct(){
      $dbConn = new MysqlDB('hostName', 'username', 'password', 'database')
  }
  function simple($id) {

     $row = $dbConn->query($id);
     $thing = $row->partoftable;
     echo $thing;
 }
}

You can also create a database connection in common class and extend it with you class

You are creating a new instance every time when instantiating a class. This is the opposite of what OP is trying to do. — PeeHaa, Apr 24 '15 at 11:53

bytesized · Answer 3 · 2015-04-24T11:15:04.850

-1

I like this solution:

class db_connection
{
    public static $sql_object = NULL;

    public function __construct()
    {
        if ($sql_object === NULL)
        {
            // Initialize self::$sql_object
        }
    }
}

Then you can use it with:

$db = new db_connection();
// Do something with $db->sql_object

Since $sql_object is static, it will be initialized only once, no matter how many times you use new db_connection().

edited Apr 24 '15 at 11:15

answered Apr 24 '15 at 11:12

bytesized

1,502
13
22

Having a global `db_connection` thing is not really OOP. – PeeHaa Apr 24 '15 at 11:14
`$db_connection` is not global. I suppose you could consider `$sql_object` to be global in the sense that it is static, but I don't think using static members violates OOP. – bytesized Apr 24 '15 at 11:20

score -1 · Answer 4 · answered Apr 24 '15 at 11:29

    <?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'db');
class DB_con {
    function __construct()
    { 
            $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die('localhost connection problem'.mysql_error());

                mysql_select_db(DB_NAME, $conn); 
    } 

    public function insert($fname,$lname,)
    { 
        $res = mysql_query("INSERT users(first_name,last_name,) VALUES('$fname','$lname')");
        return $res;
    }
    public function select($id)
    { 
        $res=mysql_query("SELECT * FROM users WHERE id = $id");
        return $res;
    }
} ?>

Reusing a mysql connection in oop php

4 Answers4