-3

In SQL is it possible to get only the rows of a table where the column name is like image? I've tried:

SELECT * FROM `projects` WHERE COLUMN LIKE '%image%' AND `id` = '125384332'
Alex
  • 9,215
  • 8
  • 39
  • 82
  • Yes, It's possible and your query looks fine. What result are you getting? – Jorge Barrios Jul 16 '17 at 00:01
  • 7 errors were found during analysis. Unrecognized keyword. (near "COLUMN" at position 31) Unrecognized keyword. (near "LIKE" at position 38) Unexpected token. (near "'%image%'" at position 43) Unrecognized keyword. (near "AND" at position 53) Unexpected token. (near "`id`" at position 57) Unexpected token. (near "=" at position 62) Unexpected token. (near "'125384332'" at position 64) – Alex Jul 16 '17 at 00:02
  • even if I remove that part SELECT * FROM `projects` WHERE COLUMN LIKE '%image%' I still get Unrecognized keyword. (near "COLUMN" at position 31) Unrecognized keyword. (near "LIKE" at position 38) Unexpected token. (near "'%image%'" at position 43) – Alex Jul 16 '17 at 00:08
  • https://dev.mysql.com/doc/refman/5.7/en/columns-table.html – Funk Forty Niner Jul 16 '17 at 00:12
  • @Fred-ii- care to elaborate? posting a link with no description isn't that helpful. – Alex Jul 16 '17 at 00:13
  • see also https://stackoverflow.com/q/2224860/1415724 – Funk Forty Niner Jul 16 '17 at 00:13
  • you also tagged as php but there's no php to support the question. Read the manual link, I didn't type out anything else in there because there was no need to. – Funk Forty Niner Jul 16 '17 at 00:14
  • right, but this does nothing towards answering my question. i can easily get the column names where the name is like image. i was curious if i could get the rows or rather row, just containing the columns that are like image. imagine i have 10 columns in a row (5 of which are columns that contain the name image in it), i just want to get the values of those 5 for a given id without selecting them literally. – Alex Jul 16 '17 at 00:16
  • 1
    This kind of problem is symptomatic of poor design – Strawberry Jul 16 '17 at 00:57
  • not being able to run this query? – Alex Jul 16 '17 at 01:14

1 Answers1

1

Can't add this as a comment due to not enough rep, but perhaps you can do something similar to what Fred -ii- was alluding to by getting the column names from the information schema columns table, then executed a prepared statement (which I think is roughly the MySQL equivalent of dynamic SQL, but I'm not certain) to create a query that will get you a query result with only those image columns selected.

Perhaps see also Selecting columns whose name matches a regular expression in PostgreSQL and How To have Dynamic SQL in MySQL Stored Procedure.

Kdawg
  • 1,508
  • 13
  • 19
  • i think he was misinterpreting my question assuming i just want to do something like `show columns from` .etc. that's just going to get me the relevant field names, not the rows. i can easily match the field names with the values in php but i was wondering if there was a way to do it in sql. but what your showing me is a bit beyond my knowledge. – Alex Jul 16 '17 at 00:32
  • Yeah, I think if you want to do it in SQL, you'll have to go a route similar to what I linked to. If not, and this is probably what I would do anyway simply because it's really not that complex, then probably what you'll need to do is just explicitly select all those columns, which really shouldn't be a big deal, considering that you should know what the structure of your table is, and if you anticipate it changing often with new image* columns, but don't want to change your SQL statements, you could simply read in the column names from a configuration file or something. – Kdawg Jul 16 '17 at 00:42
  • yea right now im just getting all of the columns like image and matching it to an array that has all of the columns + values for the given row, so i essentially get there. just via php. – Alex Jul 16 '17 at 01:17