diff options
Diffstat (limited to 'Database')
| -rw-r--r-- | Database/meth_dbadmin.php | 11 | ||||
| -rw-r--r-- | Database/meth_dbmission.php | 8 | ||||
| -rw-r--r-- | Database/meth_dbsearch.php | 35 | ||||
| -rw-r--r-- | Database/meth_dbuser.php | 76 |
4 files changed, 88 insertions, 42 deletions
diff --git a/Database/meth_dbadmin.php b/Database/meth_dbadmin.php index 32d6077..883063b 100644 --- a/Database/meth_dbadmin.php +++ b/Database/meth_dbadmin.php @@ -15,8 +15,8 @@ class DbAdmin extends DbMain { // https://www.php.net/manual/fr/pdostatement.fetch.php // "PDO::FETCH_ASSOC: retourne un tableau indexé // par le nom de la colonne comme retourné dans le jeu de résultats" - $data = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC); + return $result; } // https://stackoverflow.com/questions/1361340/how-can-i-do-insert-if-not-exists-in-mysql @@ -44,13 +44,6 @@ class DbAdmin extends DbMain { return 0; } } - - // Récupérer toutes les infos de la table jobCategory - final public function get_all_job_category() { - $reqGetAll = "SELECT * FROM ".$this->tableJobCategory; - $data = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC); - return $data; - } // Ajout d'une categorie final public function job_category_register($jobCategoryNameEn, $jobCategoryNameFr) { diff --git a/Database/meth_dbmission.php b/Database/meth_dbmission.php index 006f572..f39f0b6 100644 --- a/Database/meth_dbmission.php +++ b/Database/meth_dbmission.php @@ -42,8 +42,8 @@ class DbMission extends DbMain { LEFT JOIN ".$this->tableUserInfo." ON ".$this->tableMission.".missionId = ".$this->tableUserInfo.".userId WHERE proId = ?"; - $data = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC); + return $result; } // Compte le nombre de mission d'un consultant par son nom @@ -52,8 +52,8 @@ class DbMission extends DbMain { INNER JOIN ".$this->tableUserInfo." ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId WHERE lastname = ?"; - $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); + return $result; } }
\ No newline at end of file diff --git a/Database/meth_dbsearch.php b/Database/meth_dbsearch.php index 8a95a67..5104d15 100644 --- a/Database/meth_dbsearch.php +++ b/Database/meth_dbsearch.php @@ -5,7 +5,7 @@ // **************************************************************************** // 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 @@ -15,8 +15,8 @@ class DbSearch extends DbMain { final public function get_user_account_by_id($id) { $reqSearchUser = "SELECT userId, email, inscriptionDate, isClient, isPro, isAdmin FROM ".$this->tableUserAccount." WHERE userId = ?"; - $data = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC); + return $result; } // Récupération des infos d'un utilisateur par son id @@ -25,8 +25,8 @@ class DbSearch extends DbMain { capability, description, phoneNumber, adress, zipCode, city FROM ".$this->tableUserInfo." WHERE userId = ?"; - $data = $this->exec_cmd($reqGetUserInfo, array($id))->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqGetUserInfo, array($id))->fetchAll(PDO::FETCH_ASSOC); + return $result; } // Récupération des infos d'un pro par son nom @@ -36,19 +36,36 @@ class DbSearch extends DbMain { INNER JOIN ".$this->tableUserAccount." ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".userId WHERE isPro = '1' AND lastname LIKE CONCAT('%', ?, '%')"; - $data = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC); + return $result; } + // Récupération des emploies associés à un pro + final public function get_pro_job_category($id) { + $reqGetAll = "SELECT jobCategoryId FROM ".$this->tableUserJob." WHERE userId = ?"; + $result = $this->exec_cmd($reqGetAll, array($id))->fetchAll(PDO::FETCH_NUM); + $proJobs = []; + for ($i = 0; $i < count($result); $i++) { + array_push($proJobs, $result[$i][0]); + } + return $proJobs; + } + // Récupération des notes d'un consultant par son nom final public function get_pro_note_by_lastname($name) { $reqCountMission = "SELECT note FROM ".$this->tableMission." INNER JOIN ".$this->tableUserInfo." ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId WHERE lastname = ?"; - $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); - return $data; + $result = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC); + return $result; } + // Récupérer toutes les infos de la table jobCategory + final public function get_job_category_all() { + $reqGetAll = "SELECT * FROM ".$this->tableJobCategory; + $result = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC); + return $result; + } } ?>
\ No newline at end of file diff --git a/Database/meth_dbuser.php b/Database/meth_dbuser.php index c0afb8c..1b62b46 100644 --- a/Database/meth_dbuser.php +++ b/Database/meth_dbuser.php @@ -24,7 +24,8 @@ class DbUser extends DbMain { // Préparation de la requète $reqAddUser = "INSERT INTO ".$this->tableUserAccount." (email, password, inscriptionDate, isClient, isPro, isAdmin) - VALUES (?, ?, ?, ?, ?, ?)"; + VALUES + (?, ?, ?, ?, ?, ?)"; // Execution de la requète $this->exec_cmd($reqAddUser, array($email, @@ -51,9 +52,9 @@ class DbUser extends DbMain { // Chiffrement du mot de passe $crypt = $this->crypt_pass($pass); // Execution de la requète - $data = $this->exec_cmd($reqCheckCredential, array($email))->fetchAll(PDO::FETCH_ASSOC); + $result = $this->exec_cmd($reqCheckCredential, array($email))->fetchAll(PDO::FETCH_ASSOC); // Vérifiaction de correspondance email & pass - foreach ($data as $rows) { + foreach ($result as $rows) { if ($rows["password"] == $crypt) { return $rows["userId"]; } @@ -61,24 +62,59 @@ class DbUser extends DbMain { } // Mise à jour des infos d'un utilisateur - final public function user_infos_update($lastname, $firstname, $job, $degree, - $capability, $description, $phoneNumber, - $adress, $zipCode, $city, $userId) { + final public function user_infos_update($userInfos, $userId, $isPro, $userJobs = null) { // Préparation de la requète pour mise à jour des infos - $reqAddInfo = "REPLACE INTO ".$this->tableUserInfo." - (lastname, firstname, job, degree, capability, - description, phoneNumber, adress, zipCode, city, userId) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - // Exécution de la requète - $this->exec_cmd($reqAddInfo, - array($lastname, $firstname, $job, $degree, - $capability, $description, $phoneNumber, - $adress, $zipCode, $city, $userId - ) - ); + // J'ai préféré faire deux requètes distinctes et ne pas utiliser des valeurs + // passées par $POST ($key => $value) pour gérer les noms des colonnes + // car je préfère les passer en "dur" dans mes requêtes (risque d'injection SQL?). + $reqAddInfoClient = "UPDATE ".$this->tableUserInfo." + SET + lastname = ?, firstname = ?, phoneNumber = ?, + adress = ?, zipCode = ?, city = ? + WHERE + userId = ?"; + $reqAddInfoPro = "UPDATE ".$this->tableUserInfo." + SET + lastname = ?, firstname = ?, phoneNumber = ?, + adress = ?, zipCode = ?, city = ?, + degree = ?, capability = ?, description = ? + WHERE + userId = ?"; + $reqAddInfoProJobs = "INSERT INTO ".$this->tableUserJob." + (jobCategoryId, userId) + VALUES + (?, ?)"; + $reqRemoveInfoProJobs = "DELETE FROM ".$this->tableUserJob." + WHERE + jobCategoryId = ? + AND + userId = ?"; + if (!$isPro) { + // Exécution de la requète + // https://www.php.net/manual/fr/function.array-values.php + // Pour passer de dict a list -> array_values() + $args = array_values($userInfos); + array_push($args, $userId); + $this->exec_cmd($reqAddInfoClient, array_push($args, $userId)); + } else { + // TODO: Commenter + // https://stackoverflow.com/questions/15986235/how-to-use-json-stringify-and-json-decode-properly + $selectedJobs = json_decode(html_entity_decode($userInfos["jobs"])); + unset($userInfos["jobs"]); + $jobsAdded = array_values(array_diff($selectedJobs, $userJobs)); + $jobsRemoved = array_values(array_diff($userJobs, $selectedJobs)); + $args = array_values($userInfos); + array_push($args, $userId); + $this->exec_cmd($reqAddInfoPro, $args); + for ($i = 0; $i < count($jobsAdded); $i++) { + $this->exec_cmd($reqAddInfoProJobs, array($jobsAdded[$i], $userId)); + } + for ($i = 0; $i < count($jobsRemoved); $i++) { + $this->exec_cmd($reqRemoveInfoProJobs, array($jobsRemoved[$i], $userId)); + } + } } - // **************************************************************************** // Gestion du token-autologin // **************************************************************************** @@ -89,9 +125,9 @@ class DbUser extends DbMain { $reqCheckToken = "SELECT userId, tokenAutoLogin FROM ".$this->tableUserAccount." WHERE email = ?"; // Exécution de la requète - $data = $this->exec_cmd($reqCheckToken, array($email))->fetchAll(PDO::FETCH_ASSOC); + $result = $this->exec_cmd($reqCheckToken, array($email))->fetchAll(PDO::FETCH_ASSOC); // Vérifiaction de correspondance email & jeton d'auto-connection - foreach ($data as $rows) { + foreach ($result as $rows) { if ($rows["tokenAutoLogin"] == $token) { return $rows["userId"]; } |