Jump to content

How to restrict mySQL results to single instance after using mb_substr


speckytwat
Go to solution Solved by NotionCommotion,

Recommended Posts

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 by Psycho
Link to comment
Share on other sites

  • Solution

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;
Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.