0

I have been trying with this for days i have 700+ records in a mysql database that i use a select query to get and then do some stuff to it. But when i want to reinsert the data it takes a long time to insert. How can i speed this up any help appreciated

       $zip_db_name = $wpdb->prefix . 'wa_zip_codes';

        $taxTable = $wpdb->prefix . "woocommerce_tax_rates";

        $taxTableLocation = $wpdb->prefix . "woocommerce_tax_rate_locations";   

        $getzip = $wpdb->get_results("SELECT zip FROM $zip_db_name ORDER BY zip DESC");

        foreach ($getzip as $retrieved_data){
            $zipcode = $retrieved_data->zip;                

            $url = 'http://dor.wa.gov/AddressRates.aspx?output=xml&addr=&city=&zip='. $zipcode .'';
            $xml = simplexml_load_file($url) or die("feed not loading");

            foreach ($xml as $value){
                //data to be stored
                $country = 'US';

                $state = 'WA';                      

                $city = '*';

                $rate = $xml['rate'];   

                $wpdb->query("START TRANSACTION");          

                    // Inserts values into woocommerce_tax_rates                                                    
                    $wpdb->query("INSERT INTO $taxTable (tax_rate_country, tax_rate_state, tax_rate, tax_rate_name) VALUES ('$country', '$state', '$rate', '$city')");              

                    $thelastId = $wpdb->get_results("SELECT tax_rate_id FROM $taxTable ORDER BY tax_rate_id DESC LIMIT 1;");

                    //Gets the ID of the last inserted value in wpdb

                    $lastid = $wpdb->insert_id;

                    //Inserts zip code value into wp_woocommerce_tax_rate_locations

                    $wpdb->query("INSERT INTO $taxTableLocation (tax_rate_id, location_code, location_type) VALUES ('$lastid', '$zipcode', 'postcode')");                       

                $wpdb->query("COMMIT");
            }   
        }
youngdeveloper
  • 9
  • 1
  • 8
  • 1
    Not possible as far as I'm aware. You may need a loop to insert it. – Script47 Aug 08 '15 at 03:07
  • How would you construct that loop with what i already have. Thanks – youngdeveloper Aug 08 '15 at 03:10
  • Well assuming all your `values` are in an `array`, you'd do a `for loop` an have it loop through that array. – Script47 Aug 08 '15 at 03:12
  • Currently the values are not in an array, if you could show an example of how to do this i would be greatful – youngdeveloper Aug 08 '15 at 03:14
  • people often insert 100 (or so) at a time, versus 1 at a time. much faster – Drew Aug 08 '15 at 03:42
  • 1
    http://stackoverflow.com/questions/12502032/insert-multiple-rows-with-one-query-mysql – Drew Aug 08 '15 at 03:44
  • 1
    Are you sure the insert's the slow part? I think it's more likely to be the fact you're downloading data from an external site for each zip code. Can you get the data for all zips in a single call, rather than doing them one at a time? – Hobo Aug 08 '15 at 08:10
  • 1
    Other possible (slight) improvements - if `$country`, `$state`, and `$city` never change, only set them once, outside the loops. And this loop `foreach ($xml as $value){` seems unnecessary - you never use `$value`. If you're just checking the `$xml` is populated, an `if` statement makes more sense. Finally, you're not using `$thelastId`. I assume you think it's populating `$wpdb->insert_id`, but I doubt very much it is. Try removing the line that sets it. But really - I think the slow part is likely to be the external download (`simplexml_load_file`). – Hobo Aug 08 '15 at 08:20
  • Hello Hobo thanks for your comment, could you explain how i would go about getting the zips in a single call – youngdeveloper Aug 08 '15 at 15:59
  • I don't know; it may not be possible. It depends on the site that you're using to get the rates (http://dor.wa.gov/). [The documentation](http://dor.wa.gov/content/findtaxesandrates/retailsalestax/destinationbased/clientinterface.aspx) seems to imply you need to provide a zip code, so you may be out of luck. Maybe try using the contact form on that page and asking. – Hobo Aug 09 '15 at 05:35

0 Answers0