Mysql add "prefix" to each column

Question

I need to add a “prefix” before each value in a specific column.

Example: all fields in column x: 200, 201, 202, 203, etc. I need them to be pn_200, pn_201, pn_202, pn_203, etc.

Is there any way to use ALTER or MODIFY commands for this?

I would like something like ADD to BEGINNING of * column_name 'pn_'

Or perhaps a way to do this in PHP ? Maybe get the value of the field, turn it into a variable and do something like.

 `$variablex = `'SELECT column_name FROM table' $result = mysqli_query($con, variablex); foreach($r=mysqli_fetch_row($result) { `ADD TO BEGINNING OF * column_name 'pn_'` 

Is there any way to do this?

Answer 1

This is actually even easier.

 UPDATE table SET column_name = CONCAT('pn_', column_name) 

Without a WHERE clause, it will update all rows in the table

Answer 2

 SELECT concat('pn_', column_name) AS column_name FROM yourtable 

but why do it at the database level? It is trivial to do this in PHP:

 SELECT column_name ... while($row = mysql_fetch_assoc($result)) { $data = 'pn_' . $row['column_name']; } 

Answer 3

I think this is what you want

 $que = "SELECT column_name FROM table"; $res = mysql_query($que, $con); if(mysql_num_rows($res)>0){ while($row = mysql_fetch_array($res)){ echo "PN_". $row['column_name']; } } 

if you want to show it only with pn_ at the beginning but if you want to change it in the database too, you need to select everything to get the id value and update it using concatenation

Answer 4

 UPDATE MyTable SET MyField = CONCAT('pn_', MyField)