SQL to JSON is a small PHP script allowing you to make GET and POST request to a database and retreive the result as a JSON object
- Simple and fast setup
- Only 2 files required ( api.php and models.json )
- Subqueries (Ex : user_id will be converted to an user object) Explanations here
- No complex query (Join etc...)
- No Put, Delete method at the moment (you must use the Post method)
- For Subqueries, you must use my database model
##Configuration
All required database infos must be defined at the beginning of api.php
Here is an example :
// BEGIN DB CONFIGURATION \\
$PARAM_host='localhost';
$PARAM_port='3306';
$PARAM_db_name='yourdbname';
$PARAM_user='username';
$PARAM_pass='password';
// END DB CONFIGURATION \\
When a "table" URL parameter is called : the script retrieves the table (in the database) to be called and the rows to display.
You probably don't want to display the hashed password of an user for example, that's why I advise to never use the "*" selector
An example of an expected output :
{
"users":
{
"table" : "users",
"rows" :
[
"id","nickname"
]
},
"me":
{
"table" : "users",
"rows" :
[
"id","nickname","last_login_date","signup_date","email","avatar_url","banner_url"
]
}
}
The default .htaccess is configured to pass parameters like this :
/api/tablename/¶meter=value
Without it, it will be :
/api.php?table=tablename¶meter=value
Do not forget to take a look at it!
Retreive users (default limit = 10)
/api/users
Retreive all users who are in the group 1
/api/users/&where=group_id=1
Also, you can use multiple conditions with the separator :
/api/users/&where=group_id=1:nickname=CYRIAQU3
By default, the limit is set to 10, but you can specify a custom value
Retreive the two first users who are in the group 1
/api/users/&where=group_id=1&limit=2
Retreive the two first users who are in the group 1, ordered by their name
/api/users/&where=group_id=1&limit=2&order=name
When the script receive a POST query, it simply includes the file located in the sub-repository /post/{tableparam} You can do whatever you want with it
Post query :
/api/users
File called :
/api/post/users.php
##Others
The script auto convert some values to object if they follow this following model :
- The table name must be plural (ex : users, movies)
- Every rows must have an id with the row name "id" (not user->user_id but user->id)
- When you referenced another object in your rows, the table name must be singular following by _id ex : table_id
An example :
{
"articles":
{
"table" : "articles",
"rows" :
[
"id","user_id","name","etc"
]
}
}
Look at the user_id row. When you call an article, the script will check if the table users actually exist, in this case, it will transform user_id into user and you will get a result like it :
Call
/api/articles/1
Article structure in Database
{
"id": "1",
"name": "Insert a clickbait article name here",
"user_id" : "1"
}
Result
{
"success" : true,
"count" : 1,
"articles": [
{
"id": "1",
"name": "Insert a clickbait article name here",
"user" :
{
"id" : "1",
"nickname" : "CYRIAQU3",
"etc..." : "etc..."
}
}]
}