Multidimensional Associative Array - REMOVE DUPLICATES

Question

I've got a MySQL query that I run through a while loop that supplies me with a multidimensional associative array.

$query2 = "SELECT DISTINCT 
            soc.rowid as id,
            fc.paye as paid
            FROM invoices fc
            LEFT JOIN customers soc on fc.fk_soc = soc.rowid
            AND fc.paidBy < '" . $date . "'";

$unpaidInv = $db->query($query2);

while($row1 = mysqli_fetch_array($unpaidInv, MYSQLI_ASSOC)){
    $params1[] = $row1;
}
var_dump($params1);

var_dump result as follows:

array(10) {                                                                                                                                                                                                        
  [0]=>                                                                                                                                                                                                            
  array(2) {                                                                                                                                                                                                       
    ["id"]=>                                                                                                                                                                                                       
    string(2) "19"                                                                                                                                                                                                 
    ["paid"]=>                                                                                                                                                                                                     
    string(1) "1"                                                                                                                                                                                                  
  }                                                                                                                                                                                                                
  [1]=>                                                                                                                                                                                                            
  array(2) {                                                                                                                                                                                                       
    ["id"]=>                                                                                                                                                                                                       
    string(2) "22"                                                                                                                                                                                                 
    ["paid"]=>                                                                                                                                                                                                     
    string(1) "1"                                                                                                                                                                                                  
  }                                                                                                                                                                                                                
  [2]=>                                                                                                                                                                                                            
  array(2) {                                                                                                                                                                                                       
    ["id"]=>                                                                                                                                                                                                       
    string(2) "21"                                                                                                                                                                                                 
    ["paid"]=>                                                                                                                                                                                                     
    string(1) "0"                                                                                                                                                                                                  
  }                                                                                                                                                                                                                
  [3]=>                                                                                                                                                                                                            
  array(2) {                                                                                                                                                                                                       
    ["id"]=>                                                                                                                                                                                                       
    string(2) "23"                                                                                                                                                                                                 
    ["paid"]=>                                                                                                                                                                                                     
    string(1) "1"                                                                                                                                                                                                  
  }                                                                                                                                                                                                                
  [4]=>                                                                                                                                                                                                            
  array(2) {                                                                                                                                                                                                       
    ["id"]=>                                                                                                                                                                                                       
    string(2) "21"                                                                                                                                                                                                 
    ["paid"]=>
    string(1) "1"
  }
  [5]=>
  array(2) {
    ["id"]=>
    string(2) "32"
    ["paid"]=>
    string(1) "1"
  }
  [6]=>
  array(2) {
    ["id"]=>
    string(2) "22"
    ["paid"]=>
    string(1) "0"
  }
  [7]=>
  array(2) {
    ["id"]=>
    string(2) "23"
    ["paid"]=>
    string(1) "0"
  }
  [8]=>
  array(2) {
    ["id"]=>
    string(2) "32"
    ["paid"]=>
    string(1) "0"
  }
  [9]=>
  array(2) {
    ["id"]=>
    NULL
    ["paid"]=>
    string(1) "0"
  }
}
string(120) "

Currently there are array items for the same id with different "paid" values. As example, Array item 2, id->21, paid->0 – Array item 4, id->21, paid->1.

I need my array to return only results that are all 1. So if array has id->21 twice but the paid item has paid->1 and paid->0 it shouldn't appear in the array.

Only array items that are all 1.

No, Because it must run through all occurrences in the data set to exclude id's that have both paid 1 and paid 0 from the result array. So for id's that have paid 1 and 0 it should be false, whereas id's that have multiple paid as 1 or just single paid 1 need to be included. — Alex Coetzee, Jan 27 '21 at 14:59
Is it ok to restructure the array in order to achieve the result? — El_Vanja, Jan 27 '21 at 15:17
And please consider switching to prepared statements to [prevent SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — El_Vanja, Jan 27 '21 at 15:29

stéphane M. · Answer 1 · 2021-01-27T15:44:06.147

I would try something like this in the request:

SELECT DISTINCT 
        soc.rowid as id,
        fc.paye as paid
        FROM invoices fc
        LEFT JOIN customers soc on fc.fk_soc = soc.rowid
        WHERE fc.paidBy < '" . $date . "'"
        AND soc.rowid NOT IN (SELECT DISTINCT 
        soc2.rowid as id,
        FROM invoices fc2
        LEFT JOIN customers soc2 on fc2.fk_soc = soc2.rowid
        WHERE fc2.paidBy < '" . $date . "'"
        AND fc2.paye = '0');

To be adapted more precisely, but with this in mind, you would keep only the lines which are not in the subquery which retrieves the lines which have a value of zero

score 0 · Answer 2 · answered Jan 27 '21 at 17:01

For anyone interested in a usable solution, Please see below.

SELECT DISTINCT
soc.rowid as id
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
WHERE soc.rowid IS NOT NULL
AND fc.paidBy < '" . $date . "'"
AND soc.rowid NOT IN(
SELECT DISTINCT
soc.rowid as id
FROM invoices fc
LEFT JOIN customers soc on fc.fk_soc = soc.rowid
AND fc.paye = '0'
WHERE soc.rowid IS NOT NULL
AND fc.paidBy < '" . $date . "'");

Multidimensional Associative Array - REMOVE DUPLICATES

2 Answers2