php multiple mysql inserts

Question

I would like to read data from one table of my db (SELECT from pages) and copy them into multiple tables according to INSERTs. My script works fine, when Im copying to one or another table, but when I run it like below It will copy data only into the first one. Can u guys help me please?

      <?php

 // Pripojenie na databazu s overenim pripojenia
 $con = mysql_connect('localhost:8501' , 'root', '');

if(!$con) {
    die("Nepripojene ".mysql_error());
}  

 // Vyber databazy ikarus2 s overenim vyberu    
 $db = mysql_select_db('ikarus2',$con);
if(!$db) {
    die('Ziadny pristup k db ikarus2!'.mysql_error());
} 

 // MySQL SELECT from pages
 $result = mysql_query('SELECT uid, t3ver_oid, t3ver_id, t3ver_wsid, t3ver_label,      t3ver_state, t3ver_stage,
                t3ver_count, t3ver_tstamp, t3_origuid, tstamp, crdate, cruser_id,
                title, hidden, starttime, endtime, deleted, subtitle, nav_title, media, keywords, description, abstract, author, author_email,
                tx_impexp_origuid, tx_realurl_pathsegment, doktype, url, urltype, shortcut, shortcut_mode, t3ver_move_id, sorting,
                layout, fe_group FROM pages',$con);


 // MySQL INSERT into pages_language_overlay
 while ($row = mysql_fetch_array($result)){
$insertoverlay = "INSERT INTO pages_language_overlay( pid, t3ver_oid, t3ver_id,  t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage,
                        t3ver_count, t3ver_tstamp,  t3_origuid, tstamp, crdate, cruser_id, sys_language_uid, title, hidden, starttime,   endtime, 
                        deleted, subtitle, nav_title,    media, keywords, description, abstract, author, author_email, tx_impexp_origuid, tx_realurl_pathsegment,
                        doktype, url, urltype, shortcut, shortcut_mode) 
            VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]','$row[5]','$row[6]','$row[7]','$row[8]',
                        '$row[9]','$row[10]','$row[11]','$row[12]','5', '$row[13]','$row[14]','$row[15]','$row[16]','$row[17]','$row[18]',
                        '$row[19]','$row[20]','$row[21]','$row[22]','$row[23]','$row[24]','$row[25]','$row[26]','$row[27]','$row[28]',
                        '$row[29]','$row[30]','$row[31]','$row[32]')";
mysql_query($insertoverlay,$con);

 }

 // MySQL INSERT into tt_content
 while ($row = mysql_fetch_array($result)){
$insertcontent = "INSERT INTO tt_content( pid, t3ver_oid, t3ver_id, t3ver_wsid,    t3ver_label, t3ver_state, t3ver_stage, t3ver_count,
 t3ver_tstamp, t3ver_move_id, t3_origuid, tstamp, crdate, cruser_id, hidden, sorting,  media, layout, 
 deleted, starttime, endtime, fe_group, sys_language_uid, tx_impexp_origuid) 
            VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row [4]','$row[5]','$row[6]','$row[7]','$row[8]',
                        '$row[33]','$row[9]','$row[10]','$row[11]','$row[12]','$row[14]','$row[34]','$row[20]','$row[35]','$row[17]',
                        '$row[15]','$row[16]','$row     [36]','5','$row[26]')";
mysql_query($insertcontent,$con);   

   }

 ?>

Answer 1

The program is not structured well. The result set is already totally consumed when the control gets to the second loop. Instead, put them in the same loop to solve the immediate problem you have:

 // MySQL INSERT into pages_language_overlay
 while ($row = mysql_fetch_array($result)){
$insertoverlay = "INSERT INTO pages_language_overlay( pid, t3ver_oid, t3ver_id,  t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage,
                    t3ver_count, t3ver_tstamp,  t3_origuid, tstamp, crdate, cruser_id, sys_language_uid, title, hidden, starttime,   endtime, 
                    deleted, subtitle, nav_title,    media, keywords, description, abstract, author, author_email, tx_impexp_origuid, tx_realurl_pathsegment,
                    doktype, url, urltype, shortcut, shortcut_mode) 
        VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]','$row[5]','$row[6]','$row[7]','$row[8]',
                    '$row[9]','$row[10]','$row[11]','$row[12]','5', '$row[13]','$row[14]','$row[15]','$row[16]','$row[17]','$row[18]',
                    '$row[19]','$row[20]','$row[21]','$row[22]','$row[23]','$row[24]','$row[25]','$row[26]','$row[27]','$row[28]',
                    '$row[29]','$row[30]','$row[31]','$row[32]')";
mysql_query($insertoverlay,$con);

$insertcontent = "INSERT INTO tt_content( pid, t3ver_oid, t3ver_id, t3ver_wsid,    t3ver_label, t3ver_state, t3ver_stage, t3ver_count,
 t3ver_tstamp, t3ver_move_id, t3_origuid, tstamp, crdate, cruser_id, hidden, sorting,  media, layout, 
 deleted, starttime, endtime, fe_group, sys_language_uid, tx_impexp_origuid) 
        VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row [4]','$row[5]','$row[6]','$row[7]','$row[8]',
                    '$row[33]','$row[9]','$row[10]','$row[11]','$row[12]','$row[14]','$row[34]','$row[20]','$row[35]','$row[17]',
                    '$row[15]','$row[16]','$row     [36]','5','$row[26]')";
    mysql_query($insertcontent,$con);   

   }

 ?>

• However, what you probably want is available in plain SQL too, you can have an insert command that works with selected result set.

MySQL INSERT... SELECT:

INSERT INTO destTable (col1, col2, col3) 
    SELECT srcCol1, srcCol2, srcCol3 FROM srcTable;

This way it might be even quicker: the data does not need to travel to the server running the PHP code, and back to the DB - it is handled by the DB entirely.

• However also please note that mysql_* functions are deprecated as of PHP 5.5, so you should seriously move on to other solutions. I'd recommend using PDO.

Answer 2

Before executing the second while loop, try using mysql_data_seek():

mysql_data_seek($result, 0);

The pointer in the recordset $result needs to be reset to zero, otherwise it will still indicate that it's at the end.

OR

first store all the records in an array an then do an foreach to insert the data to the database

Answer 3

1.- Don't use mysql_* functions, are deprecated, use PDO or mysqli_* functions

2.- I think you don't understand well mysql_fetch_array, this functions returns next row of data AND moves the pointer to the next record, so, in the second while loop mysql_fetch_arraywill return false

So, you need to execute the two insert statements in the same while/loop:

// MySQL INSERT into pages_language_overlay
 while ($row = mysql_fetch_array($result)){
$insertoverlay = "INSERT INTO pages_language_overlay( pid, t3ver_oid, t3ver_id,  t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage,
                        t3ver_count, t3ver_tstamp,  t3_origuid, tstamp, crdate, cruser_id, sys_language_uid, title, hidden, starttime,   endtime, 
                        deleted, subtitle, nav_title,    media, keywords, description, abstract, author, author_email, tx_impexp_origuid, tx_realurl_pathsegment,
                        doktype, url, urltype, shortcut, shortcut_mode) 
            VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]','$row[5]','$row[6]','$row[7]','$row[8]',
                        '$row[9]','$row[10]','$row[11]','$row[12]','5', '$row[13]','$row[14]','$row[15]','$row[16]','$row[17]','$row[18]',
                        '$row[19]','$row[20]','$row[21]','$row[22]','$row[23]','$row[24]','$row[25]','$row[26]','$row[27]','$row[28]',
                        '$row[29]','$row[30]','$row[31]','$row[32]')";
mysql_query($insertoverlay,$con);

$insertcontent = "INSERT INTO tt_content( pid, t3ver_oid, t3ver_id, t3ver_wsid,    t3ver_label, t3ver_state, t3ver_stage, t3ver_count,
 t3ver_tstamp, t3ver_move_id, t3_origuid, tstamp, crdate, cruser_id, hidden, sorting,  media, layout, 
 deleted, starttime, endtime, fe_group, sys_language_uid, tx_impexp_origuid) 
            VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row [4]','$row[5]','$row[6]','$row[7]','$row[8]',
                        '$row[33]','$row[9]','$row[10]','$row[11]','$row[12]','$row[14]','$row[34]','$row[20]','$row[35]','$row[17]',
                        '$row[15]','$row[16]','$row     [36]','5','$row[26]')";
mysql_query($insertcontent,$con);   

   }

Answer 4

i think the multiple while loops will cause problems. give this a shot

    while ($row = mysql_fetch_array($result)){

        $insertoverlay = "INSERT INTO pages_language_overlay( pid, t3ver_oid, t3ver_id,  t3ver_wsid, t3ver_label, t3ver_state, t3ver_stage,
                                t3ver_count, t3ver_tstamp,  t3_origuid, tstamp, crdate, cruser_id, sys_language_uid, title, hidden, starttime,   endtime, 
                                deleted, subtitle, nav_title,    media, keywords, description, abstract, author, author_email, tx_impexp_origuid, tx_realurl_pathsegment,
                                doktype, url, urltype, shortcut, shortcut_mode) 
                    VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]','$row[5]','$row[6]','$row[7]','$row[8]',
                                '$row[9]','$row[10]','$row[11]','$row[12]','5', '$row[13]','$row[14]','$row[15]','$row[16]','$row[17]','$row[18]',
                                '$row[19]','$row[20]','$row[21]','$row[22]','$row[23]','$row[24]','$row[25]','$row[26]','$row[27]','$row[28]',
                                '$row[29]','$row[30]','$row[31]','$row[32]')";
        mysql_query($insertoverlay,$con);

$insertcontent = "INSERT INTO tt_content( pid, t3ver_oid, t3ver_id, t3ver_wsid,    t3ver_label, t3ver_state, t3ver_stage, t3ver_count,
 t3ver_tstamp, t3ver_move_id, t3_origuid, tstamp, crdate, cruser_id, hidden, sorting,  media, layout, 
 deleted, starttime, endtime, fe_group, sys_language_uid, tx_impexp_origuid) 
            VALUES ('$row[0]','$row[1]','$row[2]','$row[3]','$row [4]','$row[5]','$row[6]','$row[7]','$row[8]',
                        '$row[33]','$row[9]','$row[10]','$row[11]','$row[12]','$row[14]','$row[34]','$row[20]','$row[35]','$row[17]',
                        '$row[15]','$row[16]','$row     [36]','5','$row[26]')";
mysql_query($insertcontent,$con);   

         }