php script to move data between columns with mysql

Question

What I want to achieve is to move the data between 2 rows within one table.

Column A
--------
FN2
1 200x310mm
2 400x260mm[+0.84]
3 500x500mm[+11.34]

Column B
--------
0.0000   
0.0000    
0.0000    
0.0000

This is how it should look like after the data move:

Column A
--------
FN2
1 200x310mm
2 400x260mm
3 500x500mm

Column B
--------
0.0000   
0.0000    
+0.84    
+11.34

What I want is that the query between the [ ] is moved to column B and replaces the 0.0000

How can I achieve this?

Kind Regards

do you want to move all data of columnA to ColumnB respectively? — Yadav Chetan
– Yadav Chetan, Commented May 10, 2013 at 15:21
Only the content between the two [ ], see the last to items in the example above. — Wouter
– Wouter, Commented May 10, 2013 at 15:22
then you first fetch all records then check every field of column with [] then replace it — Yadav Chetan
– Yadav Chetan, Commented May 10, 2013 at 15:25
Are you sure you want '1','2',and '3' in the same column as the dimensions? That seems really messy! — Strawberry
– Strawberry, Commented May 10, 2013 at 16:49

Georges Brisset · Accepted Answer · 2013-05-10 16:51:03Z

1

just to illustrate what Yadav said

$query = "SELECT columnID, columnA FROM table";
$result = mysql_query($query,$conn);

while ($row = mysql_fetch_array($result)){

$id = $row['columnID'];
$a = $row['columnA'];

$pos1 = strpos($a,"[")+1;
$pos2 = strpos($a,"]");

$b = substr($a,$pos1,$pos2-$pos1);

$query = "UPDATE table SET columnB = $b WHERE columnID = $id";
mysql_query($query,$conn);


}//end while

edit: Yadav obviously proposed a better answer while I was typing mine...

answered May 10, 2013 at 16:51

Georges Brisset

2562 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Georges Brisset Over a year ago

@YadavChetan: thx, I liked your regex approach but I code a lot for people with limited php skills so I have to keep my code clean and simple.

Yadav Chetan · Accepted Answer · 2013-05-10 17:10:29Z

0

try this it works for you here i used id as unique key ... and test is my database

<?php 
    $con=mysql_connect("localhost","root","");
    $db=mysql_select_db("test");
    $query=mysql_query("SELECT * FROM test where columnA LIKE '%[%]'");
    while($row=mysql_fetch_assoc($query))
    {
        if(preg_match_all('/\[(.*?)\]/',$row['columnA'],$match))
        {            
           mysql_query("UPDATE test SET columnB='".$match[1][0]."' WHERE id=".$row['id']."");
        }
    }

?>

edited May 10, 2013 at 17:10

answered May 10, 2013 at 16:34

Yadav Chetan

1,9022 gold badges23 silver badges43 bronze badges

Collectives™ on Stack Overflow

php script to move data between columns with mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related