Insert more than one record into a table

Question

Insert more than one record into a table

Navigation

#1 by (1 votes)
#2 by (-2 votes)

0

I'm currently following the steps in the official api font . / p>

And in the current state of the project, I'm getting information from the table, changing and inserting data, without errors.

However, I would like to do a bulk insert in my table.

This is my code

$array = array();
foreach ($objects as $object) {
    array_push(
        $array,
        "('".$object->lat.",".$object->lng."','".$object->other->lat.",".$object->other->lng."')"
    );
}
$values = implode(",", $array);
$client = new Google_Client();
$tableId = "TableId";
$client->useApplicationDefaultCredentials();
$client->setScopes('https://www.googleapis.com/auth/fusiontables');
$service = new Google_Service_Fusiontables($client);

$service->query->sql("INSERT INTO ".$tableId." ('Location', 'City Location') VALUES ".$values); // I'm sorry, I forgot.

When I try to insert only one record with the same code, it works

This is my variable sql when you have more than one record:

INSERT INTO TableId 
('Location', 'City Location') 
VALUES 
('88.064342,-50.280747','-8.77,-36.62'),
(-55.781345,-69.294770','-28.24,-48.67'),
('14.696452,-26.844802','-19.92,-43.17')

Api returns the following error:

{  
  "error":{  
    "errors":[  
      {  
        "domain":"fusiontables",
        "reason":"badQueryCouldNotParse",
        "message":"Invalid query: Parse error near ',' (line 1, position 92).",
        "locationType":"parameter",
        "location":"q"
      }
    ],
    "code":400,
    "message":"Invalid query: Parse error near ',' (line 1, position 92)."
  }
}

php

asked by anonymous 20.02.2018 / 20:53

2 answers

-2

Mass insertion can be done by using UNION ALL as follows:

INSERT INTO TableId ('Location', 'City Location')  
SELECT '88.064342,-50.280747','-8.77,-36.62'   
UNION ALL 
SELECT '-55.781345,-69.294770','-28.24,-48.67' 
UNION ALL 
SELECT '14.696452,-26.844802','-19.92,-43.17'

20.02.2018 / 21:11

Select records between two dates with pre-established minute intervals Update in database table

score 1 · Accepted Answer

The syntax for entering more than one record in the google fusion API is another, as seen in:

link

INSERT

To insert one or more lines, the HTTP POST request SQL syntax is as follows:

INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);
.
.
.
INSERT INTO table_id (nome_coluna, nome_coluna2, nome_coluna3) VALUES (valor1, valor2, valor3);

Note that this syntax is only valid for 500 records, so you can loop in your object and enter 500 to 500.

My code to enter, from 500 to 500, using Laravel looks like this:

/* 
Esse método pega de 500 em 500 registros, no caso de a tabela ter uma grande quantidade 
de registros, é a mais viável
*/
$objects = Model::with('other')->chunk(500, function ($objects) { 

    $tableId = "tableId";
    foreach ($objects as $object) {
        $sql = 'INSERT INTO ''.$tableId.'' 
        ('Location', 'Other Location') 
        VALUES 
        ("'.$object->lat.",".$object->lng.'","'.$object->other->lat.",".$object->other->lng.'");';
    }

    $client = new Google_Client();
    $client->useApplicationDefaultCredentials();
    $client->setScopes('https://www.googleapis.com/auth/fusiontables');
    $service = new Google_Service_Fusiontables($client);
    $service->query->sql($sql);
    sleep(2); // Para dar um tempo ao web service e não sobrecarregar
});

The total data size of an order should not exceed 1MB and the total number of cells in the table being added should not exceed 10,000.

To better circumvent what a cell is:

Thatis,it'snousewantingtoaddastatementwith500columns,themaximumstatmentsyouwouldhavepermissionare20.

Ifyouwanttoinsertanexcessivenumberofrows,itisbesttousetheimportmethod,whichwillbefasterandmorereliablethanusingmanySQLINSERTstatements.

Evenafterfollowingtheobservationsandrequirementsofthedocumentation,Inoticedthatthedatainsertionwastooslow,returningalotofapierror,indicatingthattheserverisbusy.SoIwenttothesecondoption,tousetheimportRowsmethod.

Mytablehasthefollowingstructure:

TogetthecsvfilethatwillbeimportedIusedtheadministrativepanelofphpmyadmin.Iusedthefollowingsqltomountthequery:

SELECT''AS'Text',''AS'Number',CONCAT(object.lat,",", object.lng) AS 'Location',
    '' AS 'Date',
    CONCAT(other.lat, ",", other.lng) AS 'City Location'
FROM
    'ads'
INNER JOIN cities ON object.city_id = other.id

I will insert the step by step that followed until obtaining the necessary csv:

AfterthatIputthegeneratedfileinalocationaccessiblebymyserverandexecutedthefollowingcode:

$client=newGoogle_Client();$client->useApplicationDefaultCredentials();$client->setScopes('https://www.googleapis.com/auth/fusiontables');$service=newGoogle_Service_Fusiontables($client);$tableId="tabelId";
$service->table->importRows(
    $tableId, 
    array(
        'data' => file_get_contents('objects.csv'), 
        'uploadType' => 'media',
        'mimeType' => 'application/octet-stream'
    )
);

And VOILA! 100,000 records inserted into the successful fusion table: