topic Re: get data from mysql database in ArcGIS JavaScript Maps SDK Questions

get data from mysql database

joelde_freitas — Mon, 26 Aug 2013 23:57:14 GMT

hi, I am working on a project. I am using the cluster sample. my MySQL database has coordinates in it.
how can I get the data on the map using the esriRequest()? is there an other solution for this?

I need this solution so I can finish my project, end of this month.
Thanks in advanced.

Re: get data from mysql database

TimCollyer — Tue, 27 Aug 2013 00:13:10 GMT

I would create an event layer (under data management tools in ArcMap), publish that to my ArcGIS Server and then you can do anything (display/query/etc) with that map service.

I do this from an Oracle database for a dataset of a few thousand records and it works reasonably well.

Re: get data from mysql database

BenFousek — Fri, 10 Dec 2021 20:10:05 GMT

Joel,
You can create your own json with php. Php is easy but any server side scripting, which can access the database will work, e.g. asp.net, etc. Php has the json_encode() function which converts arrays to json. Here's a sample that returns json of map annotation projects to the application for the user to select one to load.

[PHP]<?php

$user = $_POST["user"];

$mysqli = new mysqli("HOST", "USER", "PASSWORD", "DATABASE");

if ($mysqli->connect_errno) {
$connectfail = array(
    'success' => false,
    'message' => $mysqli->connect_error
);
echo json_encode($connectfail);
return;
}

if ($user === '*') {
$query = "SELECT id, project, description, created_by, created_time, updated_by, updated_time FROM anno_projects";
} else {
$query = "SELECT id, project, description, created_by, created_time, updated_by, updated_time FROM anno_projects WHERE created_by = '" . $user . "'";
}

$result = $mysqli->query($query);

if ($mysqli->error) {
$loadfail = array(
    'success' => false,
    'message' => $mysqli->error
);
echo json_encode($loadfail);
return;
}

while ($row = $result->fetch_assoc()) {
$project    = array(
    'id' => (int) $row["id"],
    'project' => $row["project"],
    'description' => $row["description"],
    'createdBy' => $row["created_by"],
    'createdTime' => (float) $row["created_time"],
    'updatedBy' => $row["updated_by"],
    'updatedTime' => (float) $row["updated_time"]
);
$projects[] = $project;
}

$result->close();

$loadsuccess = array(
'success' => true,
'projects' => $projects,
'message' => 'Projects successfully retrieved.'
);
echo json_encode($loadsuccess);

$mysqli->close();
?>[/PHP]

Use esri.request to post any params and handle the response:

esri.request({
  url: 'html/anno/project-get-projects.php',
  content: {
    user: user
  },
  handleAs: 'json',
  callbackParamName: 'callback'
}, { usePost: true }).then(function (response) {
  if (response.success) {
    //console.log(response);

    //create dgrid for user to select project to load
  } else {
    console.log(response.message);
    app.error(response.message);
  }
}, function (error) {
  console.log(error);
  app.error('An error has occured.')
});

Re: get data from mysql database

joelde_freitas — Mon, 02 Sep 2013 23:58:06 GMT

how do i get the data automatically using the esrirequest() , when more data come in.
or what is the best practice to do this.

Re: get data from mysql database

BenFousek — Tue, 03 Sep 2013 14:53:58 GMT

how do i get the data automatically using the esrirequest() , when more data come in.
or what is the best practice to do this.

It depends on how many points you are going to load. You can get all the points in one server call and add them to the map (equivalent to FeatureLayer SNAPSHOT mode), or you can connect to the map's zoom-end & pan-end events, requesting just the points within the map's extent (equivalent to FeatureLayer ONDEMAND mode).

The latter seems like it would work best if new database entries are being created in real time albeit quite a bit more complex on the client side. I would create a custom layer class in that scenario.