get data from mysql database

Discussion created by joelcongo on Aug 26, 2013
Latest reply on Sep 3, 2013 by btfou

Like • Show 0 Likes0
Comment • 4

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.

Outcomes

4 Replies

TimSparq Aug 26, 2013 5:13 PM
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.
Like • Show 0 Likes0
Actions
btfou Aug 27, 2013 8:12 AM
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.')
});
```
Like • Show 0 Likes0
Actions
joelcongo Sep 2, 2013 4:58 PM
how do i get the data automatically using the esrirequest() , when more data come in.
or what is the best practice to do this.
Like • Show 0 Likes0
Actions
btfou Sep 3, 2013 7:53 AM
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.
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

get data from mysql database

Outcomes

Related Content

Recommended Content