How to count the total of each duplicate values from MySQL in PHP?

Question

How do I count duplicate "itemid" entries from MySQL. The code below exports results in MySQL, but I want to count the total of each duplicate "itemid".

Example: output(122,133,122,122,133,188). 122=3, 133=2, 188=1.

if(isset($_POST['daily']) && isset($_POST['reportdate'])){
     global $conn;
     $date = $_POST['reportdate'];
        $sql = $conn->prepare("SELECT * FROM issues WHERE date='$date'");
        $sql->execute();

        $output .='
        <table class="table" bordered="1">
            <tr>
                <th class="green">SAPCODE</th>
                <th class="green">DATE</th>
                <th class="green">DESCRIPTION</th>
                <th class="green">QUANTITY</th>
                <th class="green">UNIT</th>
                <th class="green">ISSUED TO</th>

            </tr>
        ';

        while($row = $sql->fetch(PDO::FETCH_ASSOC)){
                $perstat->getID($row['empid']);
                 $stock->getItemByID($row['itemid']);
                  $time = strtotime($row['date']);
                     $row['date']= date("d-M-y", $time);

                 $output .='

                     <tr>
                        <td>'.$row['itemid'].'</td>
                        <td>'.$row["date"].'</td>
                        <td>'.$stock->description.'</td>
                        <td>'.$row["qty"].'</td>
                        <td>'.$stock->unit.'</td>
                        <td>'.$perstat->pats.'</td>               
                     </tr>

                 ';
        }
        $output .='</table>';
        header("Content-Type: application/xls");
        header("Content-Disposition:attachment; filename=PPE Issuance report .xls");
        header("Pragma: no-cache"); 
        header("Expires: 0");
        echo $output;
    }else{
        header("Location:issuelist.php");
    }

Possible duplicate of [Finding duplicate values in MySQL](http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — davejal, Feb 23 '17 at 15:12
Definately a duplicate of http://stackoverflow.com/q/688549/3664960, so many answers there with a lot of upvotes — davejal, Feb 23 '17 at 15:13
I have tried the link but I couldn't fix it, for that reason I have asked. — Jacky, Feb 23 '17 at 16:27

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

I may infer that there is a column "itemid" in your issues table but I don't know that there is enough information in what you posted to be of help.

Here is how you would find duplicates Finding duplicate values in MySQL

edited May 23 '17 at 11:46

Community

1
1

answered Feb 23 '17 at 14:52

dacke.geo

233
2
13

score 0 · Answer 2 · answered Feb 23 '17 at 14:52

0

You should try this:

SELECT CONCAT(itemid,count(itemid)) FROM issues WHERE date='$date'" GROUP BY itemid

answered Feb 23 '17 at 14:52

Sudhanshu Jain

494
3
11

score 0 · Answer 3 · answered Feb 23 '17 at 14:53

0

SELECT COUNT(itemid) FROM issues WHERE date='$date' GROUP BY itemid

answered Feb 23 '17 at 14:53

B. Dionys

916
7
34

score 0 · Answer 4 · answered Feb 23 '17 at 15:01

You could add each itemID to an array as an index and keep count of them as you go through your results from your query.

For example (code commented for more explanation):

$item_id_count = array(); // Declare an empty array to keep count of itemIDs

while($row = $sql->fetch(PDO::FETCH_ASSOC)){
  $perstat->getID($row['empid']);
  $stock->getItemByID($row['itemid']);
  $time = strtotime($row['date']);
  $row['date']= date("d-M-y", $time);

  $output .='<tr>
                <td>'.$row['itemid'].'</td>
                <td>'.$row["date"].'</td>
                <td>'.$stock->description.'</td>
                <td>'.$row["qty"].'</td>
                <td>'.$stock->unit.'</td>
                <td>'.$perstat->pats.'</td>               
             </tr>';

  // Add itemID index into array with value of 0 if it does not exist
  if(!isset($item_id_count[$row['itemid']])){
    $item_id_count[$row['itemid']] = 0;
  }
  // Increment the value of the itemID index in array when it does exist
  $item_id_count[$row['itemid']]++;
}

// Print out each itemID with its count from the array.
foreach($item_id_count as $itemID => $itemIDCount){
  echo $itemID ." - " . $itemIDCount
}

score 0 · Answer 5 · answered Feb 23 '17 at 18:37

0

Finally I found it

$sql = $conn->prepare("SELECT * ,itemid,count(*) AS total_records  FROM issues WHERE date='$date' GROUP BY itemid");

answered Feb 23 '17 at 18:37

Jacky

41
6

How to count the total of each duplicate values from MySQL in PHP?

5 Answers5