-1

I am trying add one column in my Mysql database that sums all the columns starting by 'tokenvalid' which can take the value of 1 or 0.

And let's say I have 50 columns like that in my database (i.e. tokenvalid1, tokenvalid2 ...., tokenvalide50) with other columns between.

Please find below the code I would like to implement. I know that is not correct at all but it is just to give you an idea of what I am trying to do. Thank you for your help! 

'SELECT *, sum(column_name LIKE "tokenvalid"%) as total FROM points WHERE 1'
Thomas
  • 13
  • 5
  • 1
    A schema like that violates the [Zero, One or Infinity Rule](http://en.wikipedia.org/wiki/Zero_one_infinity_rule) of [database normalization](http://en.wikipedia.org/wiki/Database_normalization), so that's probably the source of your problems here. You can't sum random column names, you generally pick one and one only per `SUM()` call. – tadman Jul 04 '17 at 19:55

3 Answers3

0

This post should help you. The post describes how to get the columns and then query for results.

MySQL Like statement in SELECT column_name

DC IT
  • 221
  • 2
  • 5
0

Something like this should help you.

SET @colname = (SELECT GROUP_CONCAT(`column_name`) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='points' AND `column_name` LIKE 'tokenvalid%');
SET @table = 'points';
SET @query = CONCAT('SELECT SUM(',@colname,') FROM ', @table);
PREPARE stmt FROM @query;
EXECUTE stmt;

Similar to this answer by RocketDonkey

Sookie Singh
  • 1,543
  • 11
  • 17
0

If the string is in your external application (like PHP), sure, just construct the MySQL statement.

If the string is inside a MySQL table, you can't. MySQL has no eval() or such function. The following is impossible:

Suppose you have a table 'queries' with a field "columnname" that refers to one of the column names in the table "mytable". There might be additional columns in 'queries' that allow you to select the columnname you want...

INSERT INTO queries  (columname) VALUES ("name")
SELECT (select columnname from queries) from mytable

You can however work with PREPARED STATEMENTS. Be aware this is very hacky.

SELECT columnname from queries into @colname;
SET @table = 'mytable';
SET @s = CONCAT('SELECT ',@colname,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;
Bush
  • 261
  • 1
  • 11