How to restrict mySQL results to single instance after using mb_substr

[speckytwat]

Hi, anyone know how I can restrict the values displayed in a Select, taken from a MySQL query, which have been edited before output?

The original values are all unique so doing a COUNT or DISTINCT in MySQL won't work. This is the PHP I'm using to pull and edit the values:

        $getusers = $mysqli->query("SELECT order_discount_code FROM orders ORDER BY order_discount_code ASC");
            while ($row = $getusers->fetch_assoc()) {
                $vouchercode = $row['order_discount_code']; //These values are all unique but share common characters e.g the first three characters of the string
                $agencycode = mb_substr($vouchercode, 0, 3); //I'm doing this as I want to have just the first three characters show in the form Select that contains this query
        echo '<option>'.$agencycode.'</option>';
            }

Basically I just want to output unique instances of $agencycode, so if the first three characters might be ABC, DEF or GHI for all the various records, I want ABC to only show once in the Select, DEF to show once, GHI to show once etc.

At the moment my code shows a whole long list of every instance.

Anyone know how I can do this? Thanks! Edited January 6, 2017 by Psycho

[NotionCommotion]

Instead of having PHP do the trimming, have MySQL do it.  See http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_left.

 

Do, why won't DISTINCT or GROUP BY work?.  Are there records like ABCfoo and ABCbar? Maybe the following?

SELECT DISTINCT LEFT(order_discount_code,3) FROM orders ORDER BY order_discount_code ASC;

[mac_gyver]

where and how are the agency codes defined now? they must be defined somewhere, since you are able to produce the values you are storing in the orders table.

 

the proper way of doing this is to have the agency codes defined in an agency_code database table, then just retrieve the values to produce the output.

 

once you have 10's of thousands of records in the orders table, you would NOT attempt to extract the agency codes, after the fact, every time you need to produce the select/option menu.

Edited January 6, 2017 by mac_gyver

[speckytwat]

Thanks, I tried changing the query to exactly as suggested but unfortunately that just makes the SELECT have blank entries in the dropdown:

 

Correct, the order_discount_code can be ABC123, ABC456, ABC789 and so on, it's the ABC that I'm interested in outputting into the dropdown.

 

I thought the blank entries might be because I was using $agencycode in the option tags, but I tried $vouchercode and that did the same...

[speckytwat]

Ok, think I fixed it, by doing the following:

 

 

SELECT DISTINCT LEFT (order_discount_code,3) AS vouchercode....

 

Then pulling $row['vouchercode'] to the variable to put into the option tags.

 

So your DISTINCT LEFT did the trick.

 

Thanks