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);   

   }

 ?>

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 — ppeterka
– ppeterka, Commented Sep 9, 2013 at 12:09
The mysql_* functions are no longer maintained and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use prepared statements with either PDO or MySQLi. — tereško
– tereško, Commented Sep 11, 2013 at 0:36

Nandakumar V · Accepted Answer · 2013-09-09 12:11:33Z

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

answered Sep 9, 2013 at 12:11

Nandakumar V

4,7255 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Liam Allan · Accepted Answer · 2013-09-09 12:10:40Z

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);   

         }

Sal00m · Accepted Answer · 2013-09-09 12:11:32Z

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);   

   }

ppeterka · Accepted Answer · 2013-09-09 12:17:07Z

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.

Collectives™ on Stack Overflow

php multiple mysql inserts

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related