phpmyadmin Question posted in
The official documentation can be found here.

Guidance with a SQL query – results duplicating – Phpmyadmin

Really need some guidance, I have a website where people can upload images to competitions and they get a result (marks), I want to pull out a LEADERSHIP BOARD to see how everyone is doing. However the SQL Query I have has people names duplicated, could someone tell me where to alter my query so the results for say 1 person entering 3 competitions are added up, and only the TOTAL marks are stated once in the output. The results are kept in tblMembEntComp fldResult.

What I’m currently getting:

Leadership Board

My PHP Code:

<p><b>LEADERSHIP BOARD</b></p>
<?php
    $query = "SELECT `tblMember`.`fldFName`, `tblMember`.`fldSName`, `tblMembEntComp`.`fldResult` FROM `tblMember` AS `tblMember` JOIN `tblMembEntComp` as `tblMembEntComp` ON `tblMember`.`fldMemberID` = `tblMembEntComp`.`fldMemberID`ORDER BY `fldResult` DESC";

    $result = $conn -> query($query);

    while($row = $result -> fetch_assoc()) 
        {
            echo $row['fldFName']." ".$row['fldSName']." ".$row['fldResult']."<br>";
        } 
?>

UPDATE:
I have tried the GROUP BY function as suggested by user below however I am getting thrown the error:

SQL Error

Tags:

2

Answers


  1. 0

    You should group by query by tblMember primary key as below:

    <?php
    $query = "SELECT `tblMember`.`fldFName`, `tblMember`.`fldSName`, `tblMembEntComp`.`fldResult`, `tblMember`.`fldId` FROM `tblMember` AS `tblMember` JOIN `tblMembEntComp` as `tblMembEntComp` ON `tblMember`.`fldMemberID` = `tblMembEntComp`.`fldMemberID` GROUP BY `tblMember`.`fldId` ORDER BY `fldResult` DESC";

    $result = $conn -> query($query);

    while($row = $result -> fetch_assoc()) 
        {
            echo $row['fldFName']." ".$row['fldSName']." ".$row['fldResult']."<br>";
        } 
?>

    You can update group by field as your primary key. Hope it helps you 🙂

    Login or Signup to reply.
  2. 0

    You seem to want an aggregation query. Something like:

    SELECT m.fldSName, SUM(mec.fldResult) as fldResult
FROM tblMember m JOIN 
     tblMembEntComp mec
     ON m.fldMemberID =mec.fldMemberID
GROUP BY m.fldSName
ORDER BY SUM(mec.fldResult) DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search