NilsB NilsB - 1 month ago 29
reST (reStructuredText) Question

Simple REST API for use with Ext JS

I would like to start learning Ext JS. Seems to be an interesting and powerful Framework.

As I can imagine to replace some very old PHP/MySQL web applications, I would like to start with an Ext JS example with a working MySQL connection. I thought of REST, but that's just my idea, not a must. Happy to hear other solutions.

So the next idea which I had was, to find a simple REST API in PHP. Seems it doesn't have to be very complicated. Just read DB records, update, create and sometimes delete data as well. Few users. Intranet environment, so limited different browsers. Limited security requirements. To me it seems, something like that should already exist and be working quite stable.

And this is where I got stuck.

My Ext JS Example is here:
http://examples.sencha.com/extjs/6.2.0/examples/classic/restful/restful.html

The two best simple REST APIs which I found are:

1) https://www.leaseweb.com/labs/2015/10/creating-a-simple-rest-api-in-php/

2) https://github.com/mevdschee/php-crud-api

But 1) is only for reading from the DB, 2) doesn't work properly, I can write but reading doesn't work. The Output is different to 1).

I would really like to focus on Javascript and UI instead of understanding fully REST/SOAP/CRUD and whatever. I just need to write data to a DB read it from there. Does it have to be so complicated? Can someone give me a hint for a simple and lightweight solution?

Additionally, I am wondering why


  • so many places where someone talks about REST, but the protocol seems not to be defined clearly.

  • such an exciting and sophisticated Framework like Ext JS exists. But there's hardly any information on how to do the backend. (It simply doesn't work without the backend).


Answer

I found that the REST API mentioned in the first link is nearly sufficient for the Ext JS example. It needed only a few minor changes and everything works. See here for more details.

error_reporting(E_ERROR | E_PARSE);

// get the HTTP method, path and body of the request
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
$input = json_decode(file_get_contents('php://input'),true);

// connect to the mysql database
$link = mysqli_connect('localhost', 'user', 'password', 'table');
mysqli_set_charset($link,'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request)+0;

// escape the columns and values from the input object
$columns = preg_replace('/[^a-z0-9_]+/i','',array_keys($input));
$values = array_map(function ($value) use ($link) {
  if ($value===null) return null;
  return mysqli_real_escape_string($link,(string)$value);
},array_values($input));

// build the SET part of the SQL command
$set = '';
for ($i=0;$i<count($columns);$i++) {
  $set.=($i>0?',':'').'`'.$columns[$i].'`=';
  $set.=($values[$i]===null?'NULL':'"'.$values[$i].'"');
}

// create SQL based on HTTP method
switch ($method) {
  case 'GET':
    $sql = "select * from `$table`".($key?" WHERE id=$key":''); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete from `$table` where id=$key"; break;
}

// excecute SQL statement
$result = mysqli_query($link,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error());
}

// print results, insert id or affected row count
if ($method == 'GET') {
  if (!$key) echo '[';
  for ($i=0;$i<mysqli_num_rows($result);$i++) {
    echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
  }
  if (!$key) echo ']';
} elseif ($method == 'POST') {
  echo '{ "success":true, "data":[ { "id":'.mysqli_insert_id($link).' }]}';
} else {
  echo mysqli_affected_rows($link);
}

// close mysql connection
mysqli_close($link);