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

Question

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

@RiggsFolly how on earth is this question a duplicate of that question? — Caius Jard, Oct 18 '19 at 17:35
@Bookcellar I'm not big on php, but if I was doing this I would perhaps run `SELECT * FROM table WHERE null = null` to get an empty resultset that tells me the column names, then build an sql string in the app that was essentially a `SELECT 'aRef' as whichColumn, aQty FROM table WHERE aRef LIKE '%lookingfor%' UNION ALL SELECT 'bRef' as whichColumn, bQty FROM table WHERE bRef LIKE '%lookingfor% ...`' — Caius Jard, Oct 18 '19 at 17:40
Seems like a design failure if you can store one piece on data into one of 86 possible columns. I cannot imagine how this could possibly happen. — RiggsFolly, Oct 18 '19 at 17:43
it cant really be stored in 86 columns i just have no way of knowing which column to look in because each column represents a different location--so if they enter something like NB... then it would search the 3 columns starting with NWant if they enter in B... it would search the 4 columns starting with B, hence the ability to use if else statements in php — Bookcellar, Oct 18 '19 at 17:54
The structure of the table is totally inappropriate for your requirements; please seriously consider restructuring this table so it has only 4 columns; location, qty, guideref and comments - with things the way they are youre essentially storing table data in a column header which is a serious design flaw and will trip you up every time you try and use the table — Caius Jard, Oct 18 '19 at 18:03

Caius Jard · Accepted Answer · 2019-10-18T18:02:38.113

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

Freaking Brilliant--this is perfect---have to make some slight adjustments but saves me so much time and if else statments — Bookcellar, Oct 18 '19 at 18:36
unfortunately the columns have to stay that way as the user inputs data from grid and sometimes each column is full of different information for the same SKU.. this table is used 50 times through-out our system--i do understand what everyone is saying about the structure but it must stay that way — Bookcellar, Oct 18 '19 at 18:40

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

1 Answers1