Insert 60000 rows Mysql from PHP

Question

I have a MySQL database with a backend: PHP.

In one table I have to insert 60,000 rows.

We made a query into my PHP that returns 1,000 rows. For each rows we have to insert 60 rows. We thought make a loop but we don't know if is the best practices that way.

The part of the code that insert the data is:

$turnos = $db->query("SELECT * FROM turno t 
                    WHERE t.canchaId = :cancha 
                    AND t.fecha BETWEEN :fechaInicio AND :fechaFin
                    AND t.nombre LIKE :cadena
                    ORDER BY t.fecha,t.hora ASC",
            array("cancha" => $cancha["idCancha"], "fechaInicio" => $fechaInicio, "fechaFin" => $fechaFin, "cadena" => "%turno fijo%"));
foreach($turnos as $turno) {
    //turnos have 1000 rows
    $fecha = date_create($turno["fecha"]);
    date_add($fecha, date_interval_create_from_date_string('7 days'));
    $anioAuxiliar = 2017;

    while(2017 == $anioAuxiliar){
        //60 times
        $data1 = turno[data1]; 
        $data2 = turno[data2];
        ...
        $fechaAGuardar = (String) $fecha->format('Y-m-d');
        $result = $db->query("INSERT INTO turno(fechaAGuardar, data2, data3, data4, data5, data6, data7, data8)   VALUES(:fechaAGuardar, :data2, :data3, :data4, :data5, :data6, :data7, :data8)",
        array("fechaAGuardar" => $fechaAGuardar, "data2" => $data2, "data3" => $data3, "data4" => $data4, "data5" => $data5, "data6" => $data6, "data7" => $data7, "data8" => $data8));

        date_add($fecha, date_interval_create_from_date_string('7 days'));
        $anioAuxiliar = (int) $fecha->format("Y");
    }

    $cantidad_turnos = $cantidad_turnos + 1;
}

This php is into a hosting with phpmyadmin.

So my questions are:

This is the best way to insert 60,000 rows?

Shall we considerer take into account another constraint? (eg: phpmyadmin don't allow you insert that amount of rows)

Thanks for helping me, Any suggestions are welcome

//EDIT//

The inserts data change, we have to insert datetime, and for each loop we have to add 7 day the last date inserted. So we can't use insert with select

Possible duplicate of [Quickest way to Insert mass data Into Mysql database](http://stackoverflow.com/questions/15474675/quickest-way-to-insert-mass-data-into-mysql-database) — Elby, Dec 09 '16 at 13:35
Please, can give us an example? We don't know how to make a bath file with mysql. Other recommend us using batch. — Faustino Gagneten, Dec 09 '16 at 13:36
Possible duplicate of [INSERT with SELECT](http://stackoverflow.com/questions/5391344/insert-with-select) — MonkeyZeus, Dec 09 '16 at 13:36
@MonkeyZeus , we have to insert data into a loop, and also we have to change the values, eg: we insert datetime, so for each loop we have to insert datetime + 7 days. is dynamically data. — Faustino Gagneten, Dec 09 '16 at 13:38
NOTE: **`phpMyAdmin`** is a tool written in PHP IT IS NOT the database. MYSQL is the database — RiggsFolly, Dec 09 '16 at 13:38
Surely this is a ONE-OFF script. Worrying about if it is done in the best way is therefore unnecessary. _If it works use it then throw it away_ — RiggsFolly, Dec 09 '16 at 13:39
@FederickJons MySQL is perfectly capable of performing the dynamic calculations which you've described. — MonkeyZeus, Dec 09 '16 at 13:39
Please show the `SELECT` query which provides the data for `$turnos` — MonkeyZeus, Dec 09 '16 at 13:40
btw: typehinting does not work for primitive types `$anioAuxiliar = (int) $fecha->format("Y");` — Rápli András, Dec 09 '16 at 13:49

score 0 · Accepted Answer · answered Dec 09 '16 at 13:46

As a bunch of fellows described in the comments, INSERT/SELECT is the way to go if this data is in the same server/database. There's no need to use PHP at all. Your year comment can be handled with DATE_ADD.

Anyway, if there is any other requirement and you can't use PHP, consider using Bulk Data Loading.

Analysing your code, the MOST IMPORTANT TIP would be: don't use multiple INSERT INTO TABLE expressions. Each INSERT INTO will cause a round trip do the database and things will get really slow. Instead of it, concat multiple values with one INSERT INTO (example from the link):

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

Good luck!

Insert 60000 rows Mysql from PHP

1 Answers1