1

THE BACKGROUND

I am using a MySQL database to store data which I retrieve in data visualization on my website. Two out of ~800 items are of main interest in my visualization and will have a specific color code assigned to them (dark and light orange) inside a bar chart. Filters are used for the visualization, so each time a different set of items could be visible, but the 2 main items should always stick out (if existent after the filter).

DESIRED SOLUTION

I want to achieve both

  • A colorful bar chart
  • easy visual recognition of the two main items by avoiding similar colors in the chart

MY CURRENT SOLUTIONS

One way might be to just create a random set of hex colors, manually (or semi-automatically) change all colors of items that have a similar color code to those of the two main items and store it as a list to be retrieved. And for now, that would be my strategy...

But I would really like to have an automated way which would just create random hex codes, but avoid a specific color range (in my case everything orange).

I know already how to randomly create a hex code:

concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0))

Now the next step would be the avoidance of the orange color scheme.

MY QUESTION

Any ideas on how to achieve this inside a SELECT statement?

SELECT
   item,
   concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)) as color --THIS SHOULD CHANGE
   FROM item_list
JoeBe
  • 1,224
  • 3
  • 13
  • 28
  • Generate your colours as HSV instead, then convert HSV to RGB. Limit the hues that you generate, leaving out a range for the colours you want to pop. https://stackoverflow.com/questions/3018313/algorithm-to-convert-rgb-to-hsv-and-hsv-to-rgb-in-range-0-255-for-both – Welbog Apr 30 '19 at 14:43
  • 1
    HSV is the better way to go, as you can mask out a range of orange hue. If this gets piped to a webpage and javascript is an option there is a library called tinycolor.js which i use all the time. https://github.com/bgrins/TinyColor – Gauthier Apr 30 '19 at 14:49
  • How do you define orange in terms of numbers? – Salman A Apr 30 '19 at 14:50
  • 1
    tinycolor('orange').toHsv() which returns {h: 38.82, s:1 ,v:, a: 1} Not sure how much orange you want to mask, but anything with a hue between 32 - 48 ? Sorry that range is a little small you want to exclude 16 - 52, that ends on red and begins on yellow http://colorizer.org/ – Gauthier Apr 30 '19 at 14:53
  • @Gauthier Yes, that might be another option and it seems to be easily implementable. I just wanted it to be done on the backend. So for an excepted answer for my question I would need a `SQL` solution. Perhaps I can figure something out with the HSV color model. – JoeBe Apr 30 '19 at 15:09

1 Answers1

0

This is more a javascript solution, but its generates the same color for strings rather then random everytime.

// this requires tiny color // https://github.com/bgrins/TinyColor
let 
    MD5=function(a){function b(a,b){var c,d,e,f,g;return e=2147483648&a,f=2147483648&b,c=1073741824&a,d=1073741824&b,g=(1073741823&a)+(1073741823&b),c&d?2147483648^g^e^f:c|d?1073741824&g?3221225472^g^e^f:1073741824^g^e^f:g^e^f}function c(a,c,d,e,f,g,h){return a=b(a,b(b(c&d|~c&e,f),h)),b(a<<g|a>>>32-g,c)}function d(a,c,d,e,f,g,h){return a=b(a,b(b(c&e|d&~e,f),h)),b(a<<g|a>>>32-g,c)}function e(a,c,d,e,f,g,h){return a=b(a,b(b(c^d^e,f),h)),b(a<<g|a>>>32-g,c)}function f(a,c,d,e,f,g,h){return a=b(a,b(b(d^(c|~e),f),h)),b(a<<g|a>>>32-g,c)}function g(a){var d,b="",c="";for(d=0;3>=d;d++)c=a>>>8*d&255,c="0"+c.toString(16),b+=c.substr(c.length-2,2);return b}var i,j,k,l,m,n,o,p,h=[];for(a=function(a){a=a.replace(/\r\n/g,"\n");for(var b="",c=0;c<a.length;c++){var d=a.charCodeAt(c);128>d?b+=String.fromCharCode(d):(127<d&&2048>d?b+=String.fromCharCode(d>>6|192):(b+=String.fromCharCode(d>>12|224),b+=String.fromCharCode(d>>6&63|128)),b+=String.fromCharCode(63&d|128))}return b}(a),h=function(a){var b,c=a.length;b=c+8;for(var d=16*((b-b%64)/64+1),e=Array(d-1),f=0,g=0;g<c;)b=(g-g%4)/4,f=g%4*8,e[b]|=a.charCodeAt(g)<<f,g++;return b=(g-g%4)/4,e[b]|=128<<g%4*8,e[d-2]=c<<3,e[d-1]=c>>>29,e}(a),m=1732584193,n=4023233417,o=2562383102,p=271733878,a=0;a<h.length;a+=16)i=m,j=n,k=o,l=p,m=c(m,n,o,p,h[a+0],7,3614090360),p=c(p,m,n,o,h[a+1],12,3905402710),o=c(o,p,m,n,h[a+2],17,606105819),n=c(n,o,p,m,h[a+3],22,3250441966),m=c(m,n,o,p,h[a+4],7,4118548399),p=c(p,m,n,o,h[a+5],12,1200080426),o=c(o,p,m,n,h[a+6],17,2821735955),n=c(n,o,p,m,h[a+7],22,4249261313),m=c(m,n,o,p,h[a+8],7,1770035416),p=c(p,m,n,o,h[a+9],12,2336552879),o=c(o,p,m,n,h[a+10],17,4294925233),n=c(n,o,p,m,h[a+11],22,2304563134),m=c(m,n,o,p,h[a+12],7,1804603682),p=c(p,m,n,o,h[a+13],12,4254626195),o=c(o,p,m,n,h[a+14],17,2792965006),n=c(n,o,p,m,h[a+15],22,1236535329),m=d(m,n,o,p,h[a+1],5,4129170786),p=d(p,m,n,o,h[a+6],9,3225465664),o=d(o,p,m,n,h[a+11],14,643717713),n=d(n,o,p,m,h[a+0],20,3921069994),m=d(m,n,o,p,h[a+5],5,3593408605),p=d(p,m,n,o,h[a+10],9,38016083),o=d(o,p,m,n,h[a+15],14,3634488961),n=d(n,o,p,m,h[a+4],20,3889429448),m=d(m,n,o,p,h[a+9],5,568446438),p=d(p,m,n,o,h[a+14],9,3275163606),o=d(o,p,m,n,h[a+3],14,4107603335),n=d(n,o,p,m,h[a+8],20,1163531501),m=d(m,n,o,p,h[a+13],5,2850285829),p=d(p,m,n,o,h[a+2],9,4243563512),o=d(o,p,m,n,h[a+7],14,1735328473),n=d(n,o,p,m,h[a+12],20,2368359562),m=e(m,n,o,p,h[a+5],4,4294588738),p=e(p,m,n,o,h[a+8],11,2272392833),o=e(o,p,m,n,h[a+11],16,1839030562),n=e(n,o,p,m,h[a+14],23,4259657740),m=e(m,n,o,p,h[a+1],4,2763975236),p=e(p,m,n,o,h[a+4],11,1272893353),o=e(o,p,m,n,h[a+7],16,4139469664),n=e(n,o,p,m,h[a+10],23,3200236656),m=e(m,n,o,p,h[a+13],4,681279174),p=e(p,m,n,o,h[a+0],11,3936430074),o=e(o,p,m,n,h[a+3],16,3572445317),n=e(n,o,p,m,h[a+6],23,76029189),m=e(m,n,o,p,h[a+9],4,3654602809),p=e(p,m,n,o,h[a+12],11,3873151461),o=e(o,p,m,n,h[a+15],16,530742520),n=e(n,o,p,m,h[a+2],23,3299628645),m=f(m,n,o,p,h[a+0],6,4096336452),p=f(p,m,n,o,h[a+7],10,1126891415),o=f(o,p,m,n,h[a+14],15,2878612391),n=f(n,o,p,m,h[a+5],21,4237533241),m=f(m,n,o,p,h[a+12],6,1700485571),p=f(p,m,n,o,h[a+3],10,2399980690),o=f(o,p,m,n,h[a+10],15,4293915773),n=f(n,o,p,m,h[a+1],21,2240044497),m=f(m,n,o,p,h[a+8],6,1873313359),p=f(p,m,n,o,h[a+15],10,4264355552),o=f(o,p,m,n,h[a+6],15,2734768916),n=f(n,o,p,m,h[a+13],21,1309151649),m=f(m,n,o,p,h[a+4],6,4149444226),p=f(p,m,n,o,h[a+11],10,3174756917),o=f(o,p,m,n,h[a+2],15,718787259),n=f(n,o,p,m,h[a+9],21,3951481745),m=b(m,i),n=b(n,j),o=b(o,k),p=b(p,l);return(g(m)+g(n)+g(o)+g(p)).toLowerCase()},
    c = tinycolor(MD5('your title or something').substr(0,6)),
    hsv = c.toHsv();

if (hsv.h >= 16 && hsv.h <= 52) c.spin(100);
let desired_hex = c.toString();
Gauthier
  • 1,251
  • 10
  • 25