mysqlapi

PHP API for MySql CRUD (Create, Read, Update, Delete) features.

How to connect to your MySql database through REST API.

Pre requists

You simply need a functionnal PHP server 5.5 minimum.

The API uses msqli library to connect to MySql.

All api returns are json objects. Therefors the database must be utf8 encoded only.

Don't forget to set your .htaccess file so http://serveur:port/apiurl/database/xxx works properly (for instance):

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ api.php?rquest=$1 [QSA,NC,L]

Database configuration

Create a JSON file containing your configuration (see example demonstration.json). The name of that file will be the name of the database you'll be referring to in your http calls.

{
    "host": "server:port",
    "user": "user",
    "password": "password",
    "database": "databsename"
}

The url of your API will then be

http://serveur:port/apiurl/jsonfilename/xxx

Feature

Return object

Any API will return the same json object. Results array contains rows field by field according to the sql pharse.

{
    "sql": "sql phrase sent",
    "returnCode": "return code (http standards)",
    "insertedId": "last inserted id when INSERT is used",
    "resultCount": "count of the result",
    "sqlError": "sql error if any",
    "affectedRows": "number of changed rows when UPDATE is used",
    "results": [
        {
            "fieldid": "",
            "field1": "",
            "field2": "",
            "__idFieldName": "name of the id (PRIMARY key) field",
            "__idValue": "id of the record (value of __idFieldName)"
        }
    ]
 }

sql

• description: executes a sql expression and returns its results.
• url: /sql
• mehtod: POST
• body:

{
    "sql": "Any valid sql query will be executed"
}

• return: json object

fields

• description: returns the fields and properties of a table tablename
• url: /fields/tablename
• method: GET
• return: json object containg the description of the field of the table according to "SHOW FIELDS FROM tablename"

count

• description: returns the number of rows a table tablename.
• url: /count/tablename
• method: GET, POST
• body:

{
    "__where": "Any valid WHERE sql expression"
}

• return: json object containg the description of the field of the table according to "SELECT count(*) FROM tablename WHERE __where"

read

• description: returns the rows of a table tablename.
• url: /read/tablename?limit=10&offset=3 (records from 3 to 13), only limit and offset parameter are available in GET method
• method: GET, POST
• body:

{
    "__select": "SELECT expression",
    "__where": "Any valid WHERE sql expression",
    "__orderby": "ORDER BY expression",
    "__groupby": "GROUP BY expression",
    "__limit": "LIMIT expression",
    "__offset": "OFFSET exporession"
}

• return: json object containg the description of the field of the table according to "SELECT __select FROM tablename WHERE __where GROUP BY __groupby ORDER BY __orderby"

insert

• description: inserts rows into a table tablename.
• url: /insert/tablename
• method: POST
• body:

[
    {
        "fieldid": "",
        "field1": "",
        "field2": ""
    },
    {
        "fieldid": "",
        "field1": "",
        "field2": ""
    }
]

• return: json object containg the result of "UPDATE" query.

update

• description: updates rows of a table tablename according to a condition.
• url: /update/tablename
• method: POST
• body:

[
    {
        "fieldid": "new value",
        "field1": "new value",
        "field2": "new value",
        "__where": ""
    },
    {
        "fieldid": "new value",
        "field1": "new value",
        "field2": "new value",
        "__where": ""
    }
]

• return: json object containg the result of "UPDATE" query according to __where condition.

delete

• description: deletes some rows of a table tablename according to a condition.
• url: /delete/tablename
• method: POST
• body:

[
    {
        "__where": ""
    },
    {
        "__where": ""
    }
]

• return: json object containg the result of "DELETE" query according to __where condition.

fresh

• description: returns an empty row of a table tablename.
• url: /fresh/tablename
• method: POST
• body:

[
    {
        "__where": ""
    },
    {
        "__where": ""
    }
]

• return: json object containg the result of "DELETE" query according to __where condition.