Jump to content

gmc1103

Members
  • Content count

    211
  • Joined

  • Last visited

Community Reputation

0 Neutral

About gmc1103

  • Rank
    Advanced Member
  1. gmc1103

    Many to many php array

    Hi Barand It was an example. The database structure is what i'm using. (domains and classes) I'm still testing what it the best way to achieve what i'm pretend
  2. gmc1103

    Many to many php array

    Hi Barand How are you? Thank you for the reply... It is better to show my database structure and point of that structure So the main table is "Visitas" then i have one table "Domains" (Dominios) and another one "Classes" (Turmas) As you can see, i have 2 other tables related between "Visitas" and the other 2 mentioned. Why table between "Visitas" and "Classes" (Turmas)? Table "VisitasTurmas" Because a visit can have several classes and in each classes not all students (field : numAlunos) could go to that visit. What is a "Domain" ? It's anual school plans (26 domains can be choosen) Why table between "Visitas" and "Domains" (Dominios)? Table "VisitasDominios" Because each visit have at least 2 and max of 6 domains and then i will have to use a chart where the principle can analyse wich domains are most used. Now my example when i have inserted a new study visit I have choosed 6 domains Table "VisitasDominios" (fields: idAnoEscolar (school year) , idVisita (generated id from table "Visitas"), idDominio (id from table Dominios) N-1) idAnoEscolar idVisita idDominio ------------ -------- ----------- 1 1 2 1 1 3 1 1 6 1 1 10 1 1 15 1 1 18 , and 4 Classes Table "VisitasTurmas" ( idVisita (generated id from table "Visitas"), idTurma (id from table Turmas) N-1, numAlunos (number of students going to the visit), idAnoEscolar (school year)) idVisita idTurma numAlunos idAnoEscolar -------- ------- --------- -------------- 1 1 12 1 1 2 3 1 1 3 23 1 1 4 14 1 So when i run a simplified version of my query it gives me idVisita Escola Domains Classes Destino StartDate numAlunos TotalAluno -------- ------ ------------------------------------------------ ------ ------- ---------- --------- ------------ 1 Carmo 1.2 - Motivação e Empenho 1ºA wq 2017-08-28 12 234 1 Carmo 1.2 - Motivação e Empenho 1ºB wq 2017-08-28 3 234 1 Carmo 1.2 - Motivação e Empenho 2ºA wq 2017-08-28 23 234 1 Carmo 1.2 - Motivação e Empenho 2ºB wq 2017-08-28 14 234 1 Carmo 1.3 - Abertura à Inovação 1ºA wq 2017-08-28 12 234 1 Carmo 1.3 - Abertura à Inovação 1ºB wq 2017-08-28 3 234 1 Carmo 1.3 - Abertura à Inovação 2ºA wq 2017-08-28 23 234 1 Carmo 1.3 - Abertura à Inovação 2ºB wq 2017-08-28 14 234 1 Carmo 2.1 - Infraestruturas 1ºA wq 2017-08-28 12 234 1 Carmo 2.1 - Infraestruturas 1ºB wq 2017-08-28 3 234 1 Carmo 2.1 - Infraestruturas 2ºA wq 2017-08-28 23 234 1 Carmo 2.1 - Infraestruturas 2ºB wq 2017-08-28 14 234 1 Carmo 2.5 - Escola para todos 1ºA wq 2017-08-28 12 234 1 Carmo 2.5 - Escola para todos 1ºB wq 2017-08-28 3 234 1 Carmo 2.5 - Escola para todos 2ºA wq 2017-08-28 23 234 1 Carmo 2.5 - Escola para todos 2ºB wq 2017-08-28 14 234 1 Carmo 3.1.3 - Diferenciação e apoio 1ºA wq 2017-08-28 12 234 1 Carmo 3.1.3 - Diferenciação e apoio 1ºB wq 2017-08-28 3 234 1 Carmo 3.1.3 - Diferenciação e apoio 2ºA wq 2017-08-28 23 234 1 Carmo 3.1.3 - Diferenciação e apoio 2ºB wq 2017-08-28 14 234 1 Carmo 3.1.6 - Valorização do saber ser e saber estar 1ºA wq 2017-08-28 12 234 1 Carmo 3.1.6 - Valorização do saber ser e saber estar 1ºB wq 2017-08-28 3 234 1 Carmo 3.1.6 - Valorização do saber ser e saber estar 2ºA wq 2017-08-28 23 234 1 Carmo 3.1.6 - Valorização do saber ser e saber estar 2ºB wq 2017-08-28 14 234 So as you can see, 6 domains and 4 classes gives me 24 records wich is right With your query it gives me this idVisita Escola Nome Classes Destino StartDate numAlunos TotalAluno -------- ------ ------------------------------- ----------------------------------------------------------------------------------------------------------------------- ------- ---------- --------- ------------ 1 Carmo Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 2ºA,1ºA,2ºB,2ºA,1ºA,2ºB,1ºB,1ºA,2ºA,1ºB,2ºA,1ºB,2ºB,2ºA,1ºA,2ºB,1ºB,1ºA,2ºB,1ºB,1ºA,2ºA,1ºB,2ºB wq 2017-08-28 23 234 Wich is not what i pretend, if i have only 4 classes it is repeated It is well explained?
  3. gmc1103

    Many to many php array

    Hi Thank you for your reply. No it is not that. This is a model where a user (teacher) can schedule a study visit with several classes. One visit can have one or many class. So i have one table visit, another table class and one table many to many with visit details. So when i run that query if i have 4 classes for one visit i only want to have one line with: Visit Destination Date Class AAAA 2017-08-30 Class A, Class B, Class C, Class D instead of 4 records ..
  4. gmc1103

    Many to many php array

    Hi I'm having trouble regarding having a record with only one line instead of several records I have an issue with the following: A user choose a place, and that place can have several subplaces each one with an id, so i have one main table, another one with places and other many to many. When i use a select (pdo) gives me an array, the problem is since the user choose for example 5 places, a i have five records and i would like to have only one. This how i'm doing but no success $result = $stmt->fetchAll(PDO::FETCH_ASSOC); $final = array(); foreach ($result as $row) { $idVisita = $row['idVisita']; if (!isset($final[$idVisita])) { $final[$idVisita]['idVisita'] = $row['idVisita']; $final[$idVisita]['school'] = $row['school']; $final[$idVisita]['Departement'] = $row['Departement']; $final[$idVisita]['Name'] = $row['Name']; $final[$idVisita]['Class'] = array(); $final[$idVisita]['Destination'] = $row['Destination']; $final[$idVisita]['StartDate'] = $row['StartDate']; $final[$idVisita]['number'] = $row['Number']; $final[$idVisita]['Total'] = $row['Total']; $final[$idVisita]['Pdf'] = $row['PdfV']; $final[$idVisita]['Evaluation'] = $row['Evaluation']; $final[$idVisita]['Description'] = array(); } $final[$idVisita]['Description'][] = $row['Description']; $final[$idVisita]['Class'][] = $row['Class']; } foreach ($final as $idVisita => $reservation) { $json[]=$reservation; } echo json_encode($json); So but this code you can see i must have 2 arrays to join "Class" and "Description". When i use this code i have wrong records. Instead of having 4 "Turmas" i have something like this 2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºA,1ºB,2ºA,2ºA,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA,1ºB,1ºB,2ºA,2ºB,2ºB,1ºA And it's easy to see they repeated... This is my query SELECT v.idVisita, e.Escola, d.Departamento, dom.Descricao, p.Nome, t.Turma, v.Destino, v.StartDate, vt.numAlunos, v.TotalAluno,v.PdfVisita, v.Avaliacao FROM visitas AS v INNER JOIN escolas AS e ON (v.idEscola = e.idEscola) INNER JOIN visitasturmas AS vt ON (v.idVisita = vt.idVisita) INNER JOIN turmas AS t ON (vt.idTurma = t.idTurma) INNER JOIN professores AS p ON (v.idProfessor = p.idProfessor) INNER JOIN visitasdepartamentos AS vd ON (v.idVisita = vd.idVisita) INNER JOIN departamentos AS d ON (vd.idDepartamento = d.idDepartamento) INNER JOIN visitasdominios AS vds ON(v.idVisita = vds.idVisita) INNER JOIN dominios AS dom ON (vds.idDominio = dom.idDominios) ORDER BY v.StartDate Any help?
  5. gmc1103

    array multidimensional error

    Yes, i have fixed my problem. Thank you for your indications to have a better code. Best regards
  6. gmc1103

    array multidimensional error

    Hi Jacques How are you? About your questions You have a try statement to catch PDO exceptions and print them on the screen (which is stupid – leave them alone). Yes, is to check if everything is ok, after the code is ok, i remove it. What's the point of using a prepared statement for a query which doesn't have any parameters and is only executed once. You are right, i agree with you. When you iterate over your $aulas_array, you call json_encode() but don't store the return value anywhere. Again dummy from me. I was returning the array after the 1º iteration. Regards
  7. Hi I'm having a problem regarding getting the values off my array.... I'm calling a function to get values public function getAllAulasSemDespacho(){ try { $stmt = $this->db->prepare("SELECT a.Atividade, a.Local, a.DataAula, ae.idAula, p.Nome, t.Turma FROM aulaexteriorturmas AS ae INNER JOIN turmas AS t ON (ae.idTurma = t.idTurma) INNER JOIN aulaexterior AS a ON (ae.idAula = a.idAula) INNER JOIN professores AS p ON (a.idProfessor = p.idProfessor) WHERE a.Autorizado IS NULL "); if (!$stmt->execute()) { print_r($stmt->errorInfo()); return false; } else { $result = $stmt->fetchAll(PDO::FETCH_ASSOC); $aulas_array = array(); foreach ($result as $row) { $idAula = $row['idAula']; if (!isset($aulas_array[$idAula])) { $aulas_array[$idAula]['idAula'] = $row['idAula']; $aulas_array[$idAula]['Atividade'] = $row['Atividade']; $aulas_array[$idAula]['Local'] = $row['Local']; $aulas_array[$idAula]['DataAula'] = $row['DataAula']; $aulas_array[$idAula]['Nome'] = $row['Nome']; $aulas_array[$idAula]['Turma'] = array(); } $aulas_array[$idAula]['Turma'][] = $row['Turma']; } foreach ($aulas_array as $idAula => $aulas) { json_encode($aulas); return $aulas; } } } catch (PDOException $e) { echo $e->getMessage(); } } From the query...in my sql i receive Atividade Local DataAula idAula Nome Turma --------------------------------- --------------------- ---------- ------ ---------------------- -------- Teste final PDF Algarve 2017-08-31 5 xxxxxxxxxxxxxxxxxxxxxx 5ºC Teste final PDF Algarve 2017-08-31 5 xxxxxxxxxxxxxxxxxxxxxx 6ºB Teste final PDF Algarve 2017-07-27 6 xxxxxxxxxxxxxxxxxxxxxx 10ºO Teste final PDF Algarve 2017-07-27 6 xxxxxxxxxxxxxxxxxxxxxx 11ºJ Teste final PDF Algarve 2017-07-27 6 xxxxxxxxxxxxxxxxxxxxxx 12ºL So i want to join the same (Turma) values by (idAula) When i want to see in the front end i receive an array...but i get illegal offset errors <?php if (!empty($aulasListagem)) { foreach ($aulasListagem as $row) { print_r($row); ?> <tr> <td align="center"><?php echo $row['Atividade']; ?></td> <td align="center"><?php echo $row['Local']; ?></td> <td align="center"><?php echo $row['DataAula']; ?></td> <td align="center"><?php echo $row['Nome']; ?></td> <td align="center"><?php echo $row['Turma']; ?></td> <td><a data-toggle="modal" data-id="<?php echo $row['idAula']; ?>" class="open-AddDialog btn btn-sm btn-success center-block" href="#myModalDespachoAula">Opção</a></td> </tr> <?php } } ?> From var_dump i'm getting this array(6) { ["idAula"]=> string(1) "5" ["Atividade"]=> string(33) "Algarve" ["Local"]=> string(7) "Algarve" ["DataAula"]=> string(10) "2017-08-31" ["Nome"]=> string(22) "xxxxxxxxx" ["Turma"]=> array(2) { [0]=> string(4) "5ºC" [1]=> string(4) "6ºB" } } I only receive the first idAula and i get illegal offset errors in the table any help?
  8. gmc1103

    Ranking query problem

    Hi Jacques Again thank you for your help. It is really good idea what you are suggesting. I had 2 tables but then they ask me to have evaluation table and to don't make any disaster on those tables i have created another one...that's why i have 3. Let me explain how does it works A teacher should be able to shedule an activity. The activity has from other tables (project, user, year) and she must have an evaluation with some unique fields So i made this new database, can you give me your opinion please? All the fields in english Image here https://ibb.co/dN07Fv Thank you
  9. gmc1103

    Ranking query problem

    Hi Jacques How are you? Thank you for your answer, it works. I have all the names and the activities per user and i want to know how many have been evaluated. This information is kept in another table (yes, is no need, i need to rebuild this) CREATE TABLE `rel_atividades_avaliacao` ( `id_avaliacao` int(11) NOT NULL AUTO_INCREMENT, `id_atividades` int(11) NOT NULL, `avaliado` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT '-1', `data_prevista` varchar(50) COLLATE utf8_bin NOT NULL, `motivo` varchar(300) COLLATE utf8_bin DEFAULT '-', `adequacaos` int(11) DEFAULT NULL, `participacaos` int(11) DEFAULT NULL, `consecucaos` int(11) DEFAULT NULL, `disps` int(11) DEFAULT NULL, `mats` int(11) DEFAULT NULL, `balanco` varchar(300) COLLATE utf8_bin DEFAULT NULL, `ficheiro` varchar(300) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id_avaliacao`), KEY `id_atividades` (`id_atividades`), CONSTRAINT `rel_atividades_avaliacao_ibfk_1` FOREIGN KEY (`id_atividades`) REFERENCES `atividades` (`id_atividades`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=563 DEFAULT CHARSET=utf8 COLLATE=utf8_bin You can see that "id_atividades" is the foreign key in this table Image from this table Check the image in (https://ibb.co/hJQUdF) So i need to know from "avaliado" how many from each user is different from -1 (not evaluated) Is possible or i need to change everything now? Thanks
  10. gmc1103

    Ranking query problem

    Hi I'm having a problem regarding the following query Error Code: 1242 Subquery returns more than 1 row SET @Rank := 0; SELECT @curRank := @curRank + 1 AS rank, u.nome, (SELECT COUNT(r.id_utilizador) FROM rel_atividades_utilizador)AS total FROM rel_atividades_utilizador AS r INNER JOIN utilizador AS u ON r.id_utilizador = u.id_utilizador, (SELECT @curRank := 0) t ORDER BY (total) DESC I have a table who keeps the id_utilizador (user_id) each time this user save new info into this table I want to know the ranking of each users For instance user x as 43 posts, so my query should return rank name total 1 X 43 2 Y 40 etc Any help with this query? Thanks
  11. gmc1103

    Sql query -Help

    Hi I need to make a query to get all data, this is the one DECLARE @ReportYear INT DECLARE @ReportMonth INT DECLARE @EmployeeId INT set @ReportYear = 2014 set @ReportMonth = 12 select distinct(pc.Name) as Categoria, DATEPART(YEAR, soh.OrderDate) as [ano], sum((det.UnitPrice-det.UnitPriceDiscount)*det.OrderQty) as total, sum(soh.Freight) as transporte, SUM(det.LineTotal) as vendas from Sales.SalesOrderHeader as soh inner join Sales.SalesPerson as sp on sp.BusinessEntityID = soh.SalesPersonID inner join Sales.SalesOrderDetail as det on soh.SalesOrderID = det.SalesOrderID inner join HumanResources.Employee as e on soh.SalesPersonID = e.BusinessEntityID inner join Person.Person as per on per.BusinessEntityID = sp.BusinessEntityID inner join Production.Product as p on det.ProductID = p.ProductID inner join Production.ProductSubcategory as ps on p.ProductSubcategoryID = ps.ProductSubcategoryID inner join Production.ProductCategory as pc on ps.ProductCategoryID = pc.ProductCategoryID where ((DATEPART(YEAR, soh.OrderDate) = @ReportYear) or (DATEPART(YEAR, soh.OrderDate) <= @ReportYear)) group by DATEPART(YEAR, soh.OrderDate),soh.Freight, pc.Name order by DATEPART(YEAR, soh.OrderDate); But the data comes with all repeated I want to have just one Acessories/Clothes/Bicicle, etc and one time 2011/2012 etc... Any help?
  12. gmc1103

    Chart from my table data

    Hi Thank you. For the first item yes it does, but i have 5 items, then i want the percentage of each one.
  13. gmc1103

    Chart from my table data

    Hi I need some help regarding a chart i'm trying to make regarding my table data This is the structure CREATE TABLE `rel_atividades_avaliacao` ( `id_avaliacao` int(11) NOT NULL AUTO_INCREMENT, `id_atividades` int(11) NOT NULL, `avaliado` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT '-1', `data_prevista` varchar(50) COLLATE utf8_bin NOT NULL, `motivo` varchar(300) COLLATE utf8_bin DEFAULT '-', `adequacaos` int(11) NOT NULL, `participacaos` int(11) NOT NULL, `consecucaos` int(11) NOT NULL, `disps` int(11) NOT NULL, `mats` int(11) NOT NULL, `balanco` varchar(300) COLLATE utf8_bin NOT NULL, `ficheiro` varchar(300) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id_avaliacao`), KEY `id_atividades` (`id_atividades`), CONSTRAINT `rel_atividades_avaliacao_ibfk_1` FOREIGN KEY (`id_atividades`) REFERENCES `atividades` (`id_atividades`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=533 DEFAULT CHARSET=utf8 COLLATE=utf8_bin The chart should be regarding those 5 elements `adequacaos` int(11) NOT NULL, `participacaos` int(11) NOT NULL, `consecucaos` int(11) NOT NULL, `disps` int(11) NOT NULL, `mats` int(11) NOT NULL, Those 5 elements accept numbers between 1 to 5, 1(poor) and 5 (Excellent) So my chart must be how many times 1, 2,3,4,5 has been chosed in each table atributes Any help? Thanks
  14. gmc1103

    Database implementation suggestion

    Thanks But i keep the rel_visitas_utilizador table? with id_visita and id_user?
×

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.