Php class with mysqli

Mysqli in php class [duplicate]

Hi I am trying to rebuild my code with mysqli instead of mysql but I have a problem with mysql in a php class:

$db = new mysqli('localhost','user','password','dbname'); require_once('classes/some_class.php'); $some_class = new some_class(); 

 class some_class < function __construct() < $db->query('blablabla'); > > 

This ist not working but: If I add $db = new . in some_class.php it works! So some_class.php don’t know the database connection :/

2 Answers 2

Your variable is out of scope, some_class has no idea what $db is. You need to pass $db as an argument. You can do this when instantiating the class.

$db = new mysqli('localhost','user','password','dbname'); require_once('classes/some_class.php'); $some_class = new some_class($db); //Pass $db here -------------^ to the constructor 

class some_class < function __construct($db) < $db->query('blablabla'); > > //You receive $db here ---^ 

some_class() is dependent upon your $db.

pass the dependency in as an argument:

$db = new mysqli('localhost','user','password','dbname'); // that could be in a different file class some_class < private $db; function __construct($db) < $this->db=$db; > // eg - do a query function getStuff($qry)< $this->db->query($qry); > > 

As you said, have someclass instantiate the db connection

There are pros and cons concerning each. There are other ways too. If you want to really understand the implications of each, I suggest you find yourself at least one really good OOP book. This stuff is clever but finding the right answer for each situation does not come cheap.

Источник

PHP Class MySQLi — СУБД MySQL

На сегодняшний день существует множество статей с готовыми решениями, как работать с СУБД MySQL. Однако, почти в каждой статье приводятся примеры процедурного вызова функций. Начиная с php-версии 5.5.0 расширение MySQL будет удалено из сборки и вообще, это расширение уже устарело. Вместо него будем использовать расширение MySQLi в ООП.
Данная статья является готовым решением для работы с СУБД.

При разработке очередного проекта, я столкнулся с тем, что в каждом методе следующего написанного класса с обращением к СУБД, мне нужно было обращаться к классу работы с СУБД, для очередного select, insert, update и т.п., то есть тягать в функции параметра global с объявленной переменной вне класса. А устанавливать новое соединение с базой — слишком затратно, да и не выгодно, только ресурсы системы тратить. И решил написать переписать класс работы с базой, с сохранением экземпляра класса, чтобы обращаться сразу напрямую к методам класса из любой области видимости переменной.

Для просмотра исходника смотрим под кат.

Для инициализации класса достаточно создать экземпляр класса.
Переменную $db не следует удалять после объявления, т.к. ее адрес используется для дальнейшей работы со статическими методами класса.

 catch (Exception $e) < exit($e->getMessage()); > ?> 

Ниже приведу содержимое класса DB.class.php

connect_error) throw new Exception("Connect failed: %s", $mysqli->connect_error); self::$mysqli = &$mysqli; self::$mysqli->query('SET NAMES utf8 COLLATE utf8_general_ci'); > public static function GetRows($rows, $single = false) < $result = array(); if ($rows === false) return $result; if ($single) return $rows->fetch_assoc(); while ($row = $rows->fetch_assoc()) array_push($result, $row); $rows->free(); return $result; > public static function Select($sql, $single = false) < $result = self::$mysqli->query($sql); return self::$db->GetRows($result, $single); > public static function Update($data, $table, $where) < $sets = ''; foreach ($data as $column =>$value) < $sets .= $sets ? ', ' : ''; $sets .= "`$column` = '$value'"; >$sql = "UPDATE $table SET $sets WHERE $where"; self::$mysqli->query($sql); > public static function Insert($data, $table) < $columns = ""; $values = ""; foreach ($data as $column =>$value) < $columns .= $columns ? ', ' : ''; $columns .= "`$column`"; $values .= $values ? ', ' : ''; $values .= "'$value'"; >$sql = "INSERT INTO $table ($columns) VALUES ($values)"; self::$mysqli->query($sql); return self::$mysqli->insert_id; > public static function CountRows($table, $request = false) < $sql = "SELECT COUNT(*) FROM $table "; $sql .= $request ? $request : ''; $result = self::$mysqli->query($sql); $count = $result->fetch_array(); $result->free(); return $count[0]; > public static function Query($sql) < return self::$mysqli->query($sql); > public static function Delete($table, $where) < $sql = "DELETE FROM $table WHERE $where"; self::$mysqli->query($sql); > public static function Close() < self::$mysqli->close(); > function __destruct() < self::$db->Close(); > > ?> 

Источник

Creating a MySqli Database Class in PHP

I have been working with PHP PDO since the start of PHP 5.x. But in this post we will be using MySqli as an example.

You will learn how to create a basic OOP MySQLi class using PHP. You’ll also learn how to bind the MySQLi parameters dynamically.

Quick Explanation

What is MySQLi? It’s derived from the abbreviation «My» (Co-founder Michael Widenius’ daughter), «SQL» (Structured Query Language), and «i» (Improved version from MySQL)

Here’s a brief explanation of some MySQLi codes that we will be using.

• host [Required] — The Data Source Name that contains information required to connect to the database
• username [Required] — MySQL Username
• password [Required] — MySQL Password
• databasename [Required] — MySQL Database name

• statement [Required] — An SQL statement to execute
  Example:
  $instance->prepare(‘Select * from tableName’);
  Returns a mysqli_stmt (MySQLi Statement)

• Returns Boolean

• types [Required] — String types of corresponding columnValues

So we have 4 ? , this will then be replaced by the following in our $stmt->bind_param
i which is our 1
s which is our Hello
s which is our World
and d which is our 12.34

mixed_array
Arguments to pass dynamically to fn

Example: call_user_func_array(‘str_replace’,[«World»,»User»,»Hello World»]); is also the same as str_replace(«World»,»User»,»Hello World»);

Creating the Class:

Preparing our Class name, variables and functions. class DatabaseClass < private $connection = null; // this function is called everytime this class is instantiated public function __construct()< >// Insert a row/s in a Database Table public function Insert( ) < >// Select a row/s in a Database Table public function Select( ) < >// Update a row/s in a Database Table public function Update( ) < >// Remove a row/s in a Database Table public function Remove( ) < >// execute statement private function executeStatement( ) < >> Now that we have a simple design for our Database class. Lets fill the functions with some codes.

Establish the MySQL connection in the costructor // this function is called everytime this class is instantiated public function __construct( $dbhost = «localhost», $dbname = «myDataBaseName», $username = «root», $password = «»)< try< $this->connection = new mysqli($dbhost, $username, $password, $dbname); if( mysqli_connect_errno() ) < throw new Exception("Could not connect to database."); >>catch(Exception $e)< throw new Exception($e->getMessage()); > >
The constructor will have 4 parameters

• $dbhost — The database host.
• $dbname — The database name.
• $username The database User.
• $password — The database password for the User.

A Function that will execute all statements // execute statement private function executeStatement( $query = «» , $params = [] )< try< $stmt = $this->connection->prepare( $query ); if($stmt === false) < throw New Exception("Unable to do prepared statement: " . $query); >if( $params ) < call_user_func_array(array($stmt, 'bind_param'), $params ); >$stmt->execute(); return $stmt; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > > We will be passing our SQL Statements to this function (Insert, Select, Update and Remove).
Returns mysqli_stmt or throws an exception if it get’s an error.

The challenge here is that we have to make bind_param in our class dynamically accept any number of parameters.
We can do this by using call_user_func_array(); of course we have to also make our parameters to look like the bind_param itself.

Example: $stmt->bind_param(«issd» , 1 , «Hello» , «World» , 12.34)
Then we will pass to this function DatabaseClass::executeStatement( «Insert . values ( ?, ?, ?, ? )», [ «issd» , 1 , «Hello» , «World» , 12.34 ] )

Insert Function // Insert a row/s in a Database Table public function Insert( $query = «» , $params = [] )< try< $stmt = $this->executeStatement( $query , $params ); $stmt->close(); return $this->connection->insert_id; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > Insert will add a row and will return an integer of the last ID inserted or throws an exception if it get’s an error.

Select Function // Select a row/s in a Database Table public function Select( $query = «» , $params = [] )< try< $stmt = $this->executeStatement( $query , $params ); $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close(); return $result; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > Select will return all row/s or throws an exception if it get’s an error.

Update Function // Update a row/s in a Database Table public function Update( $query = «» , $params = [] )< try< $this->executeStatement( $query , $params )->close(); >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > Update will update a row/s or throws an exception if it get’s an error.

Remove Function // Remove a row/s in a Database Table public function Remove( $query = «» , $params = [] )< try< $this->executeStatement( $query , $params )->close(); >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > Remove will remove a row/s or throws an exception if it get’s an error.

Our Database Class example:

class DatabaseClass< private $connection = null; // this function is called everytime this class is instantiated public function __construct( $dbhost = "localhost", $dbname = "myDataBaseName", $username = "root", $password = "")< try< $this->connection = new mysqli($dbhost, $username, $password, $dbname); if( mysqli_connect_errno() ) < throw new Exception("Could not connect to database."); >>catch(Exception $e)< throw new Exception($e->getMessage()); > > // Insert a row/s in a Database Table public function Insert( $query = «» , $params = [] )< try< $stmt = $this->executeStatement( $query , $params ); $stmt->close(); return $this->connection->insert_id; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > // Select a row/s in a Database Table public function Select( $query = «» , $params = [] )< try< $stmt = $this->executeStatement( $query , $params ); $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close(); return $result; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > // Update a row/s in a Database Table public function Update( $query = «» , $params = [] )< try< $this->executeStatement( $query , $params )->close(); >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > // Remove a row/s in a Database Table public function Remove( $query = «» , $params = [] )< try< $this->executeStatement( $query , $params )->close(); >catch(Exception $e)< throw New Exception( $e->getMessage() ); > return false; > // execute statement private function executeStatement( $query = «» , $params = [] )< try< $stmt = $this->connection->prepare( $query ); if($stmt === false) < throw New Exception("Unable to do prepared statement: " . $query); >if( $params ) < call_user_func_array(array($stmt, 'bind_param'), $params ); >$stmt->execute(); return $stmt; >catch(Exception $e)< throw New Exception( $e->getMessage() ); > > >

Using the Database Class:

Create/Instantiate the Database Class. $db = new Database( «MySQLHost», «myDatabaseName», «myUserName», «myUserPassword» ); Insert Example $id = $db->Insert(«Insert into `TableName`( `column1` , `column2`) values ( ? , ? )», [ ‘ss’, ‘column1 Value’, ‘column2 Value’, ]); Select Example $db->Select(«Select * from TableName»); Update Example $db->Update(«Update TableName set `column1` = ? where ‘si’, ‘a new column1 value’, 1 ]); Remove Example $db->Remove(«Delete from TableName where ‘i’ , 1 ]);

Tips:

• Minimize connections to your server.

Take this as an example: for( $x = 1; $x Select(«Select * from TableName where // do something with $data > The above code will create 1000 connections and this could lead to your server to slowing down.

A better way to do this is to create the DatabaseClass object before the looping: $db = new Database( «MySQLHost», «myDatabaseName», «myUserName», «myUserPassword» ); for( $x = 1; $x Select(«Select * from TableName where // do something with $data > The above code will create 1 connection and will use it inside the loop.

I also have created a PHP PDO version of this class. You can read how i created it here PHP OOP Database Class Example

Источник