PHP MySQL Merge Resulting Query Rows into Single Column

Question

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.

It's difficult to query and use because the schema is poorly normalized: essentialsql.com/… — Sammitch
– Sammitch, Commented Sep 18, 2017 at 23:13
I have to agree with Sammitch: your code columns seem to contain equivalent values. These columns should probably be normalized. The answers given by Gordon Linoff clearly illustrates why. If you have difficultly with this, then ask about it, but please provide your real and complete schema. — KIKO Software
– KIKO Software, Commented Sep 18, 2017 at 23:58

Gordon Linoff · Accepted Answer · 2017-09-18 23:20:28Z

1

It is probably more efficient to do this in PHP. But, you can do:

select code1 as queriedcode, code2 as result from t where code1 = 23 union all
select code1, code3 from t where code1 = 23 union all
select code1, code4 from t where code1 = 23 union all
select code2, code1 from t where code2 = 23 union all
select code2, code3 from t where code2 = 23 union all
select code2, code4 from t where code2 = 23 union all
select code3, code1 from t where code3 = 23 union all
select code3, code2 from t where code3 = 23 union all
select code3, code4 from t where code3 = 23 union all
select code4, code1 from t where code4 = 23 union all
select code4, code2 from t where code4 = 23 union all
select code4, code3 from t where code4 = 23 ;

answered Sep 18, 2017 at 23:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL Merge Resulting Query Rows into Single Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related