Sort Multi-Dimensional Array - Deleteing Oldest Records From Array of Duplicates

Question

<?php

$q = "SELECT `Code` FROM `productspecs` GROUP BY `Code` HAVING COUNT(1) > 1";

$r = mysqli_query($conn, $q);
$a = 0;

while ($row = mysqli_fetch_array($r))

{

echo $row['Code'];
$q2 = "SELECT * FROM `productspecs` WHERE `Code`='" . $row['Code'] . "'";
$r2 = mysqli_query($conn, $q2);
$i = 0;
while ($row2 = mysqli_fetch_array($r2))
    {
    $Mod = $row2['ModificationDate'];       
    $ModDates[] = DateTime::createFromFormat('d/m/Y', $Mod); // === $ModDates[$i]
    $ModDates[$i]['SpecID']=$row2['SpecID'];
    $i++;
    }

usort($ModDates); // <<< ??
$DeleteIDs[] = $ModDates[0]['SpecID'];  

foreach ($ModDates as $ModDate)
    {

    echo $ModDate->format('d-m-Y');}
unset($ModDates); // unset the array for the next code      

echo '</br>';
echo 'Delete: ' . $DeleteIDs[$a];
$a++;

}   

?>

Hi I'm trying to Delete specifications from the product database (or at least get a list of ones to delete) where we have duplicates and we want to delete the oldest one.

So here I've 1) found a list of duplicate codes 2) Assigned spec id to each mod date 3) Sorted the mod dates to find the oldest*NOTE 4) Found a list of ids to delete by finding the id assigned to the first of the sorted array. 5) unsets the moddate array for the next set of duplicates

NOTE: I think solving this problem might have something to do with sorting the multidimensional array - this is the part that I'm fairly sure is wrong. If this wasn't a multidimensional array we would at least see the modification dates in order. At this point I COULD just do a search for product code and oldest (first in array) mod date which thinking about it now - would achieve the same result? Does it matter that the primary key will be lost - after all - all I need is to get rid of the product code with the older modification date.. it doesn't matter that we don't have the primary key?

P.S. I did see a post about sorting multi-dimensional arrays Here but I felt I should show you what I was doing in case somebody had a better suggestion?

Stop right there, Start using PDO http://php.net/manual/en/book.pdo.php Read PHP The right way : http://www.phptherightway.com/ — PayamB, Aug 04 '17 at 14:40
First, what's your question ? Second, your setting of `$ModDates` makes no sense, almost nothing involving that variable does, in fact. You should edit your question as soon as possible to provide information. About what you want to achieve and what is the problem. — Sarkouille, Aug 04 '17 at 14:41
@u_mulder beacuse using mysqli_* functions are outdated and dangerous. He better learn the right way of doing things. Have a look at PHP the right way. — PayamB, Aug 04 '17 at 14:48
Hi @ksjohn - thanks for your interest in helping me solve my problem. To answer your question: I am aiming to delete the older of duplicate codes from a product database according to the modification date — , Aug 04 '17 at 14:48
@PayamB I thought that mysqli functions were the way to do things and were supposed to be more secure? Or at least that's what I've been led to understand.. — , Aug 04 '17 at 14:50
`mysqli` functions are still secure. Maybe they are more complicated than `PDO` but you can use them. — u_mulder, Aug 04 '17 at 14:51
@Sploxon I'll point the mistakes I see in your code. Maybe I don't need to understand what data you handle to help with that. — Sarkouille, Aug 04 '17 at 14:56
@Sploxon Anyway, it would be nice from you to provide more information. For example, you didn't even wrote what errors you get. Just by looking at you code, there should be plenty. — Sarkouille, Aug 04 '17 at 14:59
@ksjohn For me it's more about finding the main problem and then wading through the errors once I know the final logical whatever should work.. if that makes sense.. So for me in this problem - I obtained an array with codes and date (and from that I could indeed solve the problem) but I thought it would be better to find the ids of these pairs (using multi-dimensional array): by this I linked the re-ordered dates to a re-ordering of the first while loop's rows i.e. the ids — , Aug 04 '17 at 15:12
@Sploxon That's not how you build programs. If you don't solve technical errors, your program won't work. If it doesn't work, you can't test it, which mean you can't check if it does what is intended. And by looking at your code, it seems you have significant progresses to make before trying to achieve your current goal. — Sarkouille, Aug 04 '17 at 15:15
@ksjohn I am able to solve the problem by searching for the code with the oldest modification date.. but I was always taught that you should avoid doing stuff like that.. and try to always use the primary/unique keys — , Aug 04 '17 at 15:29
@ksjohn Anyway - thank you for helping me - I know kind of what I'm trying to do - but it will take me a while to think it all through - the problem for me is probably 65% logical 35% PHP — , Aug 04 '17 at 16:00

score 0 · Answer 1 · answered Aug 04 '17 at 15:10

<?php

$q = "SELECT `Code` FROM `productspecs` GROUP BY `Code` HAVING COUNT(1) > 1";

$r = mysqli_query($conn, $q);
$a = 0;

That $a variable seems useless.

while ($row = mysqli_fetch_array($r)) {
    echo $row['Code'];
    $q2 = "SELECT * FROM `productspecs` WHERE `Code`='" . $row['Code'] . "'";
    $r2 = mysqli_query($conn, $q2);
    $i = 0;
    while ($row2 = mysqli_fetch_array($r2)) {
        $Mod = $row2['ModificationDate'];
        $ModDates[] = DateTime::createFromFormat('d/m/Y', $Mod); // === $ModDates[$i]
        $ModDates[$i]['SpecID'] = $row2['SpecID'];
        $i++;

If $ModDates[] === $ModDates[$i], as you say, then write $ModDates[$i] in both assignments. $ModDates[] === $ModDates[$i]can be wrong, $ModDates[$i] === $ModDates[$i] cannot. Moreover :

$ModDates[] = ...
$ModDates[$i]['SpecID'] = $row2['SpecID'];

Now, whatever has been assigned to $ModDates[$i] in the first line is gone, it's now an array with only one key : 'SpecId'.

    }

    usort($ModDates); // <<< ??

What does that comment mean ?

    $DeleteIDs[] = $ModDates[0]['SpecID'];

You put only one value from the array into $DeleteIDs. Why ?

    foreach ($ModDates as $ModDate) {
        echo $ModDate->format('d-m-Y');}
        unset($ModDates); // unset the array for the next code

That foreach will iterate only once because the line right above unsets the array it's iterating over. In case it's a mistake and you in fact wanted to unset $ModDate : - That will teach you to use variables with names you can differenciate - That wouldn't want anyway because $ModDate is a copy of an entry of $ModDates, not that entry itself. Unseting it changes nothing in $ModDates.

        echo '</br>';
        echo 'Delete: ' . $DeleteIDs[$a];
        $a++;
    }
}

I added the two last curly brackets, I don't know if you pasted wrongly or if you simply forgot to put them in your code.

Yes - all good comments. Arrays really confuse me, I think I see what you're saying in that the value just isn't there any more and there is nothing to sort after the second while loop has finished — , Aug 04 '17 at 15:19
If it's not enough to make you to understand how array work, check tutorials or PHP documentation. PHP arrays are a very simple data structure, but you have to put serious effort into it if you are abeginner, or else you'll be confused. — Sarkouille, Aug 04 '17 at 15:21
-Yes the $a was sort of useless it just allowed me to print the DeleteIDs out one by one, -The one value that goes into DeleteIDs is the oldest of the duplicates.. there are only a few duplicates so it doesn't matter if you have to run the delete duplicates script a couple times -I thought I had also missed a curly brace off but I didn't it was hiding there above the unset function..Yes - currently it looks strange, but before, when I was just printing out the ModDates, in order, I needed to unset the variable — , Aug 04 '17 at 15:35

Sort Multi-Dimensional Array - Deleteing Oldest Records From Array of Duplicates

1 Answers1