need to Amendment the Code: it Duplicate records

[x-man1]

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: student
Columns:
ID_student int(11) AI PK 
name varchar(45) 
Age varchar(45) 
Grade varchar(45)

Table: infractions
Columns:
ID_infractions int(11) AI PK 
infractions_text varchar(45) 
ID_student int(11)


Table: actions
Columns:
ID_Actions int(11) AI PK 
Actions_text varchar(45) 
ID_student int(11)

view1
SELECT 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_text
FROM (student
JOIN infractions ON student.ID_student = infractions.ID_student)
JOIN actions ON student.ID_student = actions.ID_student

php 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

[DavidAM]

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.

[x-man1]

Relationship in this form suitable for me completely, but what I need is to solve the problem of repeat records .. 

[DavidAM]

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.

[Barand]

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

 

[x-man1]

mr.Barand thank you so much  i will try the code