<?php
/**
* SQL database management to be used by several providers at the same time.
*
* @author Josantonius <hello@josantonius.com>
* @copyright 2017 - 2018 (c) Josantonius - PHP-Database
* @license https://opensource.org/licenses/MIT - The MIT License (MIT)
* @link https://github.com/Josantonius/PHP-Database
* @since 1.0.0
*/
namespace Josantonius\Database\Provider;
/**
* MSSQL database provider.
*/
class MSSQLprovider extends Provider
{
/**
* Database connection.
*
* @param string $host → database host
* @param string $dbUser → database user
* @param string $dbName → database name
* @param string $pass → database password
* @param array $settings → database options
* @param array $settings['port'] → database port
*
* @return object|null → returns the object with the connection or null
*/
public function connect($host, $dbUser, $dbName, $pass, $settings = [])
{
try {
$port = (isset($settings['port'])) ? $settings['port'] : '1433';
$this->conn = mssql_connect($host . ':' . $port, $dbUser, $pass);
mssql_select_db($dbName, $this->conn);
return $this->conn;
} catch (\Exception $e) {
$this->error = $e->getMessage();
return null;
}
}
/**
* Run database queries.
*
* @param string $query → query
* @param string $type → query type: SELECT INSERT UPDATE DELETE CREATE TRUNCATE
*
* @return object|null → returns the object with the connection or null
*/
public function query($query, $type = '')
{
try {
return mssql_query($query, $this->conn);
} catch (\Exception $e) {
$this->error = $e->getMessage();
return null;
}
}
/**
* Execute prepared queries.
*
* @param string $query → query
* @param array $statements → array with prepared parameters
*
* @return object|null → returns the object with the connection or null
*/
public function statements($query, $statements)
{
}
/**
* Create table statement.
*
* @param string $table → table name
* @param array $data → column name and configuration for data types
*
* @return int → 0
*/
public function create($table, $data)
{
$query = 'CREATE TABLE IF NOT EXISTS `' . $table . '` (';
foreach ($data as $column => $value) {
$query .= $column . ' ' . $value . ', ';
}
$query = rtrim(trim($query), ',') . ')'; // Remove final comma
return $this->query($query);
}
/**
* Selec statement.
*
* @param mixed $columns → column/s name
* @param string $from → table name
* @param mixed $where → where clauses
* @param mixed $order → query sort parameters
* @param int $limit → query limiting parameters
* @param array $statements → array with prepared parameters
*
* @return object → query response
*/
public function select($columns, $from, $where, $order, $limit, $statements)
{
$query = 'SELECT ';
$query .= (is_array($columns)) ? implode(', ', $columns) : $columns;
$query .= ' FROM `' . $from . '` ';
$query .= (! is_null($where)) ? ' WHERE ' : '';
$query .= (is_string($where)) ? $where . ' ' : '';
if (is_array($where)) {
foreach ($where as $clause) {
$query .= $clause . ' AND ';
}
$query = rtrim(trim($query), 'AND');
}
$query .= (! is_null($order)) ? ' ORDER BY ' : '';
$query .= (is_string($order)) ? $order . ' ' : '';
if (is_array($order)) {
foreach ($order as $value) {
$query .= $value . ', ';
}
$query = rtrim(trim($query), ',');
}
$query .= (! is_null($limit)) ? ' LIMIT ' : '';
$query .= (is_int($limit)) ? $limit . ' ' : '';
if (! is_null($statements) && is_array($statements)) {
return $this->statements(trim($query), $statements);
}
return $this->query(trim($query), 'SELECT');
}
/**
* Insert into statement.
*
* @param string $table → table name
* @param array $data → column name and value
* @param array $statements → array with prepared parameters
*
* @return object → query response
*/
public function insert($table, $data, $statements)
{
$input = [
'columns' => '',
'values' => ''
];
$query = 'INSERT INTO `' . $table . '` ';
foreach ($data as $column => $value) {
$input['columns'] .= $column . ', ';
$value = (is_null($statements) && is_string($value)) ? "'$value'" : $value;
$input['values'] .= $value . ', ';
}
$query .= '(' . rtrim(trim($input['columns']), ',') . ') ';
$query .= 'VALUES (' . rtrim(trim($input['values']), ',') . ')';
return $this->query($query, 'INSERT');
}
/**
* Update statement.
*
* @param string $table → table name
* @param array $data → column name and value
* @param array $statements → array with prepared parameters
* @param mixed $where → where clauses
*
* @return object → query response
*/
public function update($table, $data, $statements, $where)
{
$query = 'UPDATE `' . $table . '` SET ';
foreach ($data as $column => $value) {
$value = (is_null($statements) && is_string($value)) ? "'$value'" : $value;
$query .= $column . '=' . $value . ', ';
}
$query = rtrim(trim($query), ',');
$query .= (! is_null($where)) ? ' WHERE ' : '';
$query .= (is_string($where)) ? $where . ' ' : '';
if (is_array($where)) {
foreach ($where as $clause) {
$query .= $clause . ' AND ';
}
$query = rtrim(trim($query), 'AND');
}
return $this->query($query, 'INSERT');
}
/**
* Replace a row in a table if it exists or insert a new row in a table if not exist.
*
* @param string $table → table name
* @param array $data → column name and value
* @param array $statements → array with prepared parameters
*
* @return object → query response
*/
public function replace($table, $data, $statements)
{
$columns = array_keys($data);
$columnIdName = $columns[0];
$id = array_shift($data);
$where = $columnIdName . ' = ' . $id;
$result = $this->select($columns, $table, $where, null, 1, $statements);
if ($this->rowCount($result)) {
return $this->update($table, $data, $statements, $where);
}
return $this->insert($table, $data, $statements);
}
/**
* Delete statement.
*
* @param string $table → table name
* @param array $statements → array with prepared parameters
* @param mixed $where → where clauses
*
* @return object → query response
*/
public function delete($table, $statements, $where)
{
$query = 'DELETE FROM `' . $table . '` ';
$query .= (! is_null($where)) ? ' WHERE ' : '';
$query .= (is_string($where)) ? $where . ' ' : '';
if (is_array($where)) {
foreach ($where as $clause) {
$query .= $clause . ' AND ';
}
$query = rtrim(trim($query), 'AND');
}
return $this->query($query, 'INSERT');
}
/**
* Truncate table statement.
*
* @param string $table → table name
*
* @return int → 0
*/
public function truncate($table)
{
$query = 'TRUNCATE TABLE `' . $table . '`';
return $this->query($query);
}
/**
* Drop table statement.
*
* @param string $table → table name
*
* @return int → 0
*/
public function drop($table)
{
$query = 'DROP TABLE IF EXISTS `' . $table . '`';
return $this->query($query);
}
/**
* Process query as object or numeric or associative array.
*
* @param object $response → query result
* @param string $result → result as an object or array
*
* @return object|array → object or array with results
*/
public function fetchResponse($response, $result)
{
if ($response) {
$data = [];
while ($row = mssql_fetch_array($response)) {
$data[] = $row;
}
return $data;
}
return false;
}
/**
* Get the last id of the query object.
*
* @return int → last row id modified or added
*/
public function lastInsertId()
{
}
/**
* Get rows number.
*
* @param object $response → query result
*
* @return int → rows number in query object
*/
public function rowCount($response)
{
}
/**
* Get errors.
*
* @return string → get the message if there has been any error
*/
public function getError()
{
return $this->error;
}
/**
* Check database connection state.
*
* @return bool true|false → check the connection and return true or false
*/
public function isConnected()
{
return ! is_null($this->conn);
}
/**
* Close/delete database connection.
*/
public function kill()
{
$this->conn = null;
}
}