how to create a mysql table (n fields) from n substrings of another table (1 field)

[cardeal]

Hi!

I have a long field on a mysql table and I would like to create a new table (from the old one) with the (new) content distributed into several fields. [To select the sub-strings is not a problem: the problem is how to 'create' the new table]

Example:

oldField: [records]
1. [ia/] pace [pt] paz [an] peace [ge] Friede ... [/ai]
2. [ia/] luce [pt] luz [an] light [ge] Licht ... [/ai]
3. [ia/] rege [pt] rei [an] king [ge] König ... [/ai]
4. [ia/] lege [pt] lei [an] law [ge] Gesetz ... [/ai]

newFields (intended):
1.ia 2.pt 3.an 4.ge
pace paz peace Friede
luce luz light Licht
rege rei king König
lege lei law Gesetz

I have tried something like this:

mysql_select_db(oldBase);
$query_rsBase = "SELECT * FROM oldBase ORDER BY oldField ASC";
$rsBase = mysql_query($query_rsBase, $conn) or die(mysql_error());
$row_rsBase = mysql_fetch_assoc($rsBase);
$totalRows_rsBase = mysql_num_rows($rsBase);
...
Here goes the sub-string operations. [$ia=substr($row_rsBase['oldField']... etc.]
...
for ($i=1;$i<=$totalRows_rsBase;$i++) {
$insertSQL = sprintf("INSERT INTO newBase (ia, pt, an, ge, ...) 
VALUES (%s, %s, %s, %s, ...)",
GetSQLValueString($ia, "text"),
GetSQLValueString($pt, "text"),
GetSQLValueString($an, "text"),
GetSQLValueString($ge, "text"),
...                  
GetSQLValueString($.., "text")); 
 
mysql_select_db($database_newBase, $conn);
$Result = mysql_query($insertSQL, $conn) or die(mysql_error());
 
$codigo=mysql_insert_id();
}

But although I have c. 30 thousand records I only get the first record repeated c. 10 thousand times...

Thanks in advance,
cs

[cardeal]

Solved!
cs