Php object connect to database

PHP OOP database connect

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:

I don’t think this lends itself to good OO design. Consider that the class you have written is not particularly generic, since your database connection details are just hard-coded constants. Also, the only SQL statement that this class can execute is SELECT * FROM users . Is this the intention? I’d consider extending this class with other classes that contain specific SQL, or making the SQL function accept a SQL query string. Also, your database class should not be doing the view rendering (in fetch_array). en.wikipedia.org/wiki/SOLID_(object-oriented_design)

6 Answers 6

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.

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

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 repeat the functions it provides and 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 $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.

$injector = new \Auryn\Injector(); 

$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, ], ]); 

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

Autoloading

Assuming you have used composer, you can make use of its 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.

Источник

PHP Connect to MySQL

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the «i» stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be «Whatever you like».

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

PDO Installation

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
echo «Connected successfully»;
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
echo «Connected successfully»;
?>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;

try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo «Connected successfully»;
> catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
>
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Источник

OOP database connect/disconnect class

I have just started learning the concept of Object oriented programming and have put together a class for connecting to a database, selecting database and closing the database connection. So far everything seems to work out okay except closing the connection to the database.

 class Database < private $host, $username, $password; public function __construct($ihost, $iusername, $ipassword)< $this->host = $ihost; $this->username = $iusername; $this->password = $ipassword; > public function connectdb()< mysql_connect($this->host, $this->username, $this->password) OR die("There was a problem connecting to the database."); echo 'successfully connected to database
'; > public function select($database)< mysql_select_db($database) OR die("There was a problem selecting the database."); echo 'successfully selected database
'; > public function disconnectdb()< mysql_close($this->connectdb()) OR die("There was a problem disconnecting from the database."); > > $database = new database('localhost', 'root', 'usbw'); $database->connectdb(); $database->select('msm'); $database->disconnectdb(); 

Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in F:\Programs\webserver\root\oop\oop.php on line 53 

I’m guessing it isn’t as simple as placing the connectdb method within the parenthesis of the mysql_close function but can’t find the right way to do it. Thanks

6 Answers 6

I would add a connection/link variable to your class, and use a destructor. That will also save you from haveing to remember to close your connection, cause it’s done automatically.
It is the $this->link that you need to pass to your mysql_close().

class Database < private $link; private $host, $username, $password, $database; public function __construct($host, $username, $password, $database)< $this->host = $host; $this->username = $username; $this->password = $password; $this->database = $database; $this->link = mysql_connect($this->host, $this->username, $this->password) OR die("There was a problem connecting to the database."); mysql_select_db($this->database, $this->link) OR die("There was a problem selecting the database."); return true; > public function query($query) < $result = mysql_query($query); if (!$result) die('Invalid query: ' . mysql_error()); return $result; >public function __destruct() < mysql_close($this->link) OR die("There was a problem disconnecting from the database."); > > 

query("SELECT * FROM students"); while ($row = mysql_fetch_assoc($result)) < echo "First Name: " . $row['firstname'] ."
"; echo "Last Name: " . $row['lastname'] ."
"; echo "Address: " . $row['address'] ."
"; echo "Age: " . $row['age'] ."
"; echo "
"; > ?> 

Edit:
So people can actually use the class, I added the missing properties/methods.
The next step would be to expand on the query method, to include protection against injection, and any other helper functions.
I made the following changes:

• Added the missing private properties
• Added __construct($host, $username, $password, $database)
• Merged connectdb() and select() into __construct() saving an extra two lines of code.
• Added query($query)
• Example Usage

Please if I made a typo or mistake, leave a constructive comment, so I can fix it for others.

edit 23/06/2018

As pointed out mysql is quite outdated and as this question still receives regular visits I thought I’d post an updated solution.

class Database < private $mysqli; private $host, $username, $password, $database; /** * Creates the mysql connection. * Kills the script on connection or database errors. * * @param string $host * @param string $username * @param string $password * @param string $database * @return boolean */ public function __construct($host, $username, $password, $database)< $this->host = $host; $this->username = $username; $this->password = $password; $this->database = $database; $this->mysqli = new mysqli($this->host, $this->username, $this->password) OR die("There was a problem connecting to the database."); /* check connection */ if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >$this->mysqli->select_db($this->database); if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >return true; > /** * Prints the currently selected database. */ public function print_database_name() < /* return name of current default database */ if ($result = $this->mysqli->query("SELECT DATABASE()")) < $row = $result->fetch_row(); printf("Selected database is %s.\n", $row[0]); $result->close(); > > /** * On error returns an array with the error code. * On success returns an array with multiple mysql data. * * @param string $query * @return array */ public function query($query) < /* array returned, includes a success boolean */ $return = array(); if(!$result = $this->mysqli->query($query)) < $return['success'] = false; $return['error'] = $this->mysqli->error; return $return; > $return['success'] = true; $return['affected_rows'] = $this->mysqli->affected_rows; $return['insert_id'] = $this->mysqli->insert_id; if(0 == $this->mysqli->insert_id) < $return['count'] = $result->num_rows; $return['rows'] = array(); /* fetch associative array */ while ($row = $result->fetch_assoc()) < $return['rows'][] = $row; >/* free result set */ $result->close(); > return $return; > /** * Automatically closes the mysql connection * at the end of the program. */ public function __destruct() < $this->mysqli->close() OR die("There was a problem disconnecting from the database."); > > 

query("SELECT * FROM students"); if(true == $result['success']) < echo "Number of rows: " . $result['count'] ."
"; foreach($result['rows'] as $row) < echo "First Name: " . $row['firstname'] ."
"; echo "Last Name: " . $row['lastname'] ."
"; echo "Address: " . $row['address'] ."
"; echo "Age: " . $row['age'] ."
"; echo "
"; > > if(false == $result['success']) < echo "An error has occurred: " . $result['error'] ."
"; > ?> 

Источник