Search code examples
mysqldatatablesduplicatesrowunion

MySQL UNION show duplicates in DataTables

This is driving me crazy. I have two tables which contain invoice data. I have a customer who has one unpaid invoice in each table. I'm using this code to join the data from the two tables and get the two invoice rows:

$stmt = $db->prepare ("

    SELECT invoice_id, invoice_number, customer_id, paid 
    FROM 
    ( 
      SELECT invoice_id, invoice_number, customer_id, paid FROM tbl_invoices
      UNION ALL 
      SELECT invoice_id, invoice_number, customer_id, paid FROM tbl_invoices_el
    ) 
    tbl_invoices
    WHERE customer_id = $customer_id 
    AND paid = '0'
    GROUP BY invoice_number
");

The code works fine in phpMyAdmin, the result is two rows with the data. On the page I'm using Bootstrap + DataTables to show the result in a modal window. The strange: no matter how I try, the first invoice is repeated two times. As I said before the code works fine in phpMyAdmin, the var_dump() of the fetched result is showing two arrays, as expected. What I'm doing wrong here?

UPDATE The $output .=""wasn't empty. My fault. @Barmar discovered the issue.

$stmt->execute();
$all_result = $stmt->fetchAll();
$total_rows = $stmt->rowCount();
if ( $total_rows > 0 )  
{
   foreach ($all_result as $row )  
     { 
     /* $output .=" ........... removed */
      echo " 
      <tr style='font-size:13px;'>
        <td width='5%'>
            <div class='form-check' style='margin-bottom:0px'>
                <label class='form-check-label'>
                  <input data-attr='" .$row['invoice_number']. "' id='" .$row['invoice_id']. "' class='form-check-input' type='checkbox' value='" .$customer_id. "'> 
                  <span class='form-check-sign'>
                    <span class='check'></span>
                  </span>
                </label>
            </div>
        </td>
        <td width='15%'>".$row['invoice_number']."</td>
      </tr>   
    ";
    /* echo $output; ........... removed */
  }
}

The result of var_dump():
The result of var_dump()

Solution

  • There's no way you can get duplicate invoice numbers when you have GROUP BY invoice_number, so the code you showed can't produce that duplicate.

    I suspect the problem is that you didn't clear $output, and there's some previous output with the first invoice number. Put:

    $output = "";

    before the foreach loop.