PHP OOP MySQL Programming

Question

I am a beginner in PHP programming and would like help with a little question. Please take a look at the code below:

PHP Code

<?php
class Account
{
  public function register()
  {
    $db_link = mysql_connect("localhost","root","");  // Create Connection
    if (!$db_link)  // Check connection
    {
      die(mysql_error());
    }

    mysql_close($db_link); // Close Connection
  }

  public function login()
  {
    $con = mysql_connect("localhost","root","")  // create connection
    if (!$con)  // create connection
    {
      die(mysql_error());
    }
    mysql_close($con); //close connection
  }

}
?>

My question is if creating individual db links for every single one of the object's methods is the best way to go? Is there a better or alternative way to do this? Hopefully I've explained well enough.

Would the following be correct?

$x = new Account("localhost", "root", "");

-and x would have its own connection...and then close when its done?

Just use `mysql_connect()` and `mysql_close()` at the beginning and at the end of your whole program. There is no need to instantiate a connection at every method. — ldiqual, Jan 02 '12 at 23:20
@idiqual You should not be encouraging the use of `mysql_` at all, particularly for somebody just coming into the language and is starting a new project. — Charles Sprayberry, Jan 02 '12 at 23:23
if you want to keep db to the Accounting class connect in your `__construct` and implement `__destruct` method to destroy connection when object is destroyed. — dm03514, Jan 02 '12 at 23:24
Use singleton pattern. Example http://stackoverflow.com/questions/8685896/check-if-a-specific-mysql-connection-already-exists-during-a-php-script/8685944#8685944 — Shiplu Mokaddim, Jan 02 '12 at 23:33

Charles Sprayberry · Accepted Answer · 2012-01-02T23:28:34.593

I would not advise creating your database connections this way. Create one connection and inject that into the object using it. You should not need to create a new connection for every object.

Code example:

$connection = new mysqli('localhost', 'user', 'password');

$Account = new Account($connection);

Would need to change Account to look like:

class Account {

    protected $connection;

    public function __construct(mysqli $connection) {
        $this->connection = $connection;
    }

    public function register() {
        // use $this->connection for db
    }

    public function login() {
        // use $this->connection for db
    }

}

I would also suggest that you take a look at the php.net docs about choosing a MySQL API. If you really want to use OOP with PHP and MySQL you will need to swap over to mysqli or PDO as the API you are using does not truly support an OOP interface.

@GabrielSantos No, I didn't close it on purpose. What if there's another object that needs to use that connection? Then we have to open the connection again. The code that created the connection object should be responsible for closing the connection. — Charles Sprayberry, Jan 03 '12 at 00:04

score 3 · Answer 2 · answered Jan 02 '12 at 23:33

3

I suggest:

public function __construct(mysqli $connection) {
    $this->connection = $connection;
    if(!$this->$connection) {
        die(mysql_error());
    }
}

public function __destruct() {
    mysql_close($this->$connection);
}

answered Jan 02 '12 at 23:33

Gabriel Santos

4,934
2
43
74

I'm not sure I'd recommend using die in this (or any OO) situation. – cmbuckley Jan 02 '12 at 23:56
@cbuckley It is only for sample purpose. – Gabriel Santos Jan 03 '12 at 00:00
My only concern was that it might be a bad example to set to a beginner PHP programmer, OOP or otherwise. – cmbuckley Jan 03 '12 at 00:27
No, is not. die have not relation to OOP. – Gabriel Santos Jan 03 '12 at 00:40
1

Exception handling is an integral part of OOP. die() immediately produces fatal errors. See http://stackoverflow.com/questions/3845494/php-oop-exceptions-or-die – cmbuckley Jan 03 '12 at 00:52
I know, but, I can't do a try..catch block ever. One time I will need to call die(); – Gabriel Santos Jan 03 '12 at 01:04
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6325/discussion-between-cbuckley-and-gabriel-santos) – cmbuckley Jan 03 '12 at 01:50

score 0 · Answer 3 · answered Jan 02 '12 at 23:31

You can use some codes like as:

$db_link = mysql_connect("localhost","root","");  // Create Connection
if (!$db_link)  // Check connection 
{
  die(mysql_error());
}
mysql_select_db("db_name");
$q=mysql_query("SELECET * FROM table_name LIMIT 1");

Other queries would be here

mysql_close($db_link); // Close Connection

Creating single connection at each page is enough. more than one connection or disconnecting from sql server and loginning again may cause reduced performance.

score -1 · Answer 4 · edited May 23 '17 at 12:06

-1

You can use a static member and all your objects will share one database connection.

See also PHP - a DB abstraction layer use static class vs singleton object?

edited May 23 '17 at 12:06

Community

1
1

answered Jan 02 '12 at 23:23

Jordan

2,708
4
22
35

1

I wouldn't use neither (static class or singleton). – PeeHaa Jan 02 '12 at 23:27
1

Downvote for singleton. No reason to introduce a database connection into the global scope with static – Charles Sprayberry Jan 02 '12 at 23:36
@CharlesSprayberry Agree again. – Gabriel Santos Jan 03 '12 at 00:01

score -1 · Answer 5 · answered Jan 02 '12 at 23:24

-1

If you wanted to shrink it and keep it neater/more manageable you could put the mysql connect code into it's own method and call it like so:

<?php
class Account
{
  private $connection;

  private function connect()
  {
    $this->$connection = mysql_connect("localhost","root","");  // Create Connection
  }

  public function register()
  {
    $this->connect();
    if (!$this->$connection)  // Check connection
    {
      die(mysql_error());
    }

    mysql_close($this->$connection); // Close Connection
  }

  public function login()
  {
    $this->connect();
    if (!$this->$connection)  // create connection
    {
      die(mysql_error());
    }
    mysql_close($this->$connection); //close connection
  }

}
?>

answered Jan 02 '12 at 23:24

George Reith

13,132
18
79
148

1

Downvoted because ultimately you're still creating the database connection every time a function is called. – Charles Sprayberry Jan 02 '12 at 23:47
@CharlesSprayberry Agree with you. – Gabriel Santos Jan 03 '12 at 00:00
@CharlesSprayberry I misinterpreted the original question – George Reith Jan 03 '12 at 00:19

PHP OOP MySQL Programming

5 Answers5

Linked