mysql - My php pdo class takes too much time to execute -
i made php database class pdo using project. simple. noticed 1 thing that, takes time execute. can tell me did wrong?
here database class code
class database{ public $error; private $db; private $dbhost = 'localhost'; private $dbname = 'support_database'; private $dbuser = 'root'; private $dbpass = ''; public $site_url = "http://localhost/support"; public function connect(){ try { $this->db = new pdo("mysql:host={$this->dbhost};dbname={$this->dbname}",$this->dbuser,$this->dbpass); $this->db->setattribute(pdo::attr_errmode, pdo::errmode_exception); } catch(pdoexception $e) { // echo "connection error: ".$e->getmessage(); } } public function deleterecord($id){ $this->connect(); $statement = $this->db->prepare("delete officers_table id=?"); $statement->execute(array($id)); } public function showrecord($tablename){ $this->connect(); $statement = $this->db->prepare("select * $tablename"); $statement->execute(); return $result = $statement->fetchall(pdo::fetch_assoc); } public function addrecord($tablename,$fields,$values){ $this->connect(); $items = $fields; $id = count($items); $newarray = array(); for($i = 0; $i<$id; $i++){ $newarray[] = "?"; } $nval = implode(",",$newarray); $fields = implode(",",$fields); $statement = $this->db->prepare("insert $tablename ($fields) values($nval)"); $statement->execute($values); return true; } public function adduser($tablename,$fields,$values){ $this->connect(); $items = $fields; $id = count($items); $newarray = array(); for($i = 0; $i<$id; $i++){ $newarray[] = "?"; } $nval = implode(",",$newarray); $fields = implode(",",$fields); $statement = $this->db->prepare("insert $tablename ($fields) values($nval)"); $statement->execute($values); return true; } public function checkuser($tablename,$field,$value){ $this->connect(); $num = 0; $statement = $this->db->prepare("select * $tablename $field=?"); $statement->execute(array($value)); $num = $statement->rowcount(); if($num>0) { return true; } else { return false; } } public function singlevaluesearch($tablename,$field,$value,$id="id",$ordertype="desc"){ $this->connect(); $statement = $this->db->prepare("select * $tablename $field=? order $id $ordertype"); $statement->execute(array($value)); return $result = $statement->fetchall(pdo::fetch_assoc); } public function allticket($tablename){ $this->connect(); $statement = $this->db->prepare("select * $tablename order id desc"); $statement->execute(); return $result = $statement->fetchall(pdo::fetch_assoc); } public function userticket($tablename,$field,$value,$id="id",$ordertype="desc"){ $this->connect(); $statement = $this->db->prepare("select * $tablename $field=? order $id $ordertype"); $statement->execute(array($value)); return $result = $statement->fetchall(pdo::fetch_assoc); } public function currentuserid($email){ $this->connect(); $statement = $this->db->prepare("select * support_users email=?"); $statement->execute(array($email)); return $result = $statement->fetchall(pdo::fetch_assoc); } public function lastid($tablename){ $this->connect(); $statement = $this->db->prepare("select * $tablename order id desc limit 1"); $statement->execute(); return $result = $statement->fetchall(pdo::fetch_assoc); } public function emptycheck($tablename,$user_id,$user_role){ $this->connect(); $num = 0; if($user_role == 'admin'){ $statement = $this->db->prepare("select * $tablename"); $statement->execute(); $num = $statement->rowcount(); if($num>0){ return true; } else { return false; } } else{ $statement = $this->db->prepare("select * $tablename user_id =?"); $statement->execute(array($user_id)); $num = $statement->rowcount(); if($num>0){ return true; } else { return false; } } } public function filecheck($name) { foreach ($_files[$name]['error'] $ferror) { if ($ferror != upload_err_no_file) { return true; } } return false; } public function deleteticket($id){ $this->connect(); $statement = $this->db->prepare("select * support_tickets id=?"); $statement->execute(array($id)); $result = $statement->fetchall(pdo::fetch_assoc); $media_id = unserialize($result[0]['media_id']); foreach( $media_id $mid ){ $statement = $this->db->prepare("delete support_media id=?"); $statement->execute(array($mid)); } $statement = $this->db->prepare("delete support_tickets id=?"); $statement->execute(array($id)); return true; } } and in project using this. below link's code taking long time execute.i have used more queries on page.
please me find out mistake made. in advance.
there optimizations can in existing code.
the database connection
you're creating new connection on every query db. expensive should reuse current connection instead.
add in beginning of connect() method:
public function connect(){ if ($this->db) { // there connection, return instead return $this->db; } // current try/catch block } repeating same query on , over
you're fetching same record database on , on within same script. though db calls fast (assuming db indexed correctly), it's still unnecessary overhead. should fetch once, store in variable , reuse variable:
$ticket = $db->singlevaluesearch('support_tickets','id',$_get['id'],'id','desc'); now can replace identical calls use variable $ticket instead.
Comments
Post a Comment