Sorting SQL results based off from information which is not in a table?

[mrln68]

Well, I can get the data that I want out of the database thanks to you guys on this thread: http://www.phpfreaks.com/forums/index.php/topic,152423.msg658320.html#msg658320.

 

1)  Now I have come across a new problem.  I only want to display the 6 best matches (i.e. - closest to the source color).  When I return the information from the database though, it is (I assume) in order from first to last.  The distance from point A to point B is not stored after it is used to evaluate the entries (or is it?  I looked around in the mySQL documentation, but although it is great for figuring out how to use specific commands...it isn't so good for finding something you don't know exists ??? ).  I could run the same evaluation again once inside PHP and then insert those values into the array...sort...and then display, but I am hoping there is a better way that I just am not aware of.  Not sure if a better way exists...or if I'll need to handle it within the PHP as opposed to sorting in the db query itself.

 

2)  A second (maybe easier question...dunno) is in regards to the actual output.  You can see a somewhat working example here: http://colors.silicon-dragons.com/

When a visitor selects a dark color, the text for that color in the center becomes hard to read (you can see for yourself by entering something like "111111" for the RGB).  Is anyone familiar with a way to determine a colors inverse and set that to the text color?  I haven't tripped across it while looking for related information, but the web is a big place.

 

BTW - on the test site, the only input forms which do anything are the Color Range and Hex RGB.  The second group currently only does the query...I haven't gotten around to limiting the searches with it yet.

[mrln68]

SELECT color_name, color_number, color_hex, line_name, SQRT(POW((43.192289563-color_L),2)+POW((0.00268431928258-color_a),2)+POW((-0.00531107131247-color_b),2)) AS distance 
FROM color_data 
LEFT JOIN color_lines 
ON color_line_id = line_id 
HAVING distance <= 4 
ORDER BY distance

 

Partially solved.  I was looking back over the original thread, and saw something I wasn't familiar with...using the alias gives me the distance so that I can sort by that and then return the sorted results back to PHP for display.

 

Anyone might know a trick on the text color?

 

BTW - should I be able to find an edit button here?