2

Below is my table TABLE

id   colname1   colname2     colname3
1    Alex       John         Mary
2    Alyssa     Eben         Stephen
3    Sandra     Tina         William

I try to use below query

SELECT * FROM TABLE WHERE CONCAT('colname',id) = 'Eben'

I expected the result would be from 2nd row 2nd column. But I get nothing. I referred many solutions which guides to use GROUP_CONCAT but I get nothing worked. Is this possible to do this with mysql?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Dhay
  • 585
  • 7
  • 29
  • 2
    The reason this doesn't work is because it's treating `CONCAT('colname', id)` as a string, not as the actual columns name. So because 'colname1' = 'Eben' is always false, you'll get no results. Not sure what the solution is yet, which is why I didn't answer, but hopefully understanding why it failed will help haha. – AdamMc331 Oct 05 '15 at 13:45
  • 1
    Thank you. Will try someother way. – Dhay Oct 05 '15 at 13:47
  • I will do some research as well and post an answer when I find it. – AdamMc331 Oct 05 '15 at 13:47
  • I've found this, which works if you are looking for a specific id value, but is a little tricky to check across the whole table: http://stackoverflow.com/questions/13282718/dynamic-conversion-of-string-into-column-name-mysql – AdamMc331 Oct 05 '15 at 14:00
  • Looks like what I expected. tried few times. will give more try until get a solution. – Dhay Oct 05 '15 at 14:23

2 Answers2

0

You can try dynamic sql. Populate a string variable with your select such as: DECLARE @str varchar(50) = 'SELECT * FROM TABLE WHERE colname@id = ''' + 'Eben' + '''' Then replace the placeholder string ("@id") with the column number you want such as: select REPLACE(@str, '@id', 2)

Last to actually execute the statement simply use "EXEC " + statement. ie: EXEC (@str)

Geoffrey Fernandez
  • 129
  • 2
  • 9
  • I am assuming you want to be able to reference a certain column by "colname" concatenated with the ID. – Geoffrey Fernandez Oct 05 '15 at 14:01
  • Sorry Geoffrey. I meant by "You are right" that I want to be able to reference a certain column by "colname" concatenated with the ID. But.. not that the solution worked for me. – Dhay Oct 09 '15 at 10:01
  • ok. But with the code provided you could do just that (by substituting the string "@id"). Please let me know where the answer is lacking. -thx – Geoffrey Fernandez Oct 09 '15 at 12:33
  • Actually I am unsure how to execute your code since it shows '[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @str varchar(50) = 'SELECT * FROM TABLE WHERE colname@id = ''' + 'Eben' ' at line 1'. – Dhay Oct 09 '15 at 13:27
0

I had to create another answer because this forum won't allow me to use the @(ampersand) in comments.

    DECLARE @str varchar(50) = 'SELECT * FROM TABLE WHERE colname@id = ''' +    
    'EBEN' + '''' 
    select @str = REPLACE(@str, '@id', 2)
    select @str '@str'  --This is just to examine the code, get the results of     
     --this and run it to test
--you should get this: SELECT * FROM TABLE WHERE colname2 = 'EBEN'
EXEC (@str) --this will actually execute your code
Geoffrey Fernandez
  • 129
  • 2
  • 9
  • Is the above query can be used in navicat or should we use it in php? Because still I get the same error. – Dhay Oct 10 '15 at 05:54
  • I asked the above question because I am familiar with Procedural style mysqli query only and I don't often use object oriented style and why I couldn't successfully run the solution you have given. – Dhay Oct 10 '15 at 06:36
  • Now I have this code. `$con=new PDO("mysql:host=10.10.10.10;dbname=TABLE","root","root@123"); $query=$con->prepare("DECLARE @str varchar(50) = 'SELECT * FROM TABLE WHERE colname@id = ''' + 'EBEN' + '''' select @str = REPLACE(@str, '@id', 2) select @str '@str' EXEC (@str)"); $query->execute(); $result=$query->fetchall(); //echo $result["id"]; foreach ($result as $key=>$value){ echo $key . "=" . $value; } echo count($result); $con=null;`. This code runs but gives nothing. the count shows zero. Where I could be wrong? – Dhay Oct 10 '15 at 09:57
  • Sorry, not familar with the language you are using (PHP I guess), but could you run the sql steps one at a time? And you proably don't need this bit of code in your sql: select @str '@str'. Also, if in your statement $ query=$con->prepare... couldn't you just use some type of string variable, then that way you only need to do a replace on the string there, not in the sql? – Geoffrey Fernandez Oct 12 '15 at 12:53
  • I think you guide me by using command line sql. if it is then I should improve my knowledge to do that. – Dhay Oct 12 '15 at 13:05
  • Oh god! I am trying to do this within php and mysql. – Dhay Oct 12 '15 at 13:25
  • thats ok, you should be able to figure out a way with what I gave you. Don't panic! – Geoffrey Fernandez Oct 12 '15 at 13:26
  • Thanks. Now I am trying to run this query in mysql command line. – Dhay Oct 12 '15 at 13:31