How can I search only for parts of values inside the mysql database?

Question

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  |

+-----------------------------------------+

I want to search if a specific value exists.

$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);

As a result, for me the only interesting part is the path until the searched value:

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;

}

My result is now:

Search result: 4



monkey/rabbit/horse

monkey/rabbit/horse

monkey/rabbit/frog/mouse/horse

monkey/rabbit/frog/mouse/horse

But actually the result I need in this case is:

Search result: 2



monkey/rabbit/horse

monkey/rabbit/frog/mouse/horse

Answer 1

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. : )

How can I search only for parts of values inside the mysql database?

Sign up