I have a series of short codes, that I need to match to existing values in a database, when I run the following query, it returns values that do not match, or even have the same characters in:

The short_code_to_compare value in my testing is 'ACA'

An example value from the privileges table might be: ACA_TOT_WORKING, I am always interested, in the first characters, some may have 2, some may have 3. Those with 2 will always have an underscore after them

$privilege_match = $DB_con2->prepare("SELECT DISTINCT LEFT(privilege_code, 3) as priv_code FROM privileges WHERE privilege_code LIKE '%$short_code_to_compare%'");

The results I get back are just the first 3 characters of the priv_code string and the values don't even match ACA, what am I missing as all the tutorials point to this working.

Thanks for reading!

    Basically, your problem is that your LIKE expression starts with % so any matches of "ACA" aren't necessarily at the start of the string. Read up to see what % means in this context.

    Of course, then all you'll get is the first three letters and you already know what those are. That is of course because that's explicitly what you're asking for.

    In short, your LEFT stuff should be in the WHERE clause, not in the SELECT clause.

      Write a Reply...