CODEWITHSUNDEEP
phpmysqlprefixalter
Ghost Echo
Ghost Echo

Reputation: 2067

mysql add "prefix" to every value in column

I need to add a 'prefix' in front of every value in a certain column.

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

Is there a way to use ALTER or MODIFY commands to do this?

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

Or perhaps a way to do it in PHP? Maybe get the value of the field, turn that 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 anyway to do that?

Upvotes: 16

Views: 22716

Answers (4)

Jose Samaniego
Jose Samaniego

Reputation: 110

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 only want to show it wit pn_ at the beginnning but if you want to change it also in the database you need to select all get the id value and update it with concatination

Upvotes: 1

Machavity
Machavity

Reputation: 31634

Actually it's even easier.

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

Without a WHERE clause it will update all the rows of your table

Upvotes: 37

Marc B
Marc B

Reputation: 360702

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

but why do this at the database layer? It's trivial to do it in PHP:

SELECT column_name ...

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

Upvotes: 5

Linger
Linger

Reputation: 15058

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

Upvotes: 0

Related Questions