Get variable columns for "where value in"

Question

I'm trying to get all columns from a table. The problem is, that every table has got a variable amount and order of the columns.

Is it possible to get all columns from one specific table?

I need to check if a value exists in a table with variable amount of columns.

Possible duplicate of [MySQL Select all columns from one table and some from another table](http://stackoverflow.com/questions/3492904/mysql-select-all-columns-from-one-table-and-some-from-another-table) — Jester, Apr 07 '16 at 06:46
Sounds like pretty bad database design. maybe you should rethink that. — Gerald Schneider, Apr 07 '16 at 06:59
The problem is that i dont have this much time cause it is the final project of my apprenticeship :) — Tobi ._., Apr 07 '16 at 07:40

Jester · Answer 1 · 2016-04-07T08:01:24.293

0

yes, you can use tablename.* for this, here is a short example:

SELECT table1.*
FROM table1
JOIN table2 ON table2.id = table1.id

to check whether a variable exists in any of the columns you'd first select the columns using the query that scaisEdge posted as well:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database' 
AND TABLE_NAME = 'my_table';

Then you would loop through the results of this query to buil the IN(column1, column2 e.t.c) part of the query

And than you can put this line in a where to do your new select:

WHERE ? IN(column1, column2)

Use prepared statements so you are not vulnerable to sql injections

edited Apr 07 '16 at 08:01

answered Apr 07 '16 at 06:45

Jester

That wont help me unfortunately.. I need to check if a value exists in a table with variable amount of columns. – Tobi ._. Apr 07 '16 at 06:54
so you need to check whether a variable exists in any of the columns? are we talking substring? or full match? – Jester Apr 07 '16 at 07:00
Excatly ! Full match.. e.g. i got the value abc and i have to check if this value exists in any column – Tobi ._. Apr 07 '16 at 07:41
You can either use the MATCH function. which does require the fields to have a FULLTEXT key and you also have to enter all the columns (you can't match on id's though) or you use IN (column1,) this also requires you to know the columns though. Best solution if you really want this is to select the columns using the information_schema and then dynamically build your query from the results – Jester Apr 07 '16 at 07:55

score 0 · Answer 2 · answered Apr 07 '16 at 06:47

0

for the value related to the column you can use

select * from my_table;

otherwise for the column name you should use

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database' 
 AND TABLE_NAME = 'my_table';

answered Apr 07 '16 at 06:47

ScaisEdge

2 Answers2