get data from mysql database

Question

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.

BenFousek · Answer

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.

joelde_freitas · Answer

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

BenFousek · Answer

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.')
});