Clase si lucrul cu BD in php

IUser.interface.php

define ("DB", "pwdatabase1");
define ("USER", "root");
define ("PASS", "");
 
interface IUser {
   public function __construct ($id);
   public function __toString ();
}

Pornind de la aceasta interfata, am scris clasa User care o extinde si implementeaza metodele __construct si __toString; in plus exista metodele Update , Insert si Search care interactioneaza cu BD (am considerat un tabel numit users , cu 3 coloane: Id | Name | Description). Clasa MyException este construita pt a fi folosita la generarea exceptiilor de la initializarea unui User cu id invalid.

Clasa Bonus este asemanatoare cu User, doar ca face insert/update pe un tabel cu oricate coloane (pt asta se dau numele coloanelor si valorile lor ca parametri). Metoda Search ar fi asemanatoare.

<?php

interface IUser {
   public function __construct ($id);
   public function __toString ();
}

class MyException extends Exception {

protected $message;
public function __construct() {
$this->message = 'Id incorect ';
}
}

class User implements IUser {

public $username;
public $id;
public $description;

public function __construct($id){


if (gettype($id)!='integer')
throw new MyException();
$connection = new mysqli('localhost','root','','lab2'); // linia 2
$query = "SELECT * from users where Id = ?"; //linia 3
$stmt = $connection->prepare($query); //linia 4
$stmt->bind_param("i",$id); //linia 5
$stmt->execute(); //linia 6
$stmt->bind_result($this->id,$this->username,$this->description); //linia 7
$stmt->fetch(); // linia 8
$stmt->close(); 
$connection->close();
}

public function __toString (){
return '['.$this->id.', '.$this->username.', '.$this->description."]\n";
}

public function Update ($id,$name,$desc) {

$connection = new mysqli('localhost','root','','lab2'); 
$query = "Update users set Name = ?, Description = ? where Id = ?"; 
$stmt = $connection->prepare($query); 
$stmt->bind_param("ssi",$name,$desc,$id); 
$stmt->execute(); 
$stmt->fetch(); 
$stmt->close(); 
$connection->close();
}

public function Insert ($id,$name,$desc) {


$connection = new mysqli('localhost','root','','lab2'); 
$query = "Insert into users values(?, ?,?)"; 
$stmt = $connection->prepare($query); 
$stmt->bind_param("iss",$id, $name, $desc); 
$stmt->execute(); 
$stmt->fetch(); 
$stmt->close(); 
$connection->close();
}

static function Search ($Nume){


$connection = new mysqli('localhost','root','','lab2'); //linia 2
$query = "SELECT * from users where Name='".$Nume."'"; //linia 3
$result = $connection->query($query); //linia 4
$row = $result->fetch_assoc() ; //linia 5
return new User(intval ($row["Id"]));
}

}

class Bonus {

public $id;
public $coloane;
public $tabel;

public function __construct($id, $tabel){


$this->id = $id;
$this->tabel = $tabel;
if (gettype($id)!='integer')
throw new MyException();
$connection = new mysqli('localhost','root','','lab2'); 
$query = "SELECT * from ". $this->tabel  ." where Id = " . $id; 
$result = $connection->query($query);
$row = $result->fetch_row();
$i = 1;
while (isset($row[$i])) {
$this->coloane[$i-1] = $row[$i];
$i++;
}
$connection->close();
}

public function __toString (){

$str = '['.$this->id;
for ($i=0; $i<count($this->coloane); $i++)  
$str = $str . ','. $this->coloane[$i] ;
$str = $str . ']';
return $str;
}

public function Update ($id, $col, $val) {

$connection = new mysqli('localhost','root','','lab2'); 
$query = "Update ". $this->tabel . " set ";
for ($i=0; $i<count($col); $i++) {
$query = $query . $col[$i] . "='" . $val[$i] . "'";
if ($i<count($col)-1)
$query = $query . ",";
}
$query = $query . " where id=?";


$stmt = $connection->prepare($query);
$stmt->bind_param("i",$id);
$stmt->execute();
$stmt->fetch();
$stmt->close(); 
$connection->close();
}

public function Insert ($id,$col,$val) {


$connection = new mysqli('localhost','root','','lab2');
$query = "insert into " . $this->tabel . " (id, ";
for ($i=0; $i<count($col); $i++) {
$query = $query . $col[$i];
if ($i<count($col)-1)
$query = $query . ",";
}
$query = $query . ") values (" . $id . ",";
for ($i=0; $i<count($val); $i++) {
$query = $query . $val[$i];
if ($i<count($val)-1)
$query = $query . ",";
}
$query = $query . ")";
echo $query;
$result = $connection->query($query); 
$connection->close();
}

}

//////////////////////////////////////////////////////////////////////////////////////////////////////

try {

$user = new User(3);
echo $user;

//$user->Insert(4,'Ion','ne miscam');
//$user->Update(4,'Ion','okaa');
//$rezultat = $user->Search('Ion');
//echo $rezultat;

$bon = new Bonus(1, 'mytab');
echo $bon;
//$bon->Update(1, array('nume', 'prenume'), array('popescu','ion'));
//$bon->Insert(2, array('nume', 'prenume', 'email'), array("'lal'", "'salu'", "'lalsalu@bg.com'"));

} catch (MyException $e) {
echo 'Exceptie -> Message: ' .$e->getMessage();
}

?>

Pentru conectarea la baza de date se pot folosi:

• interogari simple
• prepared statements

La o interogare simpla se foloseste metoda query a obiectului de tip mysqli creat si rezultate se extrag prin metodele fetch_row/fetch_assoc/etc . [vezi constructorul clasei Bonus]

La un prepared statement, se foloseste metoda prepare a unui obiect de tip mysqli, parametrii se adauga prin metoda bind_param iar rezultatele se extrag cu bind_result. [vezi constructorul clasei User]