Jump to content

gmc1103

Members
  • Content Count

    219
  • Joined

  • Last visited

Community Reputation

0 Neutral

About gmc1103

  • Rank
    Advanced Member
  1. Hello In my query i have x activities, then i have the count of how many still to be executed, i need to get the percentage of the total executed I mean i have 200 activities, 20 have been executed, so i want to have 10% done My query is this one but missing the percentage field SELECT COUNT(*) AS total, IFNULL(SUM(CASE WHEN (t1.dataPrevista >= CURDATE() OR ae.Fim <= CURDATE()) THEN 1 ELSE 0 END),0) AS exec FROM atividades AS t1 INNER JOIN anosescolares AS ae ON (t1.`idAnoEscolar` = ae.idAnoEscolar) WHERE ae.Estado = 1; any help?
  2. Hi Barand Thanks for helping me Sorry, i forgot to put the query "SELECT t1.idAtividade, t2.Escola, t1.Atividade, t1.Periodo, t1.Mes, t1.haveClasses, t1.DataPrevista, t1.Destinatarios, t1.Orcamento, t1.Organizador, t1.Obs, t1.PdfAtividade, t1.Avaliacao, t1.idProfessor, t1.PdfAvaliacao, p.Nome, g.Grupo, d.Departamento, t1.Validado FROM atividades AS t1 INNER JOIN atividadesprofessores ap on t1.idAtividade = ap.idAtividade INNER JOIN professores p on ap.idProfessor = p.idProfessor INNER JOIN atividadesgrupos ag on t1.idAtividade = ag.idAtividade INNER JOIN grupos g on ag.idGrupo = g.idGrupo INNER JOIN departamentosatividades da on t1.idAtividade = da.idAtividade INNER JOIN departamentos d on da.idDepartamento = d.idDepartamento INNER JOIN escolas AS t2 ON (t2.idEscola = t1.idEscola) INNER JOIN anosescolares AS ae ON (t1.idAnoEscolar = ae.idAnoEscolar) WHERE t1.idProjeto IS NULL AND ae.Estado = 1 ORDER BY t1.DataPrevista ASC
  3. Hi I have an array from an sql query and i would like to eliminates all the duplicates, i have google search for an answer without success, any help would be aprecciated $result = $stmt->fetchAll(PDO::FETCH_ASSOC); $final = array(); $json = array(); if ($idCoord > 0 || $isDirecao > 0) { foreach ($result as $row) { $idAtividade = $row['idAtividade']; if (!isset($final[$idAtividade])) { $final[$idAtividade]['Escola'] = $row['Escola']; $final[$idAtividade]['Atividade'] = $row['Atividade']; $final[$idAtividade]['Periodo'] = $row['Periodo']; $final[$idAtividade]['Mes'] = $row['Mes']; $final[$idAtividade]['haveClasses'] = $row['haveClasses']; $final[$idAtividade]['DataPrevista'] = $row['DataPrevista']; $final[$idAtividade]['Destinatarios'] = $row['Destinatarios']; $final[$idAtividade]['Orcamento'] = $row['Orcamento']; $final[$idAtividade]['Organizador'] = $row['Organizador']; $final[$idAtividade]['Obs'] = $row['Obs']; $final[$idAtividade]['PdfAtividade'] = $row['PdfAtividade']; $final[$idAtividade]['Avaliacao'] = $row['Avaliacao']; $final[$idAtividade]['idProfessor'] = $row['idProfessor']; $final[$idAtividade]['PdfAvaliacao'] = $row['PdfAvaliacao']; $final[$idAtividade]['Validado'] = $row['Validado']; $final[$idAtividade]['Nome'] = array(); $final[$idAtividade]['Grupo'] = array(); $final[$idAtividade]['Departamento'] = array(); } $final[$idAtividade]['Nome'][] = $row['Nome']; $final[$idAtividade]['Grupo'][] = $row['Grupo']; $final[$idAtividade]['Departamento'][] = $row['Departamento']; } foreach ($final as $idVisita => $reservation) { $json[] = $reservation; } } echo json_encode($json); } And this is an example i'm receiving So you can see that Grupo has 4 times the value "500" and Departamento has 4 times that string... How can avoid this and have only one value of each? Thanks
  4. Hi Barand Done, works now. You are the best Thanks
  5. Yes it says OK, no errors messages But i have only record inserted....
  6. Hi Barand The first loop foreach ($idEscola as $id_escola) { echo $id_escola."<br>"; } Is was just to test if i was receiving the array correctly and yes it works But then i have the code (remain) to insert into the database and there...the code is only executed one time .....and it should be 3 times Remain code to insert into database if($getDisponibilidadeData ==0){ foreach ($idEscola as $id_escola) { try { $stmt = $this->db->prepare("INSERT INTO atividades (Atividade,Periodo,Mes,DataPrevista,Hora, Duracao, Destinatarios,Organizador,Orcamento, Obs,idProjeto,idEscola,idProfessor,idAnoEscolar, Criado) VALUES(:Atividade,:Periodo,:Mes,:DataPrevista,:Hora,:Duracao,:Destinatarios,:Organizador,:Orcamento,:Obs,:idProjeto, :idEscola,:idProfessor,:idAnoEscolar, NOW());"); $stmt->bindparam(":Atividade", $novaAtividade); $stmt->bindparam(":Periodo", $periodo); $stmt->bindparam(":Mes", $previsto); $stmt->bindparam(":DataPrevista", $previsao); $stmt->bindparam(":Hora", $hora); $stmt->bindparam(":Duracao", $duracao); $stmt->bindparam(":Destinatarios", $destinatarios); $stmt->bindparam(":Organizador", $organizador); $stmt->bindparam(":Orcamento", $orcamento); $stmt->bindparam(":Obs", $observacoes); $stmt->bindparam(":idProjeto", $id_projeto); $stmt->bindparam(":idEscola", $id_escola); $stmt->bindparam(":idProfessor", $id_professor); $stmt->bindparam(":idAnoEscolar", $id_ano); $dominio = explode(',', $dominios); $result = $stmt->execute(); $idAtividade = $this->db->lastInsertId(); foreach ($dominio as $idDominios) { $stmt->bindParam(':idDominios', $idDominios, PDO::PARAM_INT); $this->setTabelaAtividadesDominios($idAtividade, $idDominios); } $dpt = $this->getUserDepartamentoById($id_professor); $this->setTabelaDepartamentosAtividades($dpt, $idAtividade); $escola = $this->getEscolaById($id_escola); $professor = $this->getNomeById($id_professor); $nome_dominios = $this->getNomeDominios($idAtividade); $nome_projeto = $this->getNomeProjetos($id_projeto); if ($nome_projeto == '') { $nome_projeto = "Sem Projeto/Clube associado"; } $this->AtividadeProjetoPdf($idAtividade, $escola, $nome_projeto, $novaAtividade, $periodo, $previsao, $previsto, $nome_dominios, $destinatarios, $organizador, $orcamento, $observacoes, $professor); if (!$result) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Not done...'); } else { return array('status' => 'success', 'message' => 'Record done...'); } } catch (PDOException $ex) { echo $ex->getMessage(); } } } else{ return array('status' => 'error', 'message' => 'OK '); } and this is executed one time only and should be 3 times since my array has 3 values.. Thanks
  7. Hello I'm having a problem using foreach to send records into database This is my code foreach ($idEscola as $id_escola) { echo $id_escola."<br>"; } if($getDisponibilidadeData ==0){ foreach ($idEscola as $id_escola) { try { $stmt = $this->db->prepare("INSERT INTO atividades (Atividade,Periodo,Mes,DataPrevista,Hora, Duracao, Destinatarios,Organizador,Orcamento, Obs,idProjeto,idEscola,idProfessor,idAnoEscolar, Criado) VALUES(:Atividade,:Periodo,:Mes,:DataPrevista,:Hora,:Duracao,:Destinatarios,:Organizador,:Orcamento,:Obs,:idProjeto, :idEscola,:idProfessor,:idAnoEscolar, NOW());"); $stmt->bindparam(":Atividade", $novaAtividade); $stmt->bindparam(":Periodo", $periodo); $stmt->bindparam(":Mes", $previsto); $stmt->bindparam(":DataPrevista", $previsao); $stmt->bindparam(":Hora", $hora); $stmt->bindparam(":Duracao", $duracao); $stmt->bindparam(":Destinatarios", $destinatarios); $stmt->bindparam(":Organizador", $organizador); $stmt->bindparam(":Orcamento", $orcamento); $stmt->bindparam(":Obs", $observacoes); $stmt->bindparam(":idProjeto", $id_projeto); $stmt->bindparam(":idEscola", $id_escola); $stmt->bindparam(":idProfessor", $id_professor); $stmt->bindparam(":idAnoEscolar", $id_ano); $dominio = explode(',', $dominios); $result = $stmt->execute(); $idAtividade = $this->db->lastInsertId(); foreach ($dominio as $idDominios) { $stmt->bindParam(':idDominios', $idDominios, PDO::PARAM_INT); $this->setTabelaAtividadesDominios($idAtividade, $idDominios); } $dpt = $this->getUserDepartamentoById($id_professor); $this->setTabelaDepartamentosAtividades($dpt, $idAtividade); $escola = $this->getEscolaById($id_escola); $professor = $this->getNomeById($id_professor); $nome_dominios = $this->getNomeDominios($idAtividade); $nome_projeto = $this->getNomeProjetos($id_projeto); if ($nome_projeto == '') { $nome_projeto = "Sem Projeto/Clube associado"; } $this->AtividadeProjetoPdf($idAtividade, $escola, $nome_projeto, $novaAtividade, $periodo, $previsao, $previsto, $nome_dominios, $destinatarios, $organizador, $orcamento, $observacoes, $professor); if (!$result) { print_r($stmt->errorInfo()); return array('status' => 'error', 'message' => 'Not done...'); } else { return array('status' => 'success', 'message' => 'Record done...'); } } catch (PDOException $ex) { echo $ex->getMessage(); } } } else{ return array('status' => 'error', 'message' => 'OK '); } So...to test if the first foreach is working, i did a test and i receive the values like expected. 1 2 3 The problem is when i enter into the try catch only the first value is inserted into the database 1 I should have 3 records and i have only one Any help? Thanks
  8. 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
  9. 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?
  10. 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 ..
  11. 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?
  12. Yes, i have fixed my problem. Thank you for your indications to have a better code. Best regards
  13. 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
  14. 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?
×
×
  • 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.