471,319 Members | 1,578 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

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


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]


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:
Expand|Select|Wrap|Line Numbers
  1. mysql_select_db(oldBase);
  2. $query_rsBase = "SELECT * FROM oldBase ORDER BY oldField ASC";
  3. $rsBase = mysql_query($query_rsBase, $conn) or die(mysql_error());
  4. $row_rsBase = mysql_fetch_assoc($rsBase);
  5. $totalRows_rsBase = mysql_num_rows($rsBase);
  6. ...
  7. Here goes the sub-string operations. [$ia=substr($row_rsBase['oldField']... etc.]
  8. ...
  9. for ($i=1;$i<=$totalRows_rsBase;$i++) {
  10. $insertSQL = sprintf("INSERT INTO newBase (ia, pt, an, ge, ...) 
  11. VALUES (%s, %s, %s, %s, ...)",
  12. GetSQLValueString($ia, "text"),
  13. GetSQLValueString($pt, "text"),
  14. GetSQLValueString($an, "text"),
  15. GetSQLValueString($ge, "text"),
  16. ...                  
  17. GetSQLValueString($.., "text")); 
  19. mysql_select_db($database_newBase, $conn);
  20. $Result = mysql_query($insertSQL, $conn) or die(mysql_error());
  22. $codigo=mysql_insert_id();
  23. }
But although I have c. 30 thousand records I only get the first record repeated c. 10 thousand times...

Thanks in advance,
Oct 20 '10 #1
1 1393
Oct 21 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.