Using mysql's CONCAT() for a column name

Question

I want to UPDATE a database column by concatenating a string with a function. let's say that I have two columns sum_likes_c1 and sum_likes_c2 and I have a function called $class has 1 or 2 value

so I want the query to update the column based on $class value

my current code (not working):

if (isset($function)) {
    $class = 1;
    } else $class = 2;

    $update_likes = mysqli_query($con, "UPDATE posts SET CONCAT(sum_likes_c,'$class') ='$total_likes' WHERE id='$post_id'");

Any help would be appreciated

This kind of problem is symptomatic of poor design. Any time you find yourself with enumerated columns, consider whether your schema design is optimal. — Strawberry, Nov 18 '19 at 07:58

score 1 · Accepted Answer · answered Nov 18 '19 at 08:07

CONCAT() is a string function, you cannot use it for column names which are identifiers. You have to concatenate in PHP. See how to do it properly

That said, if you need to concatenate to get a coulmn name, most likely (like 99%) your database design is wrong. There must be a separate table to collect your likes, preferably using INSERT, not UPDATE

score -1 · Answer 2 · answered Nov 18 '19 at 09:21

-1

Try with this:

if (isset($function)) {
$class = 1;
} else $class = 2;

$column = "sum_likes_c" . $class;

$update_likes = mysqli_query($con, "UPDATE posts SET ".$column." = ".$total_likes." WHERE id = ".$post_id);

answered Nov 18 '19 at 09:21

Andrea

77
6

Please be wary of SQL injection. Use prepared statements and white-listing. – Dharman Nov 19 '19 at 12:04

Using mysql's CONCAT() for a column name

2 Answers2