get multiple values from table1, change them to values based on table2 and write the result to table1

hi

i can rewrite a column's values in a mysql databse based on another columns values, but this time i need to do this with a twist...

there are two tables (and multiple values at once): specifications and products.

products has two columns: specificationids and specificationorderids

specifications has two columns aswell: id and specificationorder

specificationids has multiple values formatted like this: ,31,29,27,18,

these are also the id in the specifications table. each id has a specificationorder value (same row, other column开发者_Go百科). now what i want to do is, that i want to swap the values of the id with the value of specificationorder and write these to specificationorderids in the products table in the same format.

ofcourse this process has to loop through all the id's in the products table.

i hope i made the problem clear and understandable

thanks for your help!

Swap id and specificationorder, specificationorder writen in specificationorderids if I understand you:

<?php
/*  Swap id and specificationorder */
$sql = "SELECT id, specificationorder from specifications";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result))
{
$id = $row[0];
$sporder = $row[1];
$sql = "UPDATE specifications SET id=$id, specificationorder=$sporder WHERE id=$id";
mysql_query($sql);
}

/* specificationorder writen in specificationorderids */
$sql = "SELECT specificationorder from specifications";
$result = mysql_query($sql);
while($row = mysql_fetch_row($result))
{
$sporder = $row[0];
$sql = "INSERT INTO products(specificationorderids) VALUES($sporder)";
mysql_query($sql);
}
?>

for the past couple days i havent been able to come up with a normal code, so heres what ive got so far. ive added comments so you know what i was thinking when experimenting with the code... as in my previous comment i include an image of what i want to achive: img822.imageshack.us/i/specm.png

//get specificationids 
$sql = "SELECT specificationids from products";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$spids = $row[0];

//get specifications
$sql2 = "SELECT id from specifications";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_row($result2);
$spid = $row2[0];

//get specificationorder
$sql3 = "SELECT specificationorder from specifications";
$result3 = mysql_query($sql3);
$row3 = mysql_fetch_row($result3);
$sporder = $row3[0];

//if the value of specificationids matches a specificationid, then....
while(VALUES($spids)==$spid)
{
$spids=$sporder; //...it should be replaced by the specificationorder
//and then update the specificationorderids column accordingly
$sql = "UPDATE products(specificationorderids) VALUES($spids)";
mysql_query($sql);
}