Jarla Jarla - 1 year ago 64
SQL Question

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

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


But actually the result I need in this case is:

Search result: 2


Answer Source

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

Test Results Screenshot

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download