get data from mysql database

3116
4
08-26-2013 04:57 PM
joelde_freitas
New Contributor III
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.
0 Kudos
4 Replies
TimCollyer
Occasional Contributor
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.
0 Kudos
BenFousek
Occasional Contributor III
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.')
});
0 Kudos
joelde_freitas
New Contributor III
how do i get the data automatically using the esrirequest()  , when more data come in.
or what is the best practice to do this.
0 Kudos
BenFousek
Occasional Contributor III
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.
0 Kudos