mevdschee / php-crud-api Goto Github PK

Single file PHP script that adds a REST API to a SQL database

License: MIT License

PHP 98.42% Shell 1.24% Dockerfile 0.33%

api-server restful mysql geospatial php postgresql php-api postgis crud rest-api openapi swagger automatic-api database multi-database sql-database geojson sqlserver sqlite

php-crud-api's People

Contributors

Stargazers

Watchers

Forkers

llanox moceannl tbarmann wildantea proj-http spandansingh ghermans liubo2055 edioi 4nn4bel tdhieu diengyacinedieng zysyyz gorocacher racd eliudiaz janusnic resident0 mannimanfred datatonic nithyanantham vicall toddses ibrahsissoko kshukla zetazaw luisvillasenor pellmellism pasarkode dineshraj01 makayi skyview059 rokirt aminempdam rakesh-mohanta ghazigtari rodrigodias mrozu rafaelcoutinho hugovoerman claudioborgogno imzali securecloud-biz codearchival quabenaa rahman541 jenalgit xivol soltrinox zerosimms onyxhat mvdriel arjanbosboom starry16 dhimalo degoya douglasevaristo jns4u twanoo67 linkeefe cleanique-coders henninghall khoasuperman qq1722332572 codenpk nicolasrabrenovic kabircse shamimislam chaelwafo justinleonardo janglapuk filipednb mbuschgens rahmatcmos m4dfry beingsane andrelotto chefkoch-dev yarnball systemx-solutions utkinolegv zubzero mvjc88 digital-entity badrzem rhizalpatrax64bit williansmartins icalderond iwane021 pandanaran alphadevteam tanzin omar-adel colinwaddell sanctumware revcozmo shapilokk irudayarajisawa vesteves locvfx

php-crud-api's Issues

Underscore in id doesn't work

I am trying to get an row by "api.php/tablename/id" but it doesn't work when my string id contains a underscore(_). When I use "api.php/tablename" all rows are shown, including those which contains underscore. Am I doing something wrong? Thanks again for a brilliant API.

Authentication for api

How can I authentic a user accessing the api??

Multiple records to insert

It is possible to send multiple records to insert ?
Sample:

{
    "users": [{
        "columns": ["id", "user", "document", "pass"]
        }, {
        "records": [
            ["1", "leo", "1232465", "pass123"],
            ["2", "kosvoski", "3124576", "pass123"]
        ]
    }]
}

Optionally restrict include to only return specified tables

Your example for includes is:
http://localhost/api.php/posts?include=categories,tags,comments&filter=id,eq,1

Yet it also returns the post_tags table. Is there a way to force it to return only the specified list of tables in the include parameter?

I can't update a record with Angularjs $http.put()

This is my code:
.factory('Cap',function($http){
var url = 'http://localhost/mysql-crud-api/api.php/cap/1/';
var dd = {};
dd.gData = function(){
var data = ({
tot : 99,//This is the field that I want to update.
});
return $http.put(url,data);
}
console.log(dd);
return dd;
});

This is the response:

OPTIONS http://localhost/mysql-crud-api/api.php/caps/1/ (anonymous function) @ ionic.bundle.js:18526sendReq @ ionic.bundle.js:18327status.$get.serverRequest @ ionic.bundle.js:18043processQueue @ ionic.bundle.js:21888(anonymous function) @ ionic.bundle.js:21904promises.$get.Scope.$eval @ ionic.bundle.js:23100promises.$get.Scope.$digest @ ionic.bundle.js:22916promises.$get.Scope.$apply @ ionic.bundle.js:23205done @ ionic.bundle.js:18358completeRequest @ ionic.bundle.js:18548requestLoaded @ ionic.bundle.js:18489
(index):1

XMLHttpRequest cannot load http://localhost/mysql-crud-api/api.php/cap/1/. Response for preflight has invalid HTTP status code 404

I've been 6 hours and not find the problem.

P.D. It works perfectly if I do with Advanced Rest Client (Chrome Extension)
URL Extension: https://chrome.google.com/webstore/detail/advanced-rest-client/hgmloofddffdnphfgcellkdfbfbjeloo?utm_source=gmail

Thanks for the help!!!

HTTP Service Issue

The issue is on my end and I know this, but please give me some advice or something.

The issue is that the way I plan to use the API and connect to it from can only use the GET and POST parameters and I was wondering if you had any way to still be able to modify all of the database in all ways as in by giving my query in my POSTs.

LUA:
local Array = {
Action="UPDATE"; --Example
}

Which would give the API what it needed to know to set up and respond correctly without having to have full support of the HTTP Service.

Cannot use DELETE OR PUT (400 BadRequest)

I'm wondering why I recieve following result when trying DELETE request on some enttiy:

<HTML>
    <HEAD>
        <TITLE>400 Bad Request</TITLE>
    </HEAD>
    <BODY BGCOLOR=#FFFFFF>
        <H1>400 Bad Request</H1>
    Your browser sent a request that this server could not understand.


    </BODY>
</HTML>

I am trying to use the api from within angularJs controller as well as Postman - same result.

My request is following:

 var req = {
             method: 'DELETE',
             url: 'http://some.application.com/api/api.php/registration/' + $scope.registrationToBeDeleted,
              data: {
               },
              headers: {
                      'Content-Type': 'application/json'
                  }
            };

            $http(req).success(function(data){
                $scope.checkTodayReservations();
            });

I've tried to remove headers and data elements - same result.

Could you please help?

Not found (entity)

I'm running mySQL and using phpMyAdmin. I've got a static IP and using SSL.
I've uploaded the api.php to my public_html folder, and I've changed the settings to connect to my database within the:
$api = new MySQL_CRUD_API(array(
'hostname'=>'localhost',
'username'=>'USER',
'password'=>'PASS',
'database'=>'USERDB',
'charset'=>'utf8'
));
$api->executeCommand();

But, I keep getting "Not found (entity)" everytime I try to view the http://site/api.php

Any help would be greatly appreciated.
Thanks in advance ~ iJenn

Question On table_authorizer

Hello,
Can you help me I have many tables in my database and only want one to be granted access to GET & PUT. Cannot seem to figure it out. Very nice Restful API very very fast. Thank You Steve

Getting response from server

I am having issues validating a response from API.
I am passing the values from my login form to the url filter[], the server responds with a 200 OK response and returns the json data for that login. However I don't need the json data just a "success" or "error" response. Any help would be amazing. Thank you in advance for any feedback.

$(document).ready(function() {
    $("#login-button").click(function() {
        log_email = $("#login_email").val();
        log_password = $("#login_password").val();
        $.ajax({
            type: "GET",
            url: "http://www.website.com/api.php/users?",
            crossDomain: true,
            data: "filter[]=email,eq,email=" + log_email + "&filter[]=password,eq,password=" + log_password,
            dataType: 'json',
            success: function(data) {
                if (data == "null") {
                    console.log("Email and Password DIDN'T match");
                    $( "#invalid-login" ).popup( "open" );
                } 
                else if (data == "true") {
                    console.log("it's a !!MATCH!!");
                    window.location = "content.html";
                }
            }
        });
        return false;
    });
});

Composite primary workaround?

Hi Mevdshee,

I'm aware of the limitation of the composite primary (you mentioned them in readme.md).

Is there any workaround to put (update) record with composite primary?

Support column name containing space character

Hi Mevdschee,

First of all, thanks for this awesome API.

How to handle column name with two or more words?

...api.php?columns=First Name

i'm using PHP5.2, MSSQL2012.

Not found with nginx, but apache OK

Hello! I try to use api with nginx 1.6.2 with PHP 5.6.14. But default configuration of nginx+php don't like links like api.php/users (nginx can not find file users). With apache all work fine. Maybe I can use another url for access to table "users"? Like this api.php?table=users. Or maybe I can do some changes in api.php to solve this problem.
PS
I tried many configuration for nginx, but they can not solve my problem.

Issue with id and foreign key

Cannot retrieve associated item of a user based on this sample request
http://mean-experience.com/api.php/users?include=items,pictures&filter=id,eq,1&transform=1

You can find all users here: http://mean-experience.com/api.php/users
all items here: http://mean-experience.com/api.php/items

Foreign keys has been set on users(id) and items(user_id)

Thanks for your assistance,

For your convenience, here is my foreign key declaration:
ALTER TABLEitems ADD CONSTRAINTitems_ibfk_1FOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE ON UPDATE NO ACTION;

How can I see relationships?

I don't really understand what it says in the documentation, because that classes are not there anymore .

So I have this structure

Person
-id (PK)
-name
-classId (FK)

Class
-id (PK)
- name

And Person.classId is in relation ship with class.id.

But in my json output I only see classId, and can't see class object.

I don't have almost no php knowledge.

Thanks.

Support for sqlite

Would it be possible to not use a db at all and instead connect to a sqlite file?

Way to execute a raw query

Is there any way to execute a query such as select distinct city ...

A addition to your examples.

I had a similar situation to issue #30 when querying time stamps. The space between the date and time was breaking my URI. I wrote this function to correct the problem. Perhaps it could be added to the extras directory.

`function buildURI($endPoint, $resources, $filters, $satisfy='all') {
/***
*** $resources = array('res1', 'res2', 'res3' ...)
*** $filters = array (array('Resource'=>'resName', 'Filter'=>'filter', 'Value'=>'val'))
***/

$baseURI = 'https://path/to/api.php';

$query = $baseURI . preg_replace('/(\s)/','%20',$endPoint);

if (!empty($resources)) {
for($i=0;$i<count($resources);$i++){
$query .= ($i>0?',':'?columns=') . $resources[$i];
}
}
if (!empty($filters[0])) {
for($i=0;$i<count($filters);$i++){
$query .= ($i>0?'&':(empty($resources)?'?':'&')) . (count($filters)>1?'filter[]=':'filter=') . $filters[$i]['Resource'] . ',' . $filters[$i]['Filter'] . ',' . preg_replace('/(\s)/','%20',$filters[$i]['Value']) . ($i===count($filters)-1?'&satisfy='.$satisfy:'');
}
}
return $query;
}`

Running againts CORS with new api

Hi there
Developing an Angular 2 App from localhost. I had no CORS with my former api. My server allows calls from other domains.

Implementing the php-crud-api, I get a "Origin http://localhost:3000 is not allowed by Access-Control-Allow-Origin." warning.

Any ideas? Thank you!
Fred

Support for CRUD on views

404 when using guid as id for update

Not found (1pk)
0

execute a stored procedure

Hi,

First thanks for this useful api. I have 2 requests if possible:

1- Sqlite connectivity, I think this was requested by one of the users
2- Is it possible to execute a stored procedure on the database and get the result in json?

I understand that your api can do simple crud operation like selecting and filtering records, but in my queries am using sql functions like sum(), count(), etc. The aim of my second request is to have all this logic stored into a procedure on server side and use your REST api to call and retrieve the results.

Regards;
Alexis

Posted in error.

This was posted in the wrong place and should be deleted. I apologize.

Dont know how to update database with Ajax

my code

jQuery('#update').click(function(){
    jQuery.ajax({
      type: 'PUT',
      url: 'http://localhost/api/api.php/users/2',
      dataType: "json",
      data:{'username':'abcxyz'},
      success: function(data){
        alert(data);
      }
    });

  });

i dont understand exactly this request is mean? what mean of the number '2' in url, what is the data will be updating after this request.

and i just run this request, it returned zero.
Please help me !

Correct data type

Is there a way to get the correct data type?
If the data type in mysql is INT(11), the type in php and then in json is always string.
I found a way with PDO to get the correct type in php by setting PDO::ATTR_EMULATE_PREPARES => false

Should I try to Implement support for PDO? Or is there a simpler way to achive the correct types?

Api does not detect the relationship?

Thanks for this great API code! Just can't figure out how to get related data from a many to one relation. It seems the api code does not detect the relationship.

When I call my api, I expect one document to return only one group (now it returns all groups)

How can I do this?
Thanks!

PATH_INFO vs ORIG_PATH_INFO

I've been using your great library developing on my test server with everything working perfect for a few weeks now. Thanks for the great library!

Today, I uploded my code to a production environment (1and1 hosted server) and the library stopped working. After about an hour of digging, I found the 1and1 server was setting $_SERVER['ORIG_PATH_INFO'] and not $_SERVER['PATH_INFO'].

In my brief research, I guess some server setups do this: http://stackoverflow.com/questions/5629683/serverpath-info-and-serverorig-path-info-in-php

In the constructor (function __construct) of your REST_CRUD_API class, I found this line of code:

if (!$request) {
    $request = isset($_SERVER['PATH_INFO'])?$_SERVER['PATH_INFO']:'';
}

I changed my copy of your library to be a bit more generic and handle both PATH_INFO and ORIG_PATH_INFO cases:

if (!$request) {
    $request = !empty($_SERVER['PATH_INFO']) ? $_SERVER['PATH_INFO'] : (!empty($_SERVER['ORIG_PATH_INFO']) ? $_SERVER['ORIG_PATH_INFO'] : '');
}

FYI... In case you want to update your master copy.

Thanks again for the library!

Permissions to read/write data

Hey,

I saw you have implemented a permission system to read and write data, mentioned here https://github.com/mevdschee/php-crud-api#permissions

Do you have some working examples for that?
My goal ist to restrict edits and deletions of tables/fields for specified users (not mysql users).
I have a webapplication and without login, i want to permit users to change data via API (if they get access to it).

function php_crud_api_transform in api.php vs php_crud_api_transform.php

Experiencing the following Notices & Warnings on running the** client.php** under the examples folder

Notice:

Undefined index: relations in \path to php_crud_api folder\lib\php_crud_api_transform.php on line 11
Warning:

Invalid argument supplied for foreach() in \path to php_crud_api folder\lib\php_crud_api_transform.php on line 11

On detailed study, It is observed that

Under lib folder, the php_crud_api_transform.php requires the following
at line 11
** if (isset($reltable['relations'])) {**
with the corresponding
**}**
at line 16

to be in match with

function php_crud_api_transform

in api.php at line 1252 and line 1257 respectively

Please confirm / advise.

Best Regards
Krishna
KALViCK Technologies

Feature request: optional optimistic locking using row versioning

To avoid last write wins behaviour.

SQLite EXCEPTION: SyntaxError: JSON.parse

When I run the api with sqlite, I get:
EXCEPTION: SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

Update JSON Put returns 0

Hi Maurits, thanks for the great code. I do however have an issue. I have no problem using the script with post create, list, list transform etc.

However when I attempt to do a PUT update with or without JSON I am not successful. Initially i was getting a 403 permission denied then realized Apache was not allowing PUT requests. I added an override in an .htaccess file and now I get a 200 with a 0 for the response. When I analyze the object it is properly encoded as a json object, and if I use a browser based REST tool, I also return the same result.

Here is my object and ajax request:

var DataToSend = new Object();
DataToSend = {  
    id : '115',
    year : '2009',
    year_end : '2014',
    make : 'Yamaha',
    model : 'YZF-R1',
    title : '2009-2014 Yamaha YZF-R1'
};

$.ajax({
    type: 'PUT',
    url: 'http://localhost/s/themes/Carbon/api.php/motorcycles/2',
    dataType: "json",
    data: JSON.stringify(DataToSend),
    success: function(data){
        alert(data);
    }
});

Any idea what the problem could be?

Thank you in advance for your reply.

Arthur

crud for composite primary key table

How can i do CRUD operations in composite primary key table?
thanks in advance.

2 steps relations?

I am trying to attach relation data which works great except when it is "relations relation". In the example it should be possible to use mysql_crud_api_tranform() to achieve this.

I want to achieve this:

{ "posts": [ { "id": "1", "post_tags": [ { "id": "1", "post_id": "1", "tag_id": "1", "tags": [ { "id": "1", "name": "funny" } ] }, { "id": "2", "post_id": "1", "tag_id": "2", "tags": [ { "id": "2", "name": "important" } ] } ], "comments": [ { "id": "1", "post_id": "1", "message": "great" }, { "id": "2", "post_id": "1", "message": "fantastic" } ], "user_id": "1", "category_id": "1", "categories": [ { "id": "1", "name": "anouncement" } ], "content": "blog started" } ] }

But the result is :

{ "posts": [ { "id": "1", "post_tags": [ { "id": "1", "post_id": "1", "tag_id": "1", }, { "id": "2", "post_id": "1", "tag_id": "2", "tags": [ { "id": "2", "name": "important" } ] } ], "comments": [ { "id": "1", "post_id": "1", "message": "great" }, { "id": "2", "post_id": "1", "message": "fantastic" } ], "user_id": "1", "category_id": "1", "categories": [ { "id": "1", "name": "anouncement" } ], "content": "blog started" } ] "tags": [ { "id": "1", "name": "funny" } ] }

Do you know why or how i can fix this? If anyone is achieving the expected result, please provide an example of how you use mysql_crud_api_tranform(). Thanks for a great api!

Feature Request - access next/previous object values on a single returned object

I've previously switched to using this php-crud-api over WordPress - is it possible to do currently or would it be a feature request for the following:

If you are on a blog post and you get the single returned object (as expected!) - could you access the next / previous object? Or a value from the next / previous object (if one is available of-course!)?

New comand Limit

First of all nice api, congratulations to the coder. One question: Can be possible add the SQL command "LIMIT" to restrict the response?

Example: Last 10 products added in my shopping car

Feature for Request Limit?

Hello, I'm glad I could find such an easy tool to start an own rest api. I've just one question, before I will test it in my production: Is it possible to support a request limit (like Facebook or Twitter have)? Let me say 100 requests per day by one ip address. If there are more, the output will give an error. Beneath that I don't need any Authentication, just an ip limit to prevent spam and some bots.

Feature: add mysql functions to columns

For example.

?column=INET_NTOA(ip_addr)

and same to filter

?filter[]=INET_NTON(ip_addr),eq,'1.1.1.1'

because I have a database which stores IP addresses as long number

Thank you

Make use of schema

Hi Maurits,

We have a database (MSSQL) that has several different schema's. Is there a way to use your api.php on this database? For what I see now is that the code does not make use of a schema variable/parameter. So only the (few) tables in the dbo schema are accessible. Do you consider adding schema functionality?

Regards,

Hugo Voerman

Batch Insert

Hi,
Question is there a way to have this API code read a JSON Post that has more than level.
If I POST with just the first set all works. If I try it with 2 it errors out. I also tried to put [] around the JSON and changed the php code to not look for { but [ instead and set json_decode to true but I am stuck. Any insight would be appreciated. Thank You
Eample JSON : {
"location": "Heart Place",
"address": "4375 Booth Calloway",
"address2": "Suite 4000",
"city": "N Richland Hills",
"state": "TX",
"zip": "76180",
"BTN": "8175902593",
"customerreference": null,
"apikey": "12345"},
{
"location": "Atlanta Office",
"address": "3800 Arco Corporate Drive",
"address2": "Suite 310",
"city": "Atlanta",
"state": "GA",
"zip": "28226",
"BTN": "7043237471",
"customerreference": "12345",
"apikey": "12345"
}

Not supporting fields of type 'Text'

When I create a field in my table with the type TEXT (from phpmyadmin) the api will return something like

{
id: "660",
imageFull: "http://s15.postimg.org/ddbe3s9q3/images_154.jpg",
imageMedium: null,
category: "Fara",
date: "1420423665425",
details: "TW9yIDopKQ=="
},

Here the details field is as type 'TEXT', if i change it back to varchar(100) everything will be ok
{
id: "660",
imageFull: "http://s15.postimg.org/ddbe3s9q3/images_154.jpg",
imageMedium: null,
category: "Fara",
date: "1420423665425",
details: "Mor :))"
},

Anyways, the filtering seems to work with fields with the type 'TEXT'.

Call to undefined function sqlsrv_connect()

Hi,

This looks exactly what I need and have spent days looking for! Alas, I was greeted with;

Fatal error: Call to undefined function sqlsrv_connect() in /home/....../api.php on line 300

I followed the setup instructions and poked around. Not sure if I'm doing something wrong?

MySQL on Apache (Cpanel), InnoDB. I see that the sqlsrv_connect is a php parameter - perhaps not available in my build?

Please advise.
:)

PUT, DELETE, POST not working for some reason.

Hi thanks for writing wounder full API. this is super work.

I am facing issues with PUT, DELETE, POST none of the method works. in my case.

GET works well.

Would more response codes work?

When submitting new resources to tables with a non-auto-increment primary key the API succeeds silently and returns NULL if the key is duplicate. Would it make sense to add 2 more exit functions? One would be 201 with a location header pointing to the primary key and the other would be 409 with a header entry indicating that the sql query threw a duplicate key error.

Not found error

I've set these lines to my settings:
$api = new MySQL_CRUD_API(array(
'hostname'=>'localhost',
'username'=>'xxx',
'password'=>'xxx',
'database'=>'xxx'
));
However I get a 'not found (entity)' error.
What am I doing wrong?

What would be a good way to add custom validation of query params?

Filtering on tables with relationships

Is it possible to apply multiple filters when tables are in a relationship?

For example:

http://localhost/api.php/posts,categories?filter[]=post_name,cs,Internet&&filter[]=categories.cat_name,eq,Internet

Can't use time as filter

I try to use TIMESTAMP as filter because I need time sampled data ( 1 day, 2 days ....) but without any success. Is there any way to use DATE(NOW()) or INTERVAL in filter ?

Filter

Hi,

can i filter something like "(col1,eq,2 or col2,eq,2) and col3,eq,7"?
My current workaround is creating a view with a column that combines col1 and col2.

Saving one-to-many child entities inside parent json post

I have a parent with 1-to-many child records, is there an easy way to post (create) within a single request to the API?

Example: (editResultsId and snapshotId reference children relationships)

{
   "submittedById":"3",
   "submissionMonth":"2",
   "submissionYear":"1989",
   "carrierId":"17",
   "carrierStatus":"warning",
   "recentAccomplishments":"Adipisci accusamus sit, commodi cupiditate illum, lorem velit labore do.",
   "dqIssues":"Culpa delectus, quo quia commodo natus officia dolorum aperiam rerum sint voluptatem et consequatur, id.",
   "editResultsId":[
      {
         "fileSystemId":"3",
         "valuationDate":"2006-05-03",
         "unitsMeasuredId":"3",
         "result":"Voluptatem Voluptatem Earum incididunt sint dolore non laborum",
         "threshold":"Illum tempore deserunt cupidatat enim ab corrupti magna quia voluptatem tempor quia voluptatum qui accusamus",
         "notes":"Cillum aspernatur sint, enim eveniet, voluptate voluptas eos, dolore ducimus, asperiores adipisci."
      }
   ],
   "snapshotId":[
      {
         "sourceSystem":"4",
         "frequency":"Bi-Weekly",
         "extractName":"234213",
         "extractDueDate":"42134125"
      }
   ],
   "dataContacts":"Libero culpa dolore odit et repudiandae sequi",
   "auditEstablished":true,
   "relationshipEstablished":true,
   "dateSubmitted":"2015-11-16 21:53:09"
}

Support inserting mutiple records (json array)

Hi,
I'm trying to insert several rows to table by passing a json array with some elements but I get a 404 error:

[{
    "id" : "1",
    "name" : "Internet"
},{
    "id" : "2",
    "name" : "Internet 2"
}]

When I put single item into json everything works fine. I know that's not a bug but this would be a nice feature to that api - do you have plans to add this? Some kind of bulk insert.

Best regards,
macaron