Как написать вспомогательный класс для другой операции выбора?

Ребята, я хочу другую операцию «выбора» в соответствии с моими потребностями, но мне просто нужна одна вспомогательная функция для всех операций выбора. Как мне ее получить?

Это мой вспомогательный класс Database.php

<?php
class Database
{
public $server = "localhost";
public $user = "root";
public $password = "";
public $database_name = "employee_application";
public $table_name  = "";
public $database_connection = "";
public $class_name = "Database";
public $function_name = "";
//constructor
public function __construct(){
//establishes connection to database
$this->database_connection = new mysqli($this->server, $this->user, $this->password, $this->database_name);
if($this->database_connection->connect_error)
die ("Connection failed".$this->database_connection->connect_error);
}
//destructor
public function __destruct(){
//closes database connection
if(mysqli_close($this->database_connection))
{
$this->database_connection = null;
}
else
echo "couldn't close";
}

public function run_query($sql_query)
{
$this->function_name = "run_query";
try{
if($this->database_connection){
$output = $this->database_connection->query($sql_query);
if($output){
$result["status"] = 1;
$result["array"] = $output;
}
else{
$result["status"] = 0;
$result["message"] = "Syntax error in query";
}
}
else{
throw new Exception ("Database connection error");
}
}
catch (Exception $error){
$result["status"] = 0;
$result["message"] = $error->getMessage();
$this->error_table($this->class_name, $this->function_name, "connection error", date('Y-m-d H:i:s'));
}
return $result;
}
public function get_table($start_from, $per_page){
$this->function_name = "get_table";
$sql_query = "select * from $this->table_name LIMIT $start_from, $per_page";
return $this->run_query($sql_query);
}
}
?>

В приведенном выше коде функция get_table выполняет основную операцию выбора ….
Теперь я хочу, чтобы функция get_table выполняла все перечисленные ниже операции,

$sql_query = "select e.id, e.employee_name, e.salary, dept.department_name, desi.designation_name, e.department, e.designation
from employees e
LEFT OUTER JOIN designations desi ON e.designation = desi.id
LEFT OUTER JOIN departments dept ON e.department = dept.id
ORDER BY e.employee_name
LIMIT $start_from, $per_page";$sql_query = "select id, designation_name from designations ORDER BY designation_name";
$sql_query = "select * from departments";
$sql_query = "select * from departments Limit 15,10";

Так как преодолеть эту проблему, кто-нибудь может мне помочь?

0

Решение

Если вы немного расширите свой класс, вы сможете достичь того, что ищете. Что-то вроде этого:

<?php
class   Database
{
public      $sql;
public      $bind;
public      $statement;
public      $table_name;

protected   $orderby;
protected   $set_limit;
protected   $function_name;
protected   $sql_where;
protected   $i;

protected   function reset_class()
{
// reset variables
$this->sql              =   array();
$this->function_name    =   false;
$this->table_name       =   false;
$this->statement        =   false;
$this->sql_where        =   false;
$this->bind             =   array();
$this->orderby          =   false;
$this->set_limit        =   false;
}

protected   function run_query($statement)
{
echo (isset($statement) && !empty($statement))? $statement:"";
}

public function get_table($start_from = false, $per_page = false)
{
// Compile the sql into a statement
$this->execute();
// Set the function if not already set
$this->function_name    =   (!isset($this->function_name) || isset($this->function_name) && $this->function_name == false)? "get_table":$this->function_name;
// Set the statement
$this->statement        =   (!isset($this->statement) || isset($this->statement) && $this->statement == false)? "select * from ".$this->table_name:$this->statement;

// Add on limiting
if($start_from != false && $per_page != false) {
if(is_numeric($start_from) && is_numeric($per_page))
$this->statement    .=  " LIMIT $start_from, $per_page";
}

// Run your query
$this->run_query($this->statement);
// Reset the variables
$this->reset_class();

return $this;
}

public  function select($value = false)
{
$this->sql[]    =   "select";

$this->function_name = "get_table";

if($value != false) {
$this->sql[]    =   (!is_array($value))? $value:implode(",",$value);
}
else
$this->sql[]    =   "*";

return $this;
}

public  function from($value = false)
{
$this->sql[]    =   "from";
$this->sql[]    =   "$value";

return $this;
}

public  function where($values = array(), $not = false, $group = false,$operand = 'and')
{

if(empty($values))
return $this;

$this->sql_where    =   array();

if(isset($this->sql) && !in_array('where',$this->sql))
$this->sql[]        =   'where';

$equals             =   ($not == false || $not == 0)? "=":"!=";

if(is_array($values) && !empty($values)) {
if(!isset($this->i))
$this->i = 0;foreach($values as $key => $value) {

$key    =   trim($key,":");if(isset($this->bind[":".$key])) {
$auto   =   str_replace(".","_",$key).$this->i;
//  $preop  =   $operand." ";
}
else {
//  $preop  =   "";
$auto   =   str_replace(".","_",$key);
}

$this->bind[":".$auto]  =   $value;
$this->sql_where[]      =   $key." $equals ".":".$auto;
$this->i++;
}

if($group == false || $group == 0)
$this->sql[]    =   implode(" $operand ",$this->sql_where);
else
$this->sql[]    =   "(".implode(" $operand ",$this->sql_where).")";
}
else {
$this->sql[]        =   $values;
}

if(is_array($this->bind))
asort($this->bind);

return $this;
}

public  function limit($value = false,$offset = false)
{
$this->set_limit        =   "";

if(is_numeric($value)) {

$this->set_limit        =   $value;

if(is_numeric($offset))
$this->set_limit    =   $offset.", ".$this->set_limit;
}

return $this;
}

public  function order_by($column = array())
{
if(is_array($column) && !empty($column)) {
foreach($column as $colname => $orderby) {
$array[]    =   $colname." ".str_replace(array("'",'"',"+",";"),"",$orderby);
}
}
else
$array[]    =   $column;

$this->orderby  =   implode(", ",$array);

return $this;
}

public  function execute()
{
$limit              =   (isset($this->set_limit) && !empty($this->set_limit))? " LIMIT ".$this->set_limit:"";
$order              =   (isset($this->orderby) && !empty($this->orderby))? " ORDER BY ".$this->orderby:"";

$this->statement    =   (is_array($this->sql))? implode(" ", $this->sql).$order.$limit:"";

return $this;
}

public  function use_table($table_name = 'employees')
{
$this->table_name   =   $table_name;
return $this;
}
}

Использовать:

    // Create instance
$query  =   new Database();

// Big query
$query  ->select(array("e.id", "e.employee_name", "e.salary", "dept.department_name", "desi.designation_name", "e.department", "e.designation"))
->from("employees e LEFT OUTER JOIN designations desi ON e.designation = desi.id LEFT OUTER JOIN departments dept ON e.department = dept.id")
->order_by(array("e.employee_name"=>""))
->limit(1,5)->get_table();

// More advanced select
$query  ->select(array("id", "designation_name"))
->from("designations")
->order_by(array("designation_name"=>""))
->get_table();

// Simple select
$query  ->use_table("departments")
->get_table();

// Simple select with limits
$query  ->use_table("departments")
->get_table(10,15);

?>

Дает тебе:

// Big query
select e.id,e.employee_name,e.salary,dept.department_name,desi.designation_name,e.department,e.designation from employees e LEFT OUTER JOIN designations desi ON e.designation = desi.id LEFT OUTER JOIN departments dept ON e.department = dept.id ORDER BY e.employee_name LIMIT 5, 1

// More advanced select
select id,designation_name from designations ORDER BY designation_name

// Simple select
select * from departments

// Simple select with limits
select * from departments LIMIT 10, 15
1

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]