best way to pass large arrays

[ilbiffi]

Hello, I'm new to the forum, and I need a suggestion.
I have an application that generates large arrays, with potentially hundreds of entries. I would like to store them in a mysql db via php, and to retrieve them by the same means.
To retrieve the data doesn't look particularly troublesome, as I think that any limit to downloadable data is really much higher than my needs.
The real problem would be the best way to upload the data using POST.
I have read that the serialize function is not really the best, but I have yet to understand the real troubles in using it.
Somewhere else I've read the suggestion to upload a file to my webserver and then to have mysql read and insert the contents, but this seems a bit clunky and probably much less safe.
What would you do in this case?
Kind regards
Marco

[requinix]

JSON is probably your best bet.

 

Upload: Do a POST with a request body containing the JSON data. PHP reads the body, decodes, and inserts into the database.

Download: PHP reads the data from the database, JSON-encodes it, and outputs it.

[ilbiffi]

JSON is probably your best bet.

 

Upload: Do a POST with a request body containing the JSON data. PHP reads the body, decodes, and inserts into the database.

Download: PHP reads the data from the database, JSON-encodes it, and outputs it.

 

JSON is more or less clear to me, is a sort of smarter way of formatting values; in fact I found a working script that reads from mysql and returns json-formatted data (I omit the $link and $query script as I suppose they're of no interest, they're the classic mysql connection and select).

 

 

<form method="post">

<name="user" />

<name="pass" />

</form>

<?php

include("../mapdb.php");

$result = mysqli_query($link, $query) or die("Error in Selecting " . mysqli_error($link));

//create an array

    $emparray = array();

    while($row =mysqli_fetch_assoc($result))

    {

        $emparray[] = $row;

    }

    echo json_encode($emparray);

 

    //close the db connection

    mysqli_close($connection);

?>

 

 

This gives me the following output

 

[{"i":"1","x":"12.213","y":"43.32"},{"i":"2","x":"-23.23","y":"43.234543"},{"i":"3","x":"-0.00123","y":"-2134.2332"},{"i":"4","x":"0.5432","y":"-8.3216564"}]

 

which is ok.

 

Now the problem is doing the reverse, where do I put json data in a post? The examples I've found so far all say to create a json file and then refer to it in a php script via the "json_decode" command.

However the problem is that I'd need to send data from a normal windows client with no php interpreter on it.

In practice I'd have to put json data directly from a POST variable, to be read by a php script on my server, and then put into a mysql db. Is this viable? can you post an example on how to format the file?

Marco

[mac_gyver]

I have an application that generates large arrays

 

 

and do you have control over this application such that you would be able to change or define what form and format the output data is presented as?  does this data source have the ability to send the data to a web server (to a URL) or does it require human intervention to get it to produce and display the data?

 

if you don't have any control over the data source, you will need to write code to process the data in whatever form and format it is available as. getting people to list all the possible ways to do something isn't useful, since all the suggestions may not even be compatible with the data source.

 

care to share some actual information about what this application/data source is and what form (file, text output on the display on a computer, ...) this data is available as, so that the suggestions being given will be targeted to the actual problem?

[ilbiffi]

and do you have control over this application such that you would be able to change or define what form and format the output data is presented as?  does this data source have the ability to send the data to a web server (to a URL) or does it require human intervention to get it to produce and display the data?

 

if you don't have any control over the data source, you will need to write code to process the data in whatever form and format it is available as. getting people to list all the possible ways to do something isn't useful, since all the suggestions may not even be compatible with the data source.

 

care to share some actual information about what this application/data source is and what form (file, text output on the display on a computer, ...) this data is available as, so that the suggestions being given will be targeted to the actual problem?

 

Yes, I have complete control over the format, I can send the text in json, xml or whatever. The data source is a client and has the ability to send POST request, the human intervention is just a button that triggers a call to a php script on the web server. Using this method I can easily retrieve output from select queries and store it my client's internal arrays.

This application produces sort of geospatial data, so it will be long lists of coordinates and strings, as they relate to objects in a 3d space and tags of these objects. The data is therefore available as 3d objects, their properties can be easily put into the client's arrays.

The problem is that I feel it would be better to avoid the creation of a separate file by the client, as I'd rather avoid write permissions, antivirus warnings, and so on. If the client could send the data directly it would be better, but my problem is in that case do you think that sending a few hundreds of lines all together is viable? do you suggest to batch the operation so that it's maximum 10 lines every time? or to send one line at a time?

[Barand]

I'd use a multiple insert query and batch in 1,000 rows at a time. EG

INSERT INTO tablename (col1, col2, col3) VALUES
(1, 2, 'aaa'),
(2, 2, 'bbb'),
(3, 2, 'ccc'),
(4, 3, 'ccc'),
(5, 4, 'aaa'),
.
.
.
(1000, 1, 'xyz');

[ilbiffi]

 

I'd use a multiple insert query and batch in 1,000 rows at a time. EG

INSERT INTO tablename (col1, col2, col3) VALUES
(1, 2, 'aaa'),
(2, 2, 'bbb'),
(3, 2, 'ccc'),
(4, 3, 'ccc'),
(5, 4, 'aaa'),
.
.
.
(1000, 1, 'xyz');

The issue was not the mysql query, but the php statements to correctly take the json encoded string and pass it to the mysql, I made it by using

 

$data = json_decode($json, true);

    foreach ($data as $row) {

        $i = $row['i'];

        $x = $row['x'];

        $y = $row['y'];

  mysqli_stmt_execute($st);

}