x-man1 Posted November 27, 2014 Share Posted November 27, 2014 hello ..I have 3 tables: student - infractions - Actions and view1 it Includes all tables and i make view1 source my page.my database structure:Table: studentColumns:ID_student int(11) AI PK name varchar(45) Age varchar(45) Grade varchar(45)Table: infractionsColumns:ID_infractions int(11) AI PK infractions_text varchar(45) ID_student int(11)Table: actionsColumns:ID_Actions int(11) AI PK Actions_text varchar(45) ID_student int(11)view1SELECT student.ID_student AS ID_student,student.name AS name,student.Age AS Age,student.Grade AS Grade,infractions.ID_infractions AS ID_infractions,infractions.infractions_text AS infractions_text,actions.ID_Actions AS ID_Actions,actions.Actions_text AS Actions_textFROM (studentJOIN infractions ON student.ID_student = infractions.ID_student)JOIN actions ON student.ID_student = actions.ID_studentphp page code: PHP Code: </script> <?php } ?> <div class="ewToolbar"> <?php if ($Report1->Export == "") { ?> <?php $Breadcrumb->Render(); ?> <?php } ?> <?php if ($Report1->Export == "") { ?> <?php echo $Language->SelectionForm(); ?> <?php } ?> <div class="clearfix"></div> </div> <?php $Report1_report->DefaultFilter = ""; $Report1_report->ReportFilter = $Report1_report->DefaultFilter; if ($Report1_report->DbDetailFilter <> "") { if ($Report1_report->ReportFilter <> "") $Report1_report->ReportFilter .= " AND "; $Report1_report->ReportFilter .= "(" . $Report1_report->DbDetailFilter . ")"; } // Set up filter and load Group level sql $Report1->CurrentFilter = $Report1_report->ReportFilter; $Report1_report->ReportSql = $Report1->GroupSQL(); // Load recordset $Report1_report->Recordset = $conn->Execute($Report1_report->ReportSql); $Report1_report->RecordExists = !$Report1_report->Recordset->EOF; ?> <?php if ($Report1->Export == "") { ?> <?php if ($Report1_report->RecordExists) { ?> <div class="ewViewExportOptions"><?php $Report1_report->ExportOptions->Render("body") ?></div> <?php } ?> <?php } ?> <?php $Report1_report->ShowPageHeader(); ?> <form method="post"> <?php if ($Report1_report->CheckToken) { ?> <input type="hidden" name="<?php echo EW_TOKEN_NAME ?>" value="<?php echo $Report1_report->Token ?>"> <?php } ?> <table class="ewReportTable"> <?php // Get First Row if ($Report1_report->RecordExists) { $Report1->ID_infractions->setDbValue($Report1_report->Recordset->fields('ID_infractions')); $Report1_report->ReportGroups[0] = $Report1->ID_infractions->DbValue; $Report1->ID_Actions->setDbValue($Report1_report->Recordset->fields('ID_Actions')); $Report1_report->ReportGroups[1] = $Report1->ID_Actions->DbValue; } $Report1_report->RecCnt = 0; $Report1_report->ReportCounts[0] = 0; $Report1_report->ChkLvlBreak(); while (!$Report1_report->Recordset->EOF) { // Render for view $Report1->RowType = EW_ROWTYPE_VIEW; $Report1->ResetAttrs(); $Report1_report->RenderRow(); // Show group headers if ($Report1_report->LevelBreak[1]) { // Reset counter and aggregation ?> <tr><td colspan=2 class="ewGroupField"><?php echo $Report1->ID_infractions->FldCaption() ?></td> <td colspan=2 class="ewGroupName"> <span<?php echo $Report1->ID_infractions->ViewAttributes() ?>> <?php echo $Report1->ID_infractions->ViewValue ?></span> </td></tr> <?php } if ($Report1_report->LevelBreak[2]) { // Reset counter and aggregation ?> <tr><td><div class="ewGroupIndent"></div></td><td class="ewGroupField"><?php echo $Report1->ID_Actions->FldCaption() ?></td> <td colspan=2 class="ewGroupName"> <span<?php echo $Report1->ID_Actions->ViewAttributes() ?>> <?php echo $Report1->ID_Actions->ViewValue ?></span> </td></tr> <?php } // Get detail records $Report1_report->ReportFilter = $Report1_report->DefaultFilter; if ($Report1_report->ReportFilter <> "") $Report1_report->ReportFilter .= " AND "; if (is_null($Report1->ID_infractions->CurrentValue)) { $Report1_report->ReportFilter .= "(`ID_infractions` IS NULL)"; } else { $Report1_report->ReportFilter .= "(`ID_infractions` = " . ew_AdjustSql($Report1->ID_infractions->CurrentValue) . ")"; } if ($Report1_report->ReportFilter <> "") $Report1_report->ReportFilter .= " AND "; if (is_null($Report1->ID_Actions->CurrentValue)) { $Report1_report->ReportFilter .= "(`ID_Actions` IS NULL)"; } else { $Report1_report->ReportFilter .= "(`ID_Actions` = " . ew_AdjustSql($Report1->ID_Actions->CurrentValue) . ")"; } if ($Report1_report->DbDetailFilter <> "") { if ($Report1_report->ReportFilter <> "") $Report1_report->ReportFilter .= " AND "; $Report1_report->ReportFilter .= "(" . $Report1_report->DbDetailFilter . ")"; } // Set up detail SQL $Report1->CurrentFilter = $Report1_report->ReportFilter; $Report1_report->ReportSql = $Report1->DetailSQL(); // Load detail records $Report1_report->DetailRecordset = $conn->Execute($Report1_report->ReportSql); $Report1_report->DtlRecordCount = $Report1_report->DetailRecordset->RecordCount(); // Initialize aggregates if (!$Report1_report->DetailRecordset->EOF) { $Report1_report->RecCnt++; } if ($Report1_report->RecCnt == 1) { $Report1_report->ReportCounts[0] = 0; } for ($i = 1; $i <= 2; $i++) { if ($Report1_report->LevelBreak[$i]) { // Reset counter and aggregation $Report1_report->ReportCounts[$i] = 0; } } $Report1_report->ReportCounts[0] += $Report1_report->DtlRecordCount; $Report1_report->ReportCounts[1] += $Report1_report->DtlRecordCount; $Report1_report->ReportCounts[2] += $Report1_report->DtlRecordCount; if ($Report1_report->RecordExists) { ?> <tr> <td><div class="ewGroupIndent"></div></td> <td><div class="ewGroupIndent"></div></td> <td class="ewGroupHeader"><?php echo $Report1->infractions_text->FldCaption() ?></td> <td class="ewGroupHeader"><?php echo $Report1->Actions_text->FldCaption() ?></td> </tr> <?php } while (!$Report1_report->DetailRecordset->EOF) { $Report1->infractions_text->setDbValue($Report1_report->DetailRecordset->fields('infractions_text')); $Report1->Actions_text->setDbValue($Report1_report->DetailRecordset->fields('Actions_text')); // Render for view $Report1->RowType = EW_ROWTYPE_VIEW; $Report1->ResetAttrs(); $Report1_report->RenderRow(); ?> <tr> <td><div class="ewGroupIndent"></div></td> <td><div class="ewGroupIndent"></div></td> <td<?php echo $Report1->infractions_text->CellAttributes() ?>> <span<?php echo $Report1->infractions_text->ViewAttributes() ?>> <?php echo $Report1->infractions_text->ViewValue ?></span> </td> <td<?php echo $Report1->Actions_text->CellAttributes() ?>> <span<?php echo $Report1->Actions_text->ViewAttributes() ?>> <?php echo $Report1->Actions_text->ViewValue ?></span> </td> </tr> <?php $Report1_report->DetailRecordset->MoveNext(); } $Report1_report->DetailRecordset->Close(); // Save old group data $Report1_report->ReportGroups[0] = $Report1->ID_infractions->CurrentValue; $Report1_report->ReportGroups[1] = $Report1->ID_Actions->CurrentValue; // Get next record $Report1_report->Recordset->MoveNext(); if ($Report1_report->Recordset->EOF) { $Report1_report->RecCnt = 0; // EOF, force all level breaks } else { $Report1->ID_infractions->setDbValue($Report1_report->Recordset->fields('ID_infractions')); $Report1->ID_Actions->setDbValue($Report1_report->Recordset->fields('ID_Actions')); } $Report1_report->ChkLvlBreak(); // Show footers if ($Report1_report->LevelBreak[2]) { $Report1->ID_Actions->CurrentValue = $Report1_report->ReportGroups[1]; // Render row for view $Report1->RowType = EW_ROWTYPE_VIEW; $Report1->ResetAttrs(); $Report1_report->RenderRow(); $Report1->ID_Actions->CurrentValue = $Report1->ID_Actions->DbValue; ?> <?php } if ($Report1_report->LevelBreak[1]) { $Report1->ID_infractions->CurrentValue = $Report1_report->ReportGroups[0]; // Render row for view $Report1->RowType = EW_ROWTYPE_VIEW; $Report1->ResetAttrs(); $Report1_report->RenderRow(); $Report1->ID_infractions->CurrentValue = $Report1->ID_infractions->DbValue; ?> <?php } } // Close recordset $Report1_report->Recordset->Close(); ?> <?php if ($Report1_report->RecordExists) { ?> <tr><td colspan=4> <br></td></tr> <tr><td colspan=4 class="ewGrandSummary"><?php echo $Language->Phrase("RptGrandTotal") ?> (<?php echo ew_FormatNumber($Report1_report->ReportCounts[0], 0) ?> <?php echo $Language->Phrase("RptDtlRec") ?>)</td></tr> <?php } ?> <?php if ($Report1_report->RecordExists) { ?> <tr><td colspan=4> <br></td></tr> <?php } else { ?> <tr><td><?php echo $Language->Phrase("NoRecord") ?></td></tr> <?php } ?> </table> </form> <?php $Report1_report->ShowPageFooter(); if (EW_DEBUG_ENABLED) echo ew_DebugMsg(); ?> <?php if ($Report1->Export == "") { ?> <script type="text/javascript"> // Write your table-specific startup script here // document.write("page loaded"); </script> Showing page result : it Duplicate records !! ------------------------------------------------------------------------------- i need it to be like this : my database records : student table infractions-table: actions-table: please help me to find an amendment to the code Gratefully Report1report.php Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/ Share on other sites More sharing options...
DavidAM Posted November 27, 2014 Share Posted November 27, 2014 There is no relationship between Actions and Infractions. Therefore all Actions (for a student) are associated with all Infractions (for that student) - a Cartesian product. The relation between Students and Infractions is one-to-many (one Student can have many Infractions). If a single Infraction can have only a single Action, I would eliminate the Actions table and put the Action_Text in the Infractions table -- leave it NULL until the action is applied. If an Infraction could result in one or more Actions (or you really want/need a separte table), the Actions table should be realted to the Infractions table NOT the Student table. Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/#findComment-1497869 Share on other sites More sharing options...
x-man1 Posted November 27, 2014 Author Share Posted November 27, 2014 Relationship in this form suitable for me completely, but what I need is to solve the problem of repeat records .. Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/#findComment-1497875 Share on other sites More sharing options...
DavidAM Posted November 27, 2014 Share Posted November 27, 2014 What records do you think are duplicate? I don't see any. I see a Cartesian Product between the Infractions and Actions table. There is no relationship and no join between these tables, so every row for Student 7 in Infractions is associated with every row in Actions for Student 7. If an Action is not intrinsically associated with a specific Infraction, then you cannot report them that way. If they are associated, then the database design needs to be fixed. Concerning Student #7 ("ahmad") is Actions #1 ("Actions 1") associated with Infractions #1 ("infractions 1") or Infractions # 2 ("infractions 2")? Is Actions #2 ("Actions 2") associated with "infractions 1" or "infractions 2"? Your database design does NOT provide any relationship (association) other than the Student ID. So, all Infractions for Student 7 are associated with all Actions for Student 7 -- and that is EXACTLY what the output is telling you. Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/#findComment-1497878 Share on other sites More sharing options...
Barand Posted November 27, 2014 Share Posted November 27, 2014 Here is how I would do it $sql = "SELECT ID_student , name , Age , Grade , ID_infractions , infractions_text , ID_Actions , Actions_text FROM view1 ORDER BY ID_student,ID_infractions,ID_Actions"; $res = $db->query($sql); $currid = 0; $currName = $currAge = $currGrade = ''; $data = array(); $heads = "<tr><th>Name</th><th>Age</th><th>Grade</th><th>Infractions</th><th>Actions</th></tr>"; $tdata = ''; while (list($sid,$name,$age,$grd,$infid,$inftxt,$actid,$acttxt) = $res->fetch_row()) { // CHECK FOR CHANGE IN ID if ($sid != $currid) { if ($currid) { // OUTPUT DATA FOR STUDENT WHEN WE GET A NEW ID $tdata .= "<tr valign='top'><td>$currName</td><td>$currAge</td><td>$currGrade</td><td>"; foreach ($data['infracts'] as $k=>$v) { $tdata .= "$k $v<br>"; } $tdata .= "</td><td>"; foreach ($data['actions'] as $k=>$v) { $tdata .= "$k $v<br>"; } $tdata .= "</td></tr>\n"; } // STORE DATA FOR CURRENT STUDENT $currid = $sid; $currName = $name; $currAge = $age; $currGrade = $grd; $data = array(); } // STORE INFRACTION AND ACTION DATA $data['actions'][$actid] = $acttxt; $data['infracts'][$infid] = $inftxt; } // DON'T FORGET TO OUTPUT THE LAST STUDENT // THAT IS STORED IN THE DATA $tdata .= "<tr><td>$currName</td><td>$currAge</td><td>$currGrade</td><td>"; foreach ($data['infracts'] as $k=>$v) { $tdata .= "$k $v<br>"; } $tdata .= "</td><td>"; foreach ($data['actions'] as $k=>$v) { $tdata .= "$k $v<br>"; } $tdata .= "</td></tr>\n"; ?> <html> <body> <table border='1'> <?= $heads?> <?= $tdata?> </table> </body> </html> Giving Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/#findComment-1497879 Share on other sites More sharing options...
x-man1 Posted November 28, 2014 Author Share Posted November 28, 2014 mr.Barand thank you so much i will try the code Link to comment https://forums.phpfreaks.com/topic/292755-need-to-amendment-the-code-it-duplicate-records/#findComment-1497923 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.