MySQL-JSONify

KO | EN

gitlite — search

#hacktoberfest #ai #typescript #android #machine-learning #linux #windows #awesome #cli #vue #pytorch #security #mcp #openai

(★ 13)

A RESTful API made with PHP to convert MySQL to JSON.

MySQL-JSONify Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# MySQL-JSONify

A RESTful API made with PHP to convert MySQL to JSON.
-------------

Table of contents

- Introduction
- Setup
- Paths/table
- Parameters
- Examples (GET, POST, PUT, DELETE)

Introduction
-------------
This is a RESTful API created with PHP.  You connect your MySQL database which allows you to CRUD (GET, POST, PUT, DELETE).

The responses are in JSON. You can use parameters to create SQL-statmenents (safely) before fetching data.

Setup
-------------

In the `details.php` file you can type in your database information, so the Connect-class knows where to fetch the data.

```
"database" =>
    [
         "host" => "XXX",
         "dbname" => "XXX",
         "username" => "XXX",
         "password" => "XXX"
    ],

```

Paths
-------------

You can connect your MySQL-tables to the URL path, by editing the `details.php` file. To add more simply name your path and then the table.

```
"paths" =>
    [
         "path" => "MYSQL-TABLE-NAME",
         "users" => "users",
         "products" => "Shop_Products"
    ]
```

If we would request data from `api.php/products`  then we would receive data from the table "Shop_Products". So you choose the path and which table to connect it to.

Parameters
-------------
The data is fetched with a SQL-statement. Therefore you can with parameters decide fairly good how you would like it to look like.

Available params
```
["select", "order", "id",  "limit", "offset"]
```

So let's go through how the SQL-statements can look like,

Basic
```
 api.php/products == SELECT * FROM Shop_Products

api.php/products?select=name == SELECT name FROM Shop_Products

api.php/products?order=asc == SELECT * FROM Shop_Products ORDER BY asc

api.php/users?id=1 == SELECT * FROM users WHERE id = :id
```

Limit
```

api.php/users?limit=5 == SELECT * FROM users LIMIT 5

api.php/users?offset=3 == SELECT * FROM users LIMIT 18446744073709551610 OFFSET 3

api.php/users?limit=2&offset=3 == SELECT * FROM users LIMIT 2 OFFSET 3

```
---
Examples (GET, POST, PUT, DELETE)
===
For the following examples we will use the following SQL-setup code.

```

CREATE TABLE `users`
(
  `id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `username` VARCHAR(120) UNIQUE,
  `password` VARCHAR(120),
  `authority` VARCHAR(120)

) ENGINE INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

INSERT INTO `users` (`username`, `password`, `authority`) VALUES
    ("Nicklas766", "pass1", "admin"),
    ("Rasmus Lerdorf", "pass2", "admin"),
    ("Jessica", "pass3", "admin"),
    ("Steve", "pass4", "user"),
    ("Adam", "pass5", "user");

```

GET
-------------

`api.php/users`
```
[
    {
        "id": "1",
        "username": "Nicklas766",
        "password": "pass1",
        "authority": "admin"
    },
    {
        "id": "2",
        "username": "Rasmus Lerdorf",
        "password": "pass2",
        "authority": "admin"
    },
...

```
`api.php/users?order=desc`

```
[
    {
        "id": "5",
        "username": "Adam",
        "password": "pass5",
        "authority": "user"
    },
    {
        "id": "4",
        "username": "Steve",
        "password": "pass4",
        "authority": "user"
    },
...
```

`api.php/users?select=username`
```
[
    {
        "username": "Adam"
    },
    {
        "username": "Jessica"
    },
    {
        "username": "Nicklas766"
    },
    {
        "username": "Rasmus Lerdorf"
    },
    {
        "username": "Steve"
    }
]
```
POST
-------------
You can send in the column names as parameters name. If the value is empty then it will be null.

So with  the following parameters,
```
username = Anna
password = pass6
authority = user

```

`POST api.php/users`

```
{
    "id": "6",
    "username": "Anna",
    "password": "pass6",
    "authority": "user"
}
```

PUT
-------------
You can send in the column names as parameters, in order to update the rows.
`api.php/users?username=Amanda&password=pass3UPDATED&id=3&authority=user`

Before
```
   {
        "id": "3",
        "username": "Jessica",
        "password": "pass3",
        "authority": "admin"
    }
```
After (this is also the response)
```
[
    {
        "id": "3",
        "username": "Amanda",
        "password": "pass3UPDATED",
        "authority": "user"
    }
]
```

DELETE
-------------
You can delete by sending in the ID, response will return the entire table. So in this case user "Nicklas766" should be removed.
`api.php/users?id=1`

```
[
    {
        "id": "2",
        "username": "Rasmus Lerdorf",
        "password": "pass2",
        "authority": "admin"
    },
    {
        "id": "3",
        "username": "Amanda",
        "password": "pass3UPDATED",
        "authority": "user"
    },
    {
        "id": "4",
        "username": "Steve",
        "password": "pass4",
        "authority": "user"
    },
    {
        "id": "5",
        "username": "Adam",
        "password": "pass5",
        "authority": "user"
    },
    {
        "id": "6",
        "username": "Anna",
        "password": "pass6",
        "authority": "user"
    }
]
```

MySQL-JSONify

MySQL-JSONify Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

MySQL-JSONify

MySQL-JSONify Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README