How can I filter mysql selection starting with same characters?

Question

$dir = Volumes/animals/big

I am selecting values from a mySQL table starting with a certain path:

$sql = "SELECT path FROM files WHERE id = ? AND path LIKE '$dir%'";  
$q = $pdo->prepare($sql);
$q->execute([$id]);
$array = $q->fetchAll(PDO::FETCH_ASSOC);



array(4) {
  [0]=>
  array(1) {
    ["path"]=>
    string(149) "Volumes/animals/big/horses/shetland/amy.jpg" 
  }
  [1]=>
  array(1) {
    ["path"]=>
    string(149) "Volumes/animals/big/horses/shetland/sara.jpg" 
  }
 [2]=>
  array(1) {
    ["path"]=>
    string(149) "Volumes/animals/big/horses/shire/lord.jpg" 
  }
 [3]=>
  array(1) {
    ["path"]=>
    string(149) "Volumes/animals/big/elephant/borneo/mila.jpg" 
  }
[4]=>
  array(1) {
    ["path"]=>
    string(149) "Volumes/animals/big/map.jpg" 
  }

What I would need is only ONE entry of each subpath. This means, here I have three entries that start with "Volumes/animals/big/horses". I would only need one. The result I would like to achieve:

 array(2) {
      [0]=>
      array(1) {
        ["path"]=>
        string(149) "Volumes/animals/big/horses/shetland/amy.jpg" 
      }
     [1]=>
      array(1) {
        ["path"]=>
        string(149) "Volumes/animals/big/elephant/borneo/mila.jpg" 
      }
    [2]=>
      array(1) {
        ["path"]=>
        string(149) "Volumes/animals/big/map.jpg" 
      }

I do not know how I could achieve this. I am thankful for any idea.

IMPORTANT: I would need this filter in the MYSQL select directly. Not in php

Answer 1

This can be done in MySQL since you are passing the top level of the path into the query. Change your query as follows:

$sql = "SELECT path, 
          SUBSTRING_INDEX(SUBSTRING_INDEX(path, '$dir', -1), '/', 2) AS subpath
        FROM files 
        WHERE id = ? AND path LIKE '$dir%'
        GROUP BY subpath";

Output when $dir = 'Volumes/animals/big';:

path                                            subpath     
Volumes/animals/big/elephant/borneo/mila.jpg    /elephant
Volumes/animals/big/horses/shetland/amy.jpg     /horses
Volumes/animals/big/map.jpg                     /map.jpg