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

GET

POST

• 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/&parameter=value

Without it, it will be :

  /api.php?table=tablename&parameter=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..."
		}
	}]
}