Select column(s) names based on user entry with a MYSQL query

Using PHP a secure user will enter a Ref (ex. NB093019) a query will be used to determine which PO(s) have that Ref and if they have any quantity. The issue is that we have 86 columns to check if that Ref is in and then once it finds what column it is in how to check the corresponding column that contains that quantity( the table cannot be edited).

I can make this work with 86 if else statements in PHP and then more if else statements inside of each PHP statement. I have no launching point once i do the initial query.

select 'remainder'as prefix, po,  *comments,*GuideRef, *Qty
from remainder 
where  ('NB092419')IN (NWANTcomments,NWANTGuideRef,NWANTpreviouscomments,
                        NWANTpreviousGuideRef,NWANTprevious2comments,
                        NWANTprevious2GuideRef, BPrev2GuideRef, 
                        BPrev2comments, BPrevGuideRef, BPrevcomments, 
                        aGuideRef, Mcomments,MGuideRef,acomments,
                        MAGuideRef,BOGuideRef ) 
group by po

I have removed some of the in() information so it is not so long also the *comments, *GuideRef, *Qty would be decided by which one of the columns in the IN() statement returns information. Is this even possible

Solution

You could perhaps write an SQL that writes an SQL:

 select REPLACE(
   'SELECT ''{colstub}GuideRef'' as which, {colstub}Qty FROM remainder WHERE {colstub}Ref like ''%somevalue%'' UNION ALL',
   '{colstub}',
   REPLACE(column_name, 'GuideRef', '')
 )  
 FROM information_schema.columns 
 WHERE table_name = 'remainder' and column_name LIKE '%Ref'

It works like "pull all the column names out of the info schema where the column name is like %guideref, replace guideref with nothing to get just the fragment of the column name that is varied: NWANTguideref -> NWANT, NWANTpreviousguideref -> NWANTprevious ... then uses this stub to form a query that gives a string depicting the column name, the qty from the quantity column, where the relevant guideref column is LIKE some value"

If you run this it will produce a result set like:

SELECT 'aGuideRef' as which, aQty FROM table WHERE aGuideRef LIKE '%lookingfor%' UNION ALL 
SELECT 'bGuideRef' as which, bQty FROM table WHERE bGuideRef LIKE '%lookingfor% ...

So it's basically utputted a load of strings that are SQLs in themselves. It might need a bit of fine tuning, and hopefully all your columns are reliably and rigidly like xQty, xGuideRef, xComments triplets, but it essentially writes most the query for you

If you then copy the result set out of the results grid and paste it back into the query window, remove the last UNION ALL and run it, it will search the columns and tell you where it was found as well as the quantity

It's not too usable for a production system, but you could do the same in php- run the query, get the strings into another sql command, re-run it..

I would suggest you consider changing your table structure though:

prefix, qty, guideref, comments

You shouldn't have 86 columns that are the mostly same thing; you should have one column that is one of 86/3 different values then you can just query the guideref and the type. If this were an address table, I'm saying you **shouldn't* have HomeZipcode, WorkZipcode, UniversityZipcode, MomZipcode, DadZipcode.. and every time you want to store another kind of address you add more columns (BoyfriendZipcode, GirlfriendZipcode, Child1Zipcode...). Instead if you just had an "addresstype" column then you can store any number of different kinds of addresses without recompiling your app and changing your db schema

You can use this technique to re-shape the table - write an SQL that writes a bunch of UNION ALL sqls (without WHERE clauses), one of the columns should be the "recordtype" column (from colstub) and the other columns should just be "qty", "guide", "comments". Once you have your result set with the unions you can make a table to hold these 4 things, and then place INSERT INTO newtable at the head of the block of unions