Transform MySQL table and rows

Question

I have one problem here, and I don't even have clue what to Google and how to solve this. I am making PHP application to export and import data from one MySQL table into another. And I have problem with these tables.

In source table it looks like this:

And my destination table has ID, and pr0, pr1, pr2 as rows. So it looks like this: enter image description here

Now the problem is the following: If I just copy ( insert every value of 1st table as new row in second) It will have like 20.000 rows, instead of 1000 for example.

Even if I copy every record as new row in second database, is there any way I can fuse rows ? Basically I need to check if value exists in last row with that ID_, if it exist in that row and column (pr2 for example) then insert new row with it, but if last row with same ID_ does not have value in pr2 column, just update that row with value in pr2 column.

I need idea how to do it in PHP or MySQL.

Try this: http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by different table structure, but basically the same problem — Marc B, Feb 18 '13 at 00:21

Meta_data · Answer 1 · 2013-02-18T11:07:53.910

So you got a few Problems: 1) copy the table from SQL to PHP, pay attention to memory usage, run your script with the PHP command Memory_usage(). it will show you that importing SQL Data can be expensive. Look this up. another thing is that PHP DOESNT realese memory on setting new values to array. it will be usefull later on.

2)i didnt understand if the values are unique at the source or should be unique at the destination table.. So i will assume that all the source need to be on the destination as is. I will also assume that pr = pr0 and quant=pr1.

3) you have missmatch names.. that can also be an issue. would take care of that..also.

4) will use My_sql, as the SQL connector..and $db is connected.. SCRIPT:

 <?PHP
   $select_sql = "SELECT * FROM Table_source";
   $data_source = array();
   while($array_data= mysql_fetch_array($select_sql)) {
        $data_source[] = $array_data;   
         $insert_data=array();
   }
   $bulk =2000;
   foreach($data_source as $data){
     if(isset($start_query) == false)
      {
          $start_query = 'REPLACE INTO DEST_TABLE ('ID_','pr0','pr1','pr2')';
      }
     $insert_data[]=implode(',',$data).',0)';// will set 0 to the
     if(count($insert_data) >=$bulk){
        $values = implode('),(',$insert_data);
        $values = substr(1,2,$values);
        $values = ' VALUES '.$values;
        $insert_query = $start_query.' '.$values;
        $mysqli->query($insert_query);
        $insert_data = array(); 
     } //CHECK THE SYNTAX IM NOT SURE OF ALL OF IT MOSTLY THE SQL PART>> SEE THAT THE QUERY IS OK 
   }
   if(count($insert_data) >=$bulk) // IF THERE ARE ANY EXTRA PIECES..
      { 
        $values = implode('),(',$insert_data);
        $values = substr(1,2,$values);
        $values = ' VALUES '.$values;
        $insert_query = $start_query.' '.$values;
        $mysqli->query($insert_query);
        $insert_data = null; 
     }  
  ?>

ITs off the top off my head but check this idea and tell me if this work, the bugs night be in small things i forgot with the QUERY structure, print this and PASTE to PHPmyADMIN or you DB query and see its all good, but this concept will sqve a lot of problems..

No, pr0 is 1st record, in 1st table pr with index 0 has 5 quant, so in 2nd table has columns ( pr0, pr1, pr2 ) and value inside is quant. I hope you know what I mean now. — Nutic, Feb 18 '13 at 03:00
Hi, Sorry i didnt undestand this yet.. please describe the main key at the second table, fields and their source from the first table in. but this is can be easily configure at the code.. — Meta_data, Feb 18 '13 at 08:46
ID_ is primary key. I think it's called pivot. With that, I just reduce number of rows. I think I can do it in php, but it would take too much time to proceed, since I have to rebuild like 20.000 rows, and in each row I would have like 4 or 5 queries so it will make it over 100.000 queries ... — Nutic, Feb 18 '13 at 20:29
As i see ID is not a unique Therefore cant be the main index, maybe you use a combination of the fields as a unique key? any way i dont understand what exactly are you planing to do. the rows number that you mention will take around 2 sec for a SQL command and maybe 4 for php. if you need further assitance with you query / php proccess write down exactly which field goes where. Example : table1.id -> table2.id --> Unique/not unique key . index / not index . key field / not key field and so on. this will help you — Meta_data, Feb 19 '13 at 23:06

Transform MySQL table and rows

1 Answers1