mysql setting for queries in a loop

Question

I am using script in php to insert large data in a loop in mysql each loop is selecting 10000 rows from one table and inserting those rows in another table

But as per my current configuration , mysql goes aaway after first loop of queries

means it goes out after inserting first 10000 rows

I am using zend server in ubuntu and mysql

i have tried to change my.cnf and php.ini but got no success

Thnx in advance to the ones who will provide solution.

<?php
include('db.php');
$conn = mysql_connect($SERVER, $USER, $PASSWORD) or die('cant connect to server'.mysql_error());
mysql_select_db($DATABASE);


//ps_orders
// truncate table ps_orders;
//select * from ps_customer order by id_customer desc limit 0,2;
//select * from ps_cart order by date_add desc limit 0,1
 // to fecth cart from sss database
//where id_customer >56020"
$conn1 = mysql_connect($SERVER,$NEW_USER,$NEW_PASSWORD);
mysql_select_db($DATABASE_NewDB,$conn1);
$sql="truncate table `ps_orders`";
if(mysql_query($sql,$conn1))
{
    echo "table truncated";
}
else
{
    mysql_errno();
}

$page =0;
$total_rows = 0 ;
while(true)
{
    $limit1 = ($page*9600)+1;
    $limit2 = ($page*9600)+9600;
    $queryContactID="SELECT id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd, courier_complete from $DATABASE.ps_orders limit $limit1,$limit2";
        $result = mysql_query($queryContactID) or die('query fetch sss address id error'.mysql_error());
        if(mysql_num_rows($result)>0){
            $carrier_query_arr = array();
            while($row = mysql_fetch_object($result)){
                $id_order = $row->id_order;
                $reference = $row->reference;
                $id_shop_group = $row->id_shop_group;
                $id_shop = $row->id_shop;
                $id_carrier = $row->id_carrier;
                $id_lang = $row->id_lang;
                $id_customer = $row->id_customer;
                $id_cart  =$row->id_cart;
                $id_currency  =$row->id_currency;
                $id_address_delivery  =$row->id_address_delivery;
                $id_address_invoice  = $row->id_address_invoice;
                $current_state =$row->current_state;
                $secure_key =$row->secure_key ;
                $payment =$row->payment;
                $conversion_rate =$row->conversion_rate;
                $module =$row->module;
                $recyclable =$row->recyclable; 
                $gift =$row->gift; 
                $gift_message =$row->gift_message; 
                $shipping_number =$row->shipping_number;
                $total_discounts =addslashes(trim($row->total_discounts));
                $total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
                $total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
                $total_paid =addslashes(trim($row->total_paid));
                $total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
                $total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
                $total_paid_real =addslashes(trim($row->total_paid_real));
                $total_products =addslashes(trim($row->total_products));
                $total_products_wt =addslashes(trim($row->total_products_wt));
                $total_shipping =addslashes(trim($row->total_shipping));
                $total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
                $total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
                $carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
                $total_wrapping =addslashes(trim($row->total_wrapping));
                $total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
                $total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
                $invoice_number =addslashes(trim($row->invoice_number));
                $delivery_number =addslashes(trim($row->delivery_number));
                $invoice_date =addslashes(trim($row->invoice_date));
                $delivery_date =addslashes(trim($row->delivery_date));
                $valid =addslashes(trim($row->valid));
                $date_add =addslashes(trim($row->date_add));
                $date_upd =addslashes(trim($row->date_upd));
                $courier_complete_count = addslashes(trim($row->courier_complete));
                $cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";
                ++$total_rows;
            }
            $cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
            $email_value = implode(',',$cart_query_arr);
            $cart_single_insert .= ' '.$email_value;
            $cart_query_arr = array();
            mysql_query($cart_single_insert,$conn1);
            ++$page;
        }
        else
        {
            echo "queries are done";
            break;
        }
}
// first while loop ends here

//    $i=0; 


 //    if(mysql_num_rows($result)>0){
 //        $j=0;

 //        $total_address = 0;

 //        $carrier_query_arr = array();
 //        /* Start of While 1 */
 //        while($row = mysql_fetch_object($result)){

//     $id_order = $row->id_order;
//     $reference = $row->reference;
//     $id_shop_group = $row->id_shop_group;
//     $id_shop = $row->id_shop;
//     $id_carrier = $row->id_carrier;
//     $id_lang = $row->id_lang;
//     $id_customer = $row->id_customer;
//     $id_cart  =$row->id_cart;
//     $id_currency  =$row->id_currency;
//     $id_address_delivery  =$row->id_address_delivery;
//     $id_address_invoice  = $row->id_address_invoice;
//     $current_state =$row->current_state;
//     $secure_key =$row->secure_key ;
//     $payment =$row->payment;
//     $conversion_rate =$row->conversion_rate;
//     $module =$row->module;
//     $recyclable =$row->recyclable; 
//     $gift =$row->gift; 
//     $gift_message =$row->gift_message; 
//     $shipping_number =$row->shipping_number;
//     $total_discounts =addslashes(trim($row->total_discounts));
//     $total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
//     $total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
//     $total_paid =addslashes(trim($row->total_paid));
//     $total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
//     $total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
//     $total_paid_real =addslashes(trim($row->total_paid_real));
//     $total_products =addslashes(trim($row->total_products));
//     $total_products_wt =addslashes(trim($row->total_products_wt));
//     $total_shipping =addslashes(trim($row->total_shipping));
//     $total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
//     $total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
//     $carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
//     $total_wrapping =addslashes(trim($row->total_wrapping));
//     $total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
//     $total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
//     $invoice_number =addslashes(trim($row->invoice_number));
//     $delivery_number =addslashes(trim($row->delivery_number));
//     $invoice_date =addslashes(trim($row->invoice_date));
//     $delivery_date =addslashes(trim($row->delivery_date));
//     $valid =addslashes(trim($row->valid));
//     $date_add =addslashes(trim($row->date_add));
//     $date_upd =addslashes(trim($row->date_upd));
//     $courier_complete_count = addslashes(trim($row->courier_complete));

//     $cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";

//     $j++;
    //     ++$total_address;

//     if($total_address > 5000){
 //                $total_address = 0;

//     $cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
//     $email_value = implode(',',$cart_query_arr);
//     $cart_single_insert .= ' '.$email_value;

//     $cart_query_arr = array();
//     mysql_query($cart_single_insert,$conn1);
// }
 //            print "Total records ".$j;
 //        }
 //    }/* End of While (1) */
?>

what i have tried so far : in php.ini i have changes the max_execution_time to 60000

@bluto max_execution_time=3000000 ,this i what i have in php.ini , but still doesn't work — Bhawin Parkeria, Sep 21 '15 at 10:42
@SureshKamrushi , its working fine in mysql directly , but i want it through php — Bhawin Parkeria, Sep 21 '15 at 10:43
You can use the batch insert to fast insert. Try this: [Batch insert in mysql][1] [1]: http://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql — Vahid Moradi, Sep 21 '15 at 10:51

score 0 · Answer 1 · answered Sep 21 '15 at 10:47

You can insert multiple rows at once. Try inserting like this:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Iterate your loop and create the query like this and insert after every 2k or 4k.

For more info: http://dev.mysql.com/doc/refman/5.6/en/insert.html

mysql setting for queries in a loop

1 Answers1