piernas Posted January 12, 2015 Share Posted January 12, 2015 (edited) I'm lazy to create custom fields and move all the data from my 2.2 live shop each time I test a new version of oscommerce in my local server. Doing it by phpadmin is boring so I finally made a tool to extract the data from the live shop and place it on the test shop tables. I plan to upload the tool to the addons section but I have a problem. The php script I wrote has a function that reads data from old tables and inserts the results on the test server corresponding table. But now I'm facing the problem that the script takes too long to process bigger tables and I end up with a Maximum execution time error at the middle of the process. In my case I may have a few thousand rows in the bigger table, but I' m sure other shops will have even bigger databases. Do you know any solution (apart of increasing max_execution_time) to avoid the problem? Here's the function in case you want to take a look: function translada ($tabla, $campos) { $sql_insert = "INSERT INTO " . $tabla; $sql_insert .= " (".implode(", ", $campos).") "; // Abre dos conexiones a las bases de datos de origen y destino $conn = new mysqli(DESTINATION_SERVER, DESTINATION_USER, DESTINATION_PASS, DESTINATION_DB); // destino $conn2 = new mysqli(SOURCE_SERVER, SOURCE_USER, SOURCE_PASS, SOURCE_DB); // origen $customers_query = "SELECT * from " . $tabla; $origen = $conn2->query($customers_query); $contador =0; if ($origen->num_rows > 0) { while($row = $origen->fetch_assoc()) { $sql=""; $sql_values =""; $sql_values .= " VALUES ("; foreach ($campos as $valor) { $sql_values .= "'" . addslashes ($row[strval($valor)]). "'," ; } $sql_values = rtrim($sql_values, ','); $sql_values .= ")"; $sql = $sql_insert . $sql_values; if ($conn->query($sql) === TRUE) { $contador++; } else { echo "Error: " . $conn->error . "<br>"; } } echo $contador . " registros procesados en la tabla " . $tabla . ".<br>"; } $conn->close(); $conn2->close(); } Edited January 12, 2015 by piernas Quote Link to comment Share on other sites More sharing options...
piernas Posted January 12, 2015 Author Share Posted January 12, 2015 Whoops! I'm sorry found I posted in the wrong forum. i think it will fit better in the add-ons forum. Please any moderator feel free to move the topic. Quote Link to comment Share on other sites More sharing options...
♥bruyndoncx Posted January 12, 2015 Share Posted January 12, 2015 perhaps you can consider only adding the indexes after the table is filled up, that would shave some time off Quote KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt Link to comment Share on other sites More sharing options...
piernas Posted January 12, 2015 Author Share Posted January 12, 2015 Thanks, I'll do it and compare response times. Anyway I think I'll need something else; it takes minutes to load. I think I've seen some tricks before on another util for importing data, but I can't remember where or how it did it. Maybe javascript? Quote Link to comment Share on other sites More sharing options...
♥kymation Posted January 13, 2015 Share Posted January 13, 2015 You could load it in sections. Maybe check a timer, or just load a fixed number of rows, then reload the page. Pass the last row loaded as a parameter when you reload so the next one knows where to start. Or you could use mysqldumper since it already does that. It's a nice, open-source database backup and copy tool. i use it for the same thing you're doing on sites with over 50,000 products, and it handles the database size easily. Regards Jim piernas 1 Quote See my profile for a list of my addons and ways to get support. Link to comment Share on other sites More sharing options...
piernas Posted January 13, 2015 Author Share Posted January 13, 2015 I'll try your first solution, it sounds good. Will try mysqldumper too, it will be useful for sure, but I want to make an one-click tool because I think it's good for people who doesn't know how to use other tools. I think something similar into installation is a must for people upgrading their shops to newer versions. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.