Jump to content

gmc1103

Members
  • Posts

    247
  • Joined

  • Last visited

Everything posted by gmc1103

  1. Thanks But i keep the rel_visitas_utilizador table? with id_visita and id_user?
  2. I understand the application, When the user schedule the study visit, the data is inserted into the visitas table, then the id generated in the table visitas is inserted into the rel_visitas_utilizador. My question was if that implementation (all tables are correct or can they can be improved). The table rel_visitas_utilizador is where is keeped some extra informations about that visit (like pdf file, validate or not, realizado(made or not), etc). About the 1-N, I doubt since rel_visitas_utilizador should only keep one id, can't be repeated
  3. Hi Requinix Right now is what i see in the database. For me it's sound a little confusing. I understand the schema, but regarding the point you mention it seems it's 1_N from visitas to rel_visitas_utilizador..
  4. Hi I'm still developing my database and now i have another task So in my school i have teachers, table Utilizador id_utilizador(teacher) The table visitas have the following data. id; classroom_id; place_visit; date_leaving; hour_leaving; date_arriving; hour_arriving; subjects(teachers subjects); teachers; objectives; transportation; price; students(how many); itinerary; observations; id_year (school year; registered (date) Classroom table turmas id_turma(classroom_id); classroom_name active(yes/no) My related table rel_visitas_utilizador id_visita(foreign key); id_utilizador(foreign key) done(yes/no) valid(yes/no) file(pdf generated and saved in webserver path) So right now this is how this work, teh process A teacher must can schedule a study visit, when he fill the form, all the data from form are sent the the manager and he must define if this visit is valid or no (rel_visitas_utilizador). So with the following explanation and the image provided can someone give me some suggestions to improve this database? Any questions, just ask
  5. Ok Let me show what i have Using this query SELECT dpt FROM visitas_estudo WHERE id_visita = 28 I have dpt --------- 4,6,2,3 this is 4 (id's) regarding the departamentos table So i need to get those values (ids) and using a foreach loop get all the subjects of that. That's why i have 2 query, first one to get all the id's and then with those id's making another query to get the subject, attach all the an array and then send it to pdf file the return should be something like Artes, Ciências Sociais e Humanas,Linguas Estrangeiras,Matemática e Informática,Português
  6. Hi Jacques No..let me explain What is stored into the database is some id's, that's the way they want. What i need is get all the id's and then return the values of those id's for instance id 1 ......Math 2.......Portuguese 3.......English and so on. So, when i want to get all the subjects, i need to get them from the id stored in the database. About the code, first i want to collect the id's stored in the dpt field; second, from the array, i execute the loop to get the values of each id and store them in an array; then i have all subjects in a string Maybe isn't the best approach but i'm still learning Regards
  7. Hi Barand How are you? Thanks for the answer but i still hava a problem....it only gives me one, when it shoud be 4 The code <?php error_reporting(E_ALL); ini_set('display_errors', 1); header('Content-type: application/json'); require_once("./dbconfig.php"); $esmaior = new ESMAIOR($DB_con); $id_visita = 28; try { $stmt = $DB_con->prepare("SELECT dpt FROM visitas_estudo where id_visita = :id_visita"); $stmt->bindParam(':id_visita', $id_visita, PDO::PARAM_INT); $stmt->execute(); $dpts = $stmt->fetchAll(PDO::FETCH_COLUMN); var_dump($dpts); if($stmt->rowCount() > 0) { $stmt = $DB_con->prepare("SELECT departamento FROM departamentos where id_departamento = :id_departamento"); foreach ($dpts as $id) { $stmt->bindParam(':id_departamento', $id, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_COLUMN); $nome_dpt = array(); foreach ($result as $departamentos) { $nome_dpt[] = $departamentos; } } } var_dump($nome_dpt); $nomes = implode(';', $nome_dpt); echo $nomes; } catch (PDOException $e) { echo $e->getMessage(); } and the var_dump It looks the loop it only executed one time only array(1) { [0]=> string(7) "4,6,2,3" } array(1) { [0]=> string(6) "Artes " } Artes right?
  8. Hi I'm having a problem regarding the following code and i don't get whats is wrong with that <?php error_reporting(E_ALL); ini_set('display_errors', 1); header('Content-type: application/json'); require_once("./dbconfig.php"); $esmaior = new ESMAIOR($DB_con); $id_visita = 28; try { $stmt = $DB_con->prepare("SELECT dpt FROM visitas_estudo where id_visita = :id_visita"); $stmt->bindParam(':id_visita', $id_visita, PDO::PARAM_INT); $stmt->execute(); $dpts = $stmt->fetchAll(PDO::FETCH_COLUMN); if($stmt->rowCount() > 0) { $stmt = $DB_con->prepare("SELECT departamento FROM departamentos where id_departamento = :id_departamento"); $departamento = implode(',', $dpts); $nome_dpt = array(); echo $departamento; foreach ($departamento as $id) { $stmt->bindParam(':id_departamento', $id, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_COLUMN); foreach ($result as $departamentos) { $nome_dpt[] = $departamentos; } } $nomes = implode(';', $nome_dpt); echo $nomes; } } catch (PDOException $e) { echo $e->getMessage(); } The problem is the foreach loop 4,6,2,3<br /> <b>Warning</b>: Invalid argument supplied for foreach() in <b>/home/esmaior/public_html/miga/db/testes.php</b> on line <b>18</b><br /> I have the echo (gives me 4,6,2,3) but when it enter the loop it say invalid argument... Not sure what is wrong
  9. Hi mac I will try to implement the way you say, any problem i will be back Thanks to you and Barand
  10. Hi Mac and Barand Mac, you are problably right, the problem is i'm getting an array, and then i want to use explode to separate the items and then execute the loop. The way you say it's better but i never use that approach detect if the insert query returned a unique index error. How?? Barand, i have read the website you gave me, you are talking about the The Booking Process, right? I have managed to make it working but with a lot of if statements, and this is not the best way. best regards
  11. Ok Let me explain better with my strange english The diagram i was working it is to make a new database but until is not ready i have this problem An user want to make a reservation of a classroom for instance all morning From 8:15 until 13:20 So until 13:20 i have 5 time period If i choose only one time period...the function i put in my first post works, but if i put 2,3,4 don't. So the fisrt fucntion public function reqSalas($data, $atividade, $id_utilizador, $id_bloco, $id_sala) call in the first line $sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala); public function getDisponibilidadeSala($data, $id_bloco, $id_sala){ try { $stmt = $this->db->prepare("SELECT COUNT(*) AS total FROM rel_requisicoes_detalhes AS t1 INNER JOIN requisicoes AS t2 ON (t1.id_requisicoes = t2.id_requisicoes) WHERE t1.id_bloco = :id_bloco AND t1.id_sala = :id_sala AND t2.datas = :datas;"); $stmt->bindparam(":datas", $data, PDO::PARAM_STR); $stmt->bindparam(":id_bloco", $id_bloco, PDO::PARAM_INT); $stmt->bindparam(":id_sala", $id_sala, PDO::PARAM_INT); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return false; } else { $result = $stmt->fetchColumn(); return $result; } } catch (Exception $ex) { echo $ex->getMessage(); } } This function check if the $id_bloco is already in a reservation as long the $data and $id_sala(classroom) With one time period, it works well, with more...don't Check the picture and follow what i gonna write So look the number one in red, this user made a reservation in 12/12/2016 at 10:00 Then number 2,3,4 made a reservation from 9 AM until 10:45 (this makes 3 time periods), so since the fisrt time period was clear, the function add to the database different users in same classroom and same time period. 1 and 3 Better explained?
  12. Hi I have a small issue with the following code Someone pick time period, can be more than one Then i have a function to check if the time period is already picked $sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala); If the return is 1 it is, but it's only pick me the first time period,, so i need a loop to check for every time period the code is the following public function reqSalas($data, $atividade, $id_utilizador, $id_bloco, $id_sala) { $sala = $this->getDisponibilidadeSala($data, $id_bloco, $id_sala); $id_ano = $this->getAnoEscolar(); $id_equipamento = NULL; $sobreposicao = $this->getDisponibilidadeDocente($data, $id_bloco, $id_utilizador); if ($sala > 0 ) { return array('status' => 'error', 'message' => 'Esta sala já está reservada nesse dia e bloco ...'); } if($sobreposicao >0){ return array('status' => 'error', 'message' => 'Ja tem uma reserva para este dia e bloco...'); } else { try { $stmt = $this->db->prepare("INSERT INTO requisicoes (datas,atividade,id_ano) VALUES(:datas,:atividade,:id_ano);"); $stmt->bindparam(":datas", $data); $stmt->bindparam(":atividade", $atividade); $stmt->bindparam(":id_ano", $id_ano); $id_blocos = explode(',', $id_bloco); foreach ($id_blocos as $id) { $result = $stmt->execute(); $id_requisicoes = $this->db->lastInsertId(); $this->insertRequisicaoByLastId($id_requisicoes, $id_utilizador, $id_equipamento, $id, $id_sala); } if (!$result) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Problema ao gravar esta nova reserva para esta sala ...'); } else { return array('status' => 'success', 'message' => 'O registo foi criado com sucesso...'); } } catch (Exception $ex) { echo $ex->getMessage(); } } } The function called is public function getDisponibilidadeSala($data, $id_bloco, $id_sala){ try { $stmt = $this->db->prepare("SELECT COUNT(*) AS total FROM rel_requisicoes_detalhes AS t1 INNER JOIN requisicoes AS t2 ON (t1.id_requisicoes = t2.id_requisicoes) WHERE t1.id_bloco = :id_bloco AND t1.id_sala = :id_sala AND t2.datas = :datas;"); $stmt->bindparam(":datas", $data, PDO::PARAM_STR); $stmt->bindparam(":id_bloco", $id_bloco, PDO::PARAM_INT); $stmt->bindparam(":id_sala", $id_sala, PDO::PARAM_INT); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return false; } else { $result = $stmt->fetchColumn(); return $result; } } catch (Exception $ex) { echo $ex->getMessage(); } } This works only if the fisrt time period is empty...otherwise, no
  13. Ok. Thank you again Barand for everything. I'm gonna open another post in php help regarding a problem i have in another work, if you can read and help me, i would appreciate, Again, many thanks Best regards
  14. Thank you Barand I didn't think this approach, i would like to know like you do, i still have a lot to learn I have a stupid question, this is the first time i work with MySQL Workbench Why the table reserva_hora is green?? And my relations between table are different from yours.. I use usually sqlLyog and this one is new for me The script is this one -- MySQL Script generated by MySQL Workbench -- 01/19/17 21:25:05 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema requisicao -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema requisicao -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `requisicao` DEFAULT CHARACTER SET utf8 ; USE `requisicao` ; -- ----------------------------------------------------- -- Table `requisicao`.`Utilizadores` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizadores` ( `id_utilizador` INT NOT NULL AUTO_INCREMENT, `nome` VARCHAR(100) NOT NULL, `email` VARCHAR(45) NOT NULL, `telefone` INT(9) NOT NULL, `pass` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_utilizador`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Tipo` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Tipo` ( `id_tipo` INT NOT NULL AUTO_INCREMENT, `tipo` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_tipo`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Sala` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Sala` ( `id_sala` INT NOT NULL AUTO_INCREMENT, `designacao` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_sala`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Equipamento` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Equipamento` ( `id_equipamento` INT NOT NULL AUTO_INCREMENT, `equipamento` VARCHAR(45) NOT NULL, `referencia` VARCHAR(45) NOT NULL, `estado` TINYINT(1) NOT NULL, PRIMARY KEY (`id_equipamento`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Horas` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Horas` ( `id_horas` INT NOT NULL AUTO_INCREMENT, `inicio` TIME(5) NOT NULL, PRIMARY KEY (`id_horas`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Reserva` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva` ( `id_reserva` INT NOT NULL AUTO_INCREMENT, `data` DATE NOT NULL, `id_user` INT(11) NOT NULL, `id_sala` INT(11) NOT NULL, `id_equip` INT(11) NOT NULL, PRIMARY KEY (`id_reserva`, `id_user`), INDEX `fk_user_idx` (`id_user` ASC), INDEX `fk_sala_idx` (`id_sala` ASC), INDEX `fk_equip_idx` (`id_equip` ASC), CONSTRAINT `fk_user` FOREIGN KEY (`id_user`) REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_sala` FOREIGN KEY (`id_sala`) REFERENCES `requisicao`.`Sala` (`id_sala`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_equip` FOREIGN KEY (`id_equip`) REFERENCES `requisicao`.`Equipamento` (`id_equipamento`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Reserva_detail` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_detail` ( `id_reserva_detail` INT NOT NULL AUTO_INCREMENT, `id_reserva` INT(11) NOT NULL, `entregue` DATETIME NULL, `devolvido` DATETIME NULL, PRIMARY KEY (`id_reserva_detail`), INDEX `fk_reserva_idx` (`id_reserva` ASC), CONSTRAINT `fk_reserva` FOREIGN KEY (`id_reserva`) REFERENCES `requisicao`.`Reserva` (`id_reserva`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Utilizador_Tipo` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizador_Tipo` ( `id_utilizador` INT NOT NULL, `id_tipo` INT NULL, INDEX `fk_utilizador_idx` (`id_utilizador` ASC), INDEX `fk_tipo_idx` (`id_tipo` ASC), CONSTRAINT `fk_utilizador` FOREIGN KEY (`id_utilizador`) REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tipo` FOREIGN KEY (`id_tipo`) REFERENCES `requisicao`.`Tipo` (`id_tipo`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Reserva_Hora` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_Hora` ( `id_reserva_hora` INT NOT NULL AUTO_INCREMENT, `id_reserva` INT NOT NULL, `id_horas` INT NOT NULL, PRIMARY KEY (`id_reserva_hora`), INDEX `fk_id_reserva_idx` (`id_reserva` ASC), INDEX `fk_id_horas_idx` (`id_horas` ASC), CONSTRAINT `fk_id_reserva` FOREIGN KEY (`id_reserva`) REFERENCES `requisicao`.`Reserva` (`id_reserva`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_id_horas` FOREIGN KEY (`id_horas`) REFERENCES `requisicao`.`Horas` (`id_horas`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Sorry for any inconvinience
  15. Ok So i keep one table to be filled when the non teacher do his job(deliver and collect) and another one to keep all time period from one reservation So can i do like Table Reserva_Status (id_reserva, delivered(datetime), collected(datetime)) Table Reserva_Details (id_reserva, id_horas, id_sala, id_equip) Using this, i can remove from Reserva table (id_horas, id_sala, id_equip) and keep only (id_reserva, data) correct? Thanks for your help
  16. Hi What if the equipment is required for two or more time periods? I understand that, i mean, for every time period i must have a new record for the same id like this Id_reserva data id_equip id_horas id_sala 1 2017-01-18 1 1 1 1 2017-01-18 1 2 1 1 2017-01-18 1 3 1 To do this i need another table right?
  17. Hi Well, Horas table is because i nedd to keep the format and to use only times stored in the DB id_horas inicio fim -------- -------- ---------- 1 08:15:00 09:00:00 2 09:00:00 09:45:00 3 10:00:00 10:45:00 4 10:45:00 11:30:00 5 11:40:00 12:25:00 6 12:25:00 13:10:00 7 13:20:00 14:05:00 8 14:05:00 14:50:00 9 15:00:00 15:45:00 10 15:45:00 16:30:00 11 16:45:00 17:30:00 12 17:30:00 18:15:00 13 18:45:00 19:30:00 14 19:30:00 20:15:00 15 20:15:00 21:00:00 16 21:00:00 21:45:00 17 22:00:00 22:45:00 18 22:45:00 23:30:00 This is Horas table, with this table i can define id_horas in the reservas table and i can avoid someone insert wrong time like 08:10 instead of 08:15 Then i can use id to make my select Example Select COUNT(*) as total from reserva where id_horas = 1 About "reserva_detail" i want to: after a reservation, the id is created in "reservas" table and in "reserva_detail" table, When the non teacher deliver the equipment the datetime is inserted into `entregue` field "reserva_detail" table When the non teacher collect the equipment after the class the datetime is inserted into `devolvido` field "reserva_detail" table I don't know if it is the best approach for doing this
  18. Hi I'm starting to make a new database wand i would like to have opinions about this implementation This is a reservation system for a school Admin can manage the database (add users, equipment, time, classroom) Teacher can make a reservation of an equipment(datashow) Non teacher, deliver the equipment in a classroom and in the end collect him Check the image to follow So i have 8 tables Tipo is (user type (admin, teacher, non teacher) Utilzador is (Users) Utilizador_Tipo (where i put all the user and type) Equipamento (equipment(datashow, pc,tv, etc) Sala (classroom) Horas(Time) Reserva(Reservation, needs , a date, the user_id, the classroom:id, the time_id, the equip_id) Reserva_Detail(when the non teacher deliver and collect the equipment) So this how i did it, 1º approach of course This is my sql -- MySQL Script generated by MySQL Workbench -- 01/19/17 16:43:39 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema requisicao -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema requisicao -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `requisicao` DEFAULT CHARACTER SET utf8 ; USE `requisicao` ; -- ----------------------------------------------------- -- Table `requisicao`.`Utilizadores` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizadores` ( `id_utilizador` INT NOT NULL AUTO_INCREMENT, `nome` VARCHAR(100) NOT NULL, `email` VARCHAR(45) NOT NULL, `telefone` INT(9) NOT NULL, `pass` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_utilizador`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Tipo` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Tipo` ( `id_tipo` INT NOT NULL AUTO_INCREMENT, `tipo` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_tipo`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Sala` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Sala` ( `id_sala` INT NOT NULL AUTO_INCREMENT, `designacao` VARCHAR(45) NOT NULL, PRIMARY KEY (`id_sala`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Equipamento` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Equipamento` ( `id_equipamento` INT NOT NULL AUTO_INCREMENT, `equipamento` VARCHAR(45) NOT NULL, `referencia` VARCHAR(45) NOT NULL, `estado` TINYINT(1) NOT NULL, PRIMARY KEY (`id_equipamento`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Horas` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Horas` ( `id_horas` INT NOT NULL AUTO_INCREMENT, `inicio` TIME(5) NOT NULL, `fim` TIME(5) NOT NULL, PRIMARY KEY (`id_horas`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Reserva` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva` ( `id_reserva` INT NOT NULL AUTO_INCREMENT, `data` DATE NOT NULL, `id_user` INT(11) NOT NULL, `id_sala` INT(11) NOT NULL, `id_equip` INT(11) NOT NULL, `id_horas` INT(11) NOT NULL, PRIMARY KEY (`id_reserva`), INDEX `fk_user_idx` (`id_user` ASC), INDEX `fk_sala_idx` (`id_sala` ASC), INDEX `fk_equip_idx` (`id_equip` ASC), INDEX `fk_horas_idx` (`id_horas` ASC), CONSTRAINT `fk_user` FOREIGN KEY (`id_user`) REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_sala` FOREIGN KEY (`id_sala`) REFERENCES `requisicao`.`Sala` (`id_sala`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_equip` FOREIGN KEY (`id_equip`) REFERENCES `requisicao`.`Equipamento` (`id_equipamento`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_horas` FOREIGN KEY (`id_horas`) REFERENCES `requisicao`.`Horas` (`id_horas`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Reserva_detail` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Reserva_detail` ( `id_reserva_detail` INT NOT NULL AUTO_INCREMENT, `id_reserva` INT(11) NOT NULL, `entregue` DATETIME NULL, `devolvido` DATETIME NULL, PRIMARY KEY (`id_reserva_detail`), INDEX `fk_reserva_idx` (`id_reserva` ASC), CONSTRAINT `fk_reserva` FOREIGN KEY (`id_reserva`) REFERENCES `requisicao`.`Reserva` (`id_reserva`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `requisicao`.`Utilizador_Tipo` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `requisicao`.`Utilizador_Tipo` ( `id_utilizador` INT NOT NULL, `id_tipo` INT NULL, INDEX `fk_utilizador_idx` (`id_utilizador` ASC), INDEX `fk_tipo_idx` (`id_tipo` ASC), CONSTRAINT `fk_utilizador` FOREIGN KEY (`id_utilizador`) REFERENCES `requisicao`.`Utilizadores` (`id_utilizador`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_tipo` FOREIGN KEY (`id_tipo`) REFERENCES `requisicao`.`Tipo` (`id_tipo`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Any improvment? Thanks
  19. Hi Can someone help me with my code. I have composer instaleed and i added ros/ezpdf dependency This is my server path config home --miga --db --file.php --vendor --ros --ezpdf --src --Cezpdf.php composer.json and my main file is error_reporting(E_ERROR | E_WARNING | E_PARSE); ini_set('display_errors', '1'); require_once "dbconfig.php"; require_once __DIR__ . '/../vendor/autoload.php'; set_include_path('../vendor/ros/ezpdf/src/' . PATH_SEPARATOR . get_include_path()); use PHPMailer\PHPMailer\PHPMailer; use PHPMailer\PHPMailer\Exception; setlocale(LC_ALL, NULL); setlocale(LC_ALL, 'pt_PT'); my composer.json "autoload": { "classmap": ["db/class.esmaior.php", "src/Cezpdf.php"] }, "require": { "phpmailer/phpmailer": "6.0.x-dev", "ros/ezpdf": "0.12.1" } When i need to make a pdf, i receive this error Fatal error: Class 'Cezpdf' not found in /home/xxxxx/public_html/miga/db/class.esmaior.php on line 2910 This is the line $pdf = new Cezpdf('a4', 'portrait'); So, i don't know why is not loading the ezpdf class Any help? Thanks
  20. Hi barand Exactly what i need.. Thanks, and Happy new year
  21. Hi I 'm trying to make a ranking based on score and time elapsed but i only get it working with score this is my table with dummy data id_ranking id_jogo id_user pontuacao data tempo ---------- ------- ------- --------- ---------- ---------- 3 5141 11 15 2016-12-27 00:00:03 4 7001 9 10 2016-12-27 00:00:06 5 2519 7 5 2016-12-27 00:00:07 6 4585 6 15 2016-12-27 00:00:09 So i need to make a ranking based on (pontuacao field, and tempo field), this what i have so far SELECT t2.nome, t1.pontuacao, t1.data, t1.tempo, FIND_IN_SET( t1.pontuacao,( SELECT GROUP_CONCAT( t1.pontuacao ORDER BY t1.pontuacao DESC, t1.tempo ASC ) FROM ranking AS t1 ) ) AS rank FROM ranking AS t1 INNER JOIN users AS t2 ON (t1.id_user = t2.id_user) With this return nome pontuacao data tempo rank ---------- --------- ---------- -------- -------- testes 15 2016-12-27 00:00:03 1 dasffgfadg 10 2016-12-27 00:00:06 3 dasgsdfg 5 2016-12-27 00:00:07 4 gil 15 2016-12-27 00:00:09 1 But i have 2 (1) and since one used 3 seconds and the other 9 seconds, i want both of them differenciate any help?
  22. Hi I have received a lot of records to put in a table but i need to add into a relational table too and the easier is to create a trigger for that. I have this trigge DELIMITER $$ USE `esmaior_biblioteca`$$ DROP TRIGGER /*!50032 IF EXISTS */ `alunos`$$ CREATE TRIGGER `alunos` AFTER INSERT ON `alunos` FOR EACH ROW BEGIN INSERT INTO `aluno_turmas` (id_aluno, id_turma) VALUES (new.id_aluno, 38); END; $$ DELIMITER ; when i use this syntax to insert into the main table INSERT INTO alunos (processo,nome,email,estado) VALUES ('17185','XXXXXXXXXX','','1'); INSERT INTO alunos (processo,nome,email,estado) VALUES ('27051','XXXXXXXXXX','','1'); INSERT INTO alunos (processo,nome,email,estado) VALUES ('27184','XXXXXXXXXX','','1'); INSERT INTO alunos (processo,nome,email,estado) VALUES ('16093','XXXXXXXXXX','','1'); The relational table "aluno_turmas" are not updated. Any help?
  23. Hi Jacques You are absolutly right, i was tryig to modify only for PDO but not every code works with some changes. For what is the best approach to get what i want into a pdf? My DB has utf-8 character set and utf bin as collation,the engine is InnoDB Thanks again
  24. Hi Jacques1 The error is there for sure in the "array_map" but my main concern is this var_dump($reservation) array (size=6) 'id_requisicoes' => string '4' (length=1) 'sala' => string '1' (length=1) 'data' => string '2016-09-15' (length=10) 'bloco' => string '08:15 - 09:45' (length=13) 'nome' => string 'XXXXX' (length=12) 'equip' => array (size=2) 0 => string 'Projetor' (length= 1 => string 'Leitor / CD/ USB' (length=16) var_dump($tmp) array (size=6) 'id_requisicoes' => string '4' (length=1) 'sala' => string '1' (length=1) 'data' => string '2016-09-15' (length=10) 'bloco' => string '08:15 - 09:45' (length=13) 'nome' => string 'XXXXXXX' (length=12) 'equip' => null So, why it loose the last element??
  25. Hi Jacques How are you? Thanks for your reply I want to print a pdf with the information of a datable With my old code this was working, since i moved to PDO don't This is all my code $pdf = new Cezpdf('a4', 'landscape'); $pdf->ezSetMargins(20, 20, 20, 20); $pdf->ezStartPageNumbers(460, 15, 10, 'right', 'Página {PAGENUM} de {TOTALPAGENUM}'); $mainFont = 'Times-Roman'; $pdf->selectFont($mainFont); $pdf->openHere('Fit'); try { $sql = "SELECT t1.id_requisicoes,t2.nome, t3.nome AS equip, t4.bloco, t5.sala, t6.datas FROM rel_requisicoes_detalhes AS t1 INNER JOIN utilizador AS t2 ON (t1.id_utilizador = t2.id_utilizador) LEFT JOIN equipamento AS t3 ON (t1.id_equipamento = t3.id_equipamento) INNER JOIN tempos AS t4 ON (t1.id_bloco = t4.id_bloco) INNER JOIN salas AS t5 ON (t1.id_sala= t5.id_sala) INNER JOIN requisicoes AS t6 ON(t1.id_requisicoes = t6.id_requisicoes) WHERE t6.`datas` = CURDATE() ORDER BY t4.`bloco`, t5.`sala`"; $stmt = $DB_con->prepare($sql); if(!$stmt->execute()){ print_r($stmt->errorInfo()); } $affectedRows = $stmt->rowCount(); if ($affectedRows <= 0) { $pdf->setColor(1, 0, 0); $sem = utf8_decode("<b>Não existem registos</b>\n"); $pdf->addText(350, 420, 12, $sem); } else { $result = $stmt->fetchAll(PDO::FETCH_ASSOC); $reservas_array = array(); $equip = array(); $ixx = 0; foreach ($result as $row) { $id_requisicoes = $row['id_requisicoes']; if (!isset($reservas_array[$id_requisicoes])) { $reservas_array[$id_requisicoes]['id_requisicoes'] = $row['id_requisicoes']; $reservas_array[$id_requisicoes]['sala'] = $row['sala']; $reservas_array[$id_requisicoes]['data'] = $row['datas']; $reservas_array[$id_requisicoes]['bloco'] = $row['bloco']; $reservas_array[$id_requisicoes]['nome'] = $row['nome']; $reservas_array[$id_requisicoes]['equip'] = array(); } $reservas_array[$id_requisicoes]['equip'][] = $row['equip']; } foreach($reservas_array as $id_requisicoes => $reservation) { $ixx = $ixx + 1; $tmp = array_map("utf8_decode", $reservation); $data_chegada[] = array_merge($tmp, array('num' => $ixx)); } } } catch (PDOException $e) { echo $e->getMessage(); } $titles = array( 'sala' => '<b>Sala</b>', 'nome' => '<b>Docente</b>', 'data' => '<b>Data</b>', 'bloco' => utf8_decode('<b>Início</b>'), 'equip' =>utf8_decode('<b>Equipamento</b>') ); $parametros_adicionales = array( 'width'=>570, 'showHeadings'=>1, 'fontSize' => 9, 'colGap'=>2, 'xPos' => 'center', 'xOrientation'=>'center', 'cols'=>array( "sala" => array('justification'=>'center', 'width' => '100'), "nome" => array('justification'=>'left', 'width' => '200'), "data" => array('justification'=>'center', 'width' => '100'), "bloco" => array('justification'=>'center', 'width' => '60'), "equip" => array('justification'=>'left', 'width' => '110'))); $escola = utf8_decode("<b>N.º do Código do Estabelecimento de Ensino xxxx/ 201</b>\n"); $contatos = utf8_decode("<b>N.º de Telefone:xxxxxx / Fax:xxxxx</b>\n"); //Posição $pdf->ezImage("../../../img/logo_pdf.jpg", 0,50, 'none', 'left'); $pdf->selectFont("fonts/Helvetica-Bold.afm"); $pdf->addText(250, 480, 11, $escola); $pdf->addText(280, 460, 11, $contatos); $pdf->setLineStyle(1); $pdf ->line(80, 450, 750, 450); $pdf->ezSetY(440); $todayDate = date("d-m-Y",strtotime("tomorrow")); $textoReq= ("Requisições do dia " .$todayDate); $pdf->addText(80, 430, 11, $textoReq); $pdf->ezSetY(415); $pdf->ezTable($data_chegada, $titles, '', $parametros_adicionales); $pdf->addInfo('Author', 'GMC'); ob_end_clean(); $impresso = "<b>Impresso em:</b> ".date("d/m/Y - H:i"); $pdf->addText(340, 30, 11, $impresso); if (isset($_GET['d']) && $_GET['d']){ $pdfcode = $pdf->ezOutput(1); $pdfcode = str_replace("\n","\n<br>",htmlspecialchars($pdfcode)); echo '<html><body>'; echo trim($pdfcode); echo '</body></html>'; } else { $pdf->ezStream(); } ?> The problem is in that line because using var_dump in $reservation is ok, var_dump in $tmp no, check the results var_dump($reservation) array (size=6) 'id_requisicoes' => string '4' (length=1) 'sala' => string '1' (length=1) 'data' => string '2016-09-15' (length=10) 'bloco' => string '08:15 - 09:45' (length=13) 'nome' => string 'XXXXX' (length=12) 'equip' => array (size=2) 0 => string 'Projetor' (length= 1 => string 'Leitor / CD/ USB' (length=16) var_dump($tmp) array (size=6) 'id_requisicoes' => string '4' (length=1) 'sala' => string '1' (length=1) 'data' => string '2016-09-15' (length=10) 'bloco' => string '08:15 - 09:45' (length=13) 'nome' => string 'XXXXXXX' (length=12) 'equip' => null Check the "equip" in both
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.