If I have a database table with columns such as code1, code2, code3, and code4. Then I run the following query against this table.
SELECT *
FROM codetable
WHERE code1 = 23 OR code2 = 23 OR code3 = 23 OR code4 = 23
I am then returned all the rows where any one of those columns has a match. I need to take these results and write them into a new database table where there is two columns. First column will be the code being searched for or in this case "23", the second column will be any matching results found from my query that are not 23.
So if a row in my codetable looks like this...
code1|code2|code3|code4
23 |27 |30 |45
My new table will be formatted like this,
queriedcode|result
23 | 27
23 | 30
23 | 45
Is there a MySQL query that can be used to achieve this or would my best bet be to use PHP, I can't seem to find a reasonable method to accomplish what I want using either. Everything I've come up with so far seems to either not work or be so over complicated it could fail easily.