diff options
Diffstat (limited to 'Database/meth_dbsearch.php')
| -rw-r--r-- | Database/meth_dbsearch.php | 78 |
1 files changed, 78 insertions, 0 deletions
diff --git a/Database/meth_dbsearch.php b/Database/meth_dbsearch.php new file mode 100644 index 0000000..0a325aa --- /dev/null +++ b/Database/meth_dbsearch.php @@ -0,0 +1,78 @@ +<?php +// **************************************************************************** +// Description: Ensemble de méthodes pour étendre dbmain afin +// d'effectuer des recherches dans la bdd +// **************************************************************************** +// Infos pour les "join": +// https://www.freecodecamp.org/news/sql-joins-tutorial/ +// +// Import de dbmain +require_once(dirname( __FILE__ )."/dbmain.php"); +// Extension de cette classe avec dbmain +class DbSearch extends DbMain { + // Récupérer toutes les infos de la table userAccount + final public function search_all() { + $reqGetAll = "SELECT * FROM ".$this->tableUserAccount; + $data = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC); + return $data; + } + + // Récupération des infos d'un compte par son id + final public function search_user_by_id($id) { + $reqSearchUser = "SELECT id, email, inscriptionDate, isAdmin, isUser, isConsultant + FROM ".$this->tableUserAccount." WHERE id = ?"; + $data = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC); + return $data; + } + + // Récupération des infos d'un utilisateur par son id + final public function get_user_info_by_id($id) { + $reqGetUserInfo = "SELECT lastname, firstname, job, degree, + capability, description, phoneNumber, + adress, zipCode, city + FROM ".$this->tableUserInfo." WHERE userId = ?"; + $data = $this->exec_cmd($reqGetUserInfo, array($id))->fetchAll(PDO::FETCH_ASSOC); + return $data; + } + + // Récupération des infos d'un consultant par son nom + final public function search_consultant_by_name($research) { + $reqSearchConsultant = "SELECT id, lastname, firstname, job, capability FROM ".$this->tableUserInfo." + INNER JOIN ".$this->tableUserAccount." + ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".id + WHERE isConsultant = '1' AND lastname LIKE CONCAT('%', ?, '%')"; + $data = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC); + return $data; + } + + // Récupération des notes d'un consultant par son nom + final public function get_consultant_note_by_name($name) { + $reqCountMission = "SELECT note FROM ".$this->tableMission." + INNER JOIN ".$this->tableUserInfo." + ON ".$this->tableMission.".consultantId = ".$this->tableUserInfo.".userId + WHERE lastname = ?"; + $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); + return $data; + } + + // Récupérations des missions d'un consultant par son id + final public function get_all_mission_by_id($id) { + $reqGetAllMission = "SELECT subject, note, review, lastname FROM ".$this->tableMission." + INNER JOIN ".$this->tableUserInfo." + ON ".$this->tableMission.".userId = ".$this->tableUserInfo.".userId + WHERE consultantId = ?"; + $data = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC); + return $data; + } + + // Compte le nombre de mission d'un consultant par son nom + final public function count_consultant_mission_by_name($name) { + $reqCountMission = "SELECT COUNT(*) FROM ".$this->tableMission." + INNER JOIN ".$this->tableUserInfo." + ON ".$this->tableMission.".consultantId = ".$this->tableUserInfo.".userId + WHERE lastname = ?"; + $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); + return $data; + } +} +?>
\ No newline at end of file |