I have some paths in my SQL database:
+-----------------------------------------+
| path |
+-----------------------------------------+
| monkey/rabbit/horse/bird/cat |
| monkey/rabbit/horse/sheep |
| monkey rabbit/frog/mouse |
| monkey rabbit/frog/mouse/horse/elephant |
| monkey rabbit/frog/mouse/horse/raccoon |
+-----------------------------------------+
$srch = "horse";
$sql = "SELECT * FROM farm WHERE id = ? AND path LIKE '%{$srch}%'";
$q = $pdo->prepare($sql);
$q->execute([$id]);
$srch_arr = $q->fetchAll(PDO::FETCH_ASSOC);
echo "Search result: ".count($srch_arr);
foreach ($srch_arr as $key => $val) {
$subject = $val['path'];
$pos = strpos($subject, $srch);
if ($pos !== false) {
$result = substr($subject, 0, strpos($subject, '/', $pos));
}
echo $result;
}
Search result: 4
monkey/rabbit/horse
monkey/rabbit/horse
monkey/rabbit/frog/mouse/horse
monkey/rabbit/frog/mouse/horse
Search result: 2
monkey/rabbit/horse
monkey/rabbit/frog/mouse/horse
Thanks for updating the question. Try updating your SQL to this:
SELECT DISTINCT left(path,instr(details,"{$srch}")+CHAR_LENGTH("{$srch}")) AS 'path' FROM `farm` WHERE id = ? AND `path` LIKE '%{$srch}%'
This will process the results and cut the path on the SQL side which means less processing is required on the PHP side. This means you can remove your substr function.
Below is a test results screenshot that shows you it working in MySQL.
Please let me know if any more info is required. : )