-1

I am trying to replace a column in the result of the select query as denoted in This reference but unlike the example I have many columns in the table thus I can not specify the name of every column in the select query.

I tried some ways to attain the same but none seems effective.

select 
    *, (REPLACE(REPLACE(role_id,1,"admin"),2,"moderator") AS role_id  
from user;

or 

Select * 
from user 
where role_id = (select REPLACE(role_id,1,"admin") as role_id from user;

Here we assume only two possible values for the role_id however at certain instanced it might have to get data from another table ie a different table that holds different ids and values corresponding to them.

So is there a way to attain the following conditions in a single query:-

  • to replace values of some fields returned from select query (assuming many columns writing the names of all the columns individually is not feasible)
  • to get the replacement values from different tables for different columns in single table.

I need to implement the above conditions in one query but the changes shouldn't be in the database only the result of select query needs to be optimized.

Already referred to the following too but could not help.

I am using phpmyadmin as engine and php as the implementation language.

Community
  • 1
  • 1
OshoParth
  • 1,492
  • 2
  • 20
  • 44

1 Answers1

1

If i have understood your question correctly, it's easier to use CASE/WHEN

SELECT *,
 CASE WHEN role_id = 1 THEN "admin" WHEN role_id = 2 THEN "moderator" END AS role_id 
FROM user;

But easier still maybe to have an array in PHP,

$roles = array("1" => "admin", "2" => "moderator", .... );

and look it up in the array. that will keep your query short and sweet. The advantage of this approach is that you don't need to change your query every time you add a new role. If you get a large number of roles (say dozens) you might actually want a separate table for that.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • thanks for helping, I tried case/when too but as i need to fetch the replacement data too dynamically from various tables thus this is not of much help, also the issue with implementing the php array is that due to use of result at many platforms i need to optimize the sql query itlself, – OshoParth Dec 16 '16 at 05:50
  • 1
    I have answered the question based on the information you have provided. – e4c5 Dec 16 '16 at 05:51
  • 1
    The most optimized solution is to do the php array since that does not put any strain on the database at all. But a join on a table that contains the roles would be equally efficient provided the tables are propery indexed. – e4c5 Dec 16 '16 at 05:52
  • unable to implement joins in the selective fields and replace the value fetched. problem still persists ! As joins return the proper value but am still unable to replace the results in fields returned by the select query. – OshoParth Dec 20 '16 at 04:31
  • what are you talking about? you don't have any joins in your question – e4c5 Dec 20 '16 at 04:34
  • kindly refer to the solution suggested by you in a previous comment.where it was suggested to use joins which seemed helpful but since i am unable to customize the final result of the select query the issue remains the same. – OshoParth Dec 20 '16 at 04:43
  • yes but you are asking a new question, about a join that I haven't seen. I said a join is efficient but I didn't give you a specific join. If you have a new question please post it as a new question. This answer correctly solves the original problem you have posted. – e4c5 Dec 20 '16 at 04:49