472,985 Members | 2,355 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,985 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 1479
Oct 21 '10 #2

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

Similar topics

by: Fiala | last post by:
Table A +--------+-----------+ | A-num | text | | 1 | | | 2 | | | 3 | | | 4 | | | 5 | | +--------+-----------+
by: Glenn P. | last post by:
Sorry, CDMA, I searched this NG on several combinations of words, but I didn't find a relevant hit, so here's my newbie question: I have an Access 2002 table which contains data extracted from...
by: sparks | last post by:
I was copying fields from one table to another. IF the var name starts with milk I change it to egg and create it in the destination table. It works fine but I want to copy the description as...
by: bitoulis | last post by:
Hi, is it possible to use the records of a table as the field names of another table? If yes, how is it done? Thanks in advance Laertes
by: Leonardo Gangemi | last post by:
How to align right a table based on another table created dinamically? Leonardo
by: S0ck3t | last post by:
Please could I have some help on matching records between tables. I want to return a check (true/false) stating whether the field combination in table 1 occurs in table 2. Obviously it's easy with...
by: Shizbart | last post by:
MS Access 97 Beginner/Moderate Level User I am trying to create a Database to track Workouts in MS Access 97. I have one Table named Workouts that contains the following Fields: Workout...
by: joshua.nicholes | last post by:
I have an access database that consists of two tables.A data collection table and a species list table. The data collection table has about 1500 records in it and the species list has about 600....
by: MrCrunchy | last post by:
Hi All I have 2 tables (as shown below), if i add a row to the FILES TABLE then the myid value is added to mydata list in the PLAYLISTS TABLE, based on the genre matching the myname values. Hope...
by: Arielle | last post by:
Problem: I have a few related tables that collects information about a given publication. The information collected varies based on the type of collection and is stored in a few different tables. ...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.