exec($alwaysset); # } return $dbh; } // inicializalas // PDOException-t dob connect error eseten! public function MySQL($socket_or_host, $db, $user, $pass, $params=null) { $this->initparams = array($socket_or_host, $db, $user, $pass, $params); $this->dbh = $this->getNewConnection($socket_or_host, $db, $user, $pass, $params); } // $dbh = new MySQL('/tmp/mysql.sock','birodalom','www','password',array('AutoCommit' => FALSE) ); public function reconnect() { list($socket_or_host, $db, $user, $pass, $params) = $this->initparams; $this->dbh = $this->getNewConnection($socket_or_host, $db, $user, $pass, $params); } // egy teljes sor a tablabol, unique id alapjan // $user = $dbh->getRowById('users','id',$_SESSION['userid']) public function getRowById($table,$column,$id) { $sth = $this->query("SELECT * FROM $table WHERE `$column`=?",$id); if (!$sth) return FALSE; return $sth->fetch(); } // egy sor a tablabol /* $user = $dbh->getRow('SELECT * FROM users WHERE id=?',array($id)) */ public function getRow($query,$bindparams=null) { $sth = $this->query($query,$bindparams); #$sth = $this->dbh->prepare($query); if ($sth === FALSE) return FALSE; # if (is_array($params)) { # $sth->execute($params); # } else { # $sth->execute(); # } return $sth->fetch(); } // egy oszlop egy mezoje a tablabol public function getColumn($query,$bindparams=null) { $sth = $this->query($query,$bindparams); if ($sth === FALSE) return FALSE; return $sth->fetchColumn(); } // create&execute update based on hash /* $dbh->updateRowById('users','id', $_SESSION['id'], array('username' => 'foo') */ public function updateRowById($table,$key,$keyvalue,$values) { $setarray = array(); $bindarray = array(); foreach ($values as $field => $value) { if (! $field) continue; $setarray[] = "`$field`=?"; $bindarray[] = $value; } $sets = join(",", $setarray); $bindarray[] = $keyvalue; return $this->query("UPDATE $table SET $sets WHERE `$key`=?",$bindarray); } // kereses tablaban, lapozoval public function searchAndCount($table,$fields,$conds,$params) { } // u.a. mint getRows, csak egy count-ot is vegrehajt, a visszateres array('count' => ..., 'list' => ...) // egy tobb soros resultset egyik id-je alapjan egy asszociativ tombot keszit public function resultToHash($array, $byid, $removeid = FALSE) { } // $usersbyid = $dbh->resultToHash($userlist,'id'); // query kiadasa // $dbh->query('TRUNCATE TABLE users'); // $count = $dbh->query('DELETE FROM users WHERE active=?',0); // Returns: $sth on success, FALSE on error public function query($query,$params=null) { # error_log("query: $query"); # error_log("params: ".print_r($params,TRUE)); $sth = $this->dbh->prepare($query); if (!$sth) { # FIXME throw new Exception("SQL QUERY ERROR IN: $query"); } $result = FALSE; if (is_array($params)) { $result = $sth->execute($params); } elseif (!is_null($params)) { $result = $sth->execute(array($params)); } else { $result = $sth->execute(); } if (!$result) { $this->errorinfo = $sth->errorInfo(); $this->success = FALSE; return FALSE; } $this->success = TRUE; $this->insertid = $this->dbh->lastInsertId(); $this->rowcount = $sth->rowCount(); return $sth; } public function insertRow($table,$values) { $this->insertid = null; $setarray = array(); $bindarray = array(); foreach ($values as $field => $value) { if (! $field) continue; $setarray[] = "`$field`=?"; $bindarray[] = $value; } $sets = join(",", $setarray); $sth = $this->query("INSERT INTO $table SET $sets", $bindarray); if ($sth) { $this->insertid = $this->dbh->lastInsertId(); } else { return FALSE; } return TRUE; } public function getInsertId() { return $this->insertid; } public function getRowCount() { return $this->rowcount; } // egy sor torlese ID alapjan public function deleteRowById($table,$field,$id) { return $this->query("DELETE FROM $table WHERE $field=? LIMIT 1",array($id)); } // FORM mentese SQL-be (insert, update) # action: insert | update | replace # table: SQL table # # fields: list of fields (for all actions), as an array # key: name of key column (typically "id") # noupdate: list of columns not to update on "update" # oninsert: add to query on insert # onupdate: add to query on update # container: typically $_FORM # keyvalue: value of key for update public function saveRecord($table, $action, $p) { $this->success = TRUE; $fields = $p["fields"]; $noupdate = $p["noupdate"]; $key = $p["key"]; $cont = $p["container"]; $oninsert = $p["oninsert"]; $onupdate = $p["onupdate"]; $keyvalue = $p['keyvalue']; $quoteempty = $p['quoteempty']; $validate = $p['validate']; # remove unvanted fields if (($action == "update") && is_array($noupdate)) { foreach ($noupdate as $del) { unset($fields[$del]); unset($validate); } } $this->errorfields = array(); // kell-e validalni? ha igen egyenkent nezzuk... if (is_array($validate)) { foreach ($validate as $field => $desc) { $value = $cont[$field]; foreach ($desc as $how => $data) { $error = FALSE; switch ($how) { case 'required': # tkp. 'defined' kene, hogy legyen... FIXME if (!isset($value)) $error = TRUE; break; case 'sameas': $otherkey = $data["aux"]; if ($value != $cont[$otherkey]) $error = TRUE; break; case 'unique': $ex = $this->getRowById($table,$field,$value); if (is_array($ex) && $ex[$field]) $error = TRUE; break; case 'posint': if (!isset($value) || ($value <= 0)) $error = TRUE; break; case 'hasnonwhitespace': if (!isset($value) || !preg_match('/\S+/',$value)) $error = TRUE; break; case 'utf8username': if (!isset($value) || !preg_match('/[0-9a-zA-Z_áéőúűóüöíÖÜÓŐÚŰÁÉÍ]+/',$value)) $error = TRUE; break; } // END switch if ($error) { $this->errorfields[$field] = TRUE; $this->errormsgs[$field] = $data["msg"]; $this->success = FALSE; } } } # ne tovabb ha error: if (!$this->success) return FALSE; } # create bind params and set part of query $setarray = array(); $bindarray = array(); foreach ($fields as $field) { if (! $field) continue; $setarray[] = "$field=?"; if ($quoteempty) { $bindarray[] = "$cont[$field]"; } else { $bindarray[] = $cont[$field]; } } $sets = join(",", $setarray); $query = ""; if ($action == "insert") { if ($oninsert) $sets = "$sets,$oninsert"; $query = "INSERT INTO $table SET $sets"; } elseif ($action == "update") { if ($onupdate) $sets = "$sets,$onupdate"; $query = "UPDATE $table SET $sets WHERE `$key`=?"; $bindarray[] = $keyvalue; } $sth = $this->query($query,$bindarray); /* if (!$this->success) { echo print_r($sth->errorInfo()); # FIXME return FALSE; } if (!$sth->rowCount()) { return FALSE; } $this->success = TRUE; if ($action == "insert") { return $this->dbh->lastInsertId(); } return TRUE; */ if ($sth) return TRUE; return FALSE; } public function getErrorFields() { return $this->errorfields; } public function getErrorMessages() { return $this->errormsgs; } public function getErrorInfo() { return $this->errorinfo; } public function getRows($query,$params=null) { $sth = $this->query($query,$params); if ($sth) return $sth->fetchAll(); return FALSE; } public function replaceRow($table,$values) { $setarray = array(); $bindarray = array(); foreach ($values as $field => $value) { if (! $field) continue; $setarray[] = "`$field`=?"; $bindarray[] = $value; } $sets = join(",", $setarray); return $this->query("REPLACE $table SET $sets",$bindarray); } // tranzakciokezeles beepitve, BEGIN // $dbh->begin(); public function begin($norollback=FALSE) {} // tranzakciokezeles, COMMIT // $dbh->commit(); public function commit() {} // return success of saveRecord public function success() { return $this->success; } } // Things to do later: // egy sor a tablabol, szukitheto /* $user = $dbh->getRow('users', 'id, email', 'username=?', $_POST['username']); $user = $dbh->getRow('users', '*', 'username="bela"'); $user = $dbh->getRow('users', array('username','password'), 'id=?', $_SESSION['username']); $user = $dbh->getRow('users', '*', array( 'and' => array('username' => $_POST['username'])) ); $user = $dbh->getRow('users,stats', '*', array( 'and' => array('username' => $_POST['username']), 'joinon' => 'users.id=stats.userid') ); $user = $dbh->getRow('SELECT * FROM users WHERE userid=?', $_SESSION['userid']); $user = $dbh->getRow('SELECT * FROM users WHERE userid=1'); */ //public function getRowMixed($table,$fields='*',$conds=null,$params=null) { // query eloallito parameterekbol // $where = $dbh->_createWhere( array( 'and' => array ('username' => 'a', 'active' => 1), 'limit' => "0,10", 'orderby' => 'created' ) ); // " WHERE username="A" AND active=1 ORDER BY created LIMIT 0,10 " // public function createWhere($array); // tobb sor a tablabol, szukitheto // public function getRows($table,$fields,$conds,$params); // u.a. mint getRow, csak array-t ad vissza // sor torlese // public function deleteRow($table,$conds,$params) { } // $dbh->deleteRow('users','username=? AND id=?',array($_POST['username'],$_SESSION['username'])) // tobbi kb. mint getRow-nal ?>