简易 PHP API 测试

简易 PHP API 测试

PHP代码

<?php
/*
======================================================================================
Author : James
Date   : 2020/3/21
Versions: Beta v2
HEPL   :
    HTTP POST :
    1.{ "select": "mock_data", "where": [{ "id": "1" },{ "username": "james" }], "limit": [1, 2] };
    2.{ "insert": "mock_data", "values": [{ "username": "james" }, { "email": "[e-mail]" }] };
    3.{ "update": "mock_data", "set": [{ "username": "james" }, { "email": "[e-mail]" }], "where": [{ "id": "1" }] };
    4.{ "delete": "mock_data", "where": [{ "id": "1" }] }
    5.{ "query":  "mock_data", "where": [{ "username": "james" }], "page": [1,10] };  page参数[当前页,页大小]
======================================================================================
*/

header("Content-type: application/json");
header("Content-type:text/html;charset=utf-8");
header("Access-Control-Allow-Origin:*");

//初始化参数
define("HOST", "localhost");
define("DBNAME", "test");
define("USER", "root");
define("PASSWORD", "");
define("ACCESSPERMISSION", json_encode(array('query', 'select', 'update', 'insert', 'delete')));
define("PAGESIZE", 10);


global $MySQL;

if (count($_POST) < 1) {
    render(null, array("Error" => "Not POST Param"));
}

$GLOBALS["MySQL"] = new PDO("mysql:host=" . HOST . ";dbname=" . DBNAME . ";charset=utf8", USER, PASSWORD, array(PDO::ATTR_PERSISTENT => true));
$SQL_TYPE = strtolower(array_keys($_POST)[0]);
if (in_array($SQL_TYPE, json_decode(ACCESSPERMISSION)) && !empty($_POST[$SQL_TYPE])) {
    if (!isTable($_POST[$SQL_TYPE])) {
        render(null, array("Error" => "Not Exist Table " . $_POST[$SQL_TYPE]));
    }
    $POST = $_POST;
    unset($POST[$SQL_TYPE]);
    call_user_func($SQL_TYPE, $_POST[$SQL_TYPE], $POST);
} else {
    render(null, array("Error" => "POST Param " . $SQL_TYPE));
}


//拼凑 WHERE 语句
function where($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = ' WHERE ';
    foreach ($SQL as $item) {
        foreach ($item as $key => $val) {
            $sql .= sprintf("%s='%s' and ", $key, $val);
        }
    }
    return substr($sql, 0, -4);
}

//拼凑 VALUES 语句
function values($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = '';
    $k = '';
    $v = '';
    foreach ($SQL as $item) {

        foreach ($item as $key => $val) {
            $k .= sprintf("%s,", $key);
            $v .= sprintf("'%s',", $val);
        }
    }
    $sql .= sprintf("(%s)VALUES(%s)", substr($k, 0, -1), substr($v, 0, -1));
    return $sql;
}

//拼凑 SET 语句
function set($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = ' SET ';
    foreach ($SQL as $item) {
        foreach ($item as $key => $val) {
            $sql .= sprintf("%s='%s',", $key, $val);
        }
    }
    return substr($sql, 0, -1);
}

//拼凑 ORDER BY 语句
function orderby($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = ' ORDER BY ';
    foreach ($SQL as $item) {
        foreach ($item as $key => $val) {
            $sql .= $key . " " . $val . ",";
        }
    }
    return substr($sql, 0, -1);
}

//拼凑 LIMIT 语句
function limit($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = ' LIMIT ';
    foreach ($SQL as $item) {
        $sql .= $item . ",";
    }
    return substr($sql, 0, -1);
}

//拼凑 PAGE 语句
function page($SQL)
{
    if (count($SQL) < 1) {
        return "";
    }
    $sql = ' LIMIT ';
    foreach ($SQL as $item) {
        $sql .= $item . ",";
    }
    return substr($sql, 0, -1);
}

//装配SQL的条件语句
function AssembleSQL($SQL)
{
    //die(var_dump($SQL));
    if (isset($SQL) && !empty($SQL)) {
        $sql = "";
        foreach ($SQL as $key => $value) {
            $sql .= call_user_func(strtolower($key), $value);
        }
        return $sql;
    }
}


//执行分页数据库
function query($TABLE, $SQL)
{
    $pageNumber = 1;
    $pageSize = PAGESIZE;
    $totalPage = 1;
    $totalRow = 1;
    $start = 0;

    if (!empty($SQL) && count($SQL['page']) > 0) {
        if (count($SQL['page']) == 1) {
            $pageNumber = $SQL['page'][0] < 1 ? 1 : $SQL['page'][0];
        }
        if (count($SQL['page']) == 2) {
            $pageNumber = $SQL['page'][0] < 1 ? 1 : $SQL['page'][0];
            $pageSize =  $SQL['page'][1] < 1 ? 10 : $SQL['page'][1];
        }
        $start = ($pageNumber - 1) * $pageSize;
    }
    $SQL['page'] = array($start, $pageSize);
    $sql = sprintf("SELECT * FROM %s %s", $TABLE, AssembleSQL($SQL));
    $sql_count = substr($sql, 0, strpos($sql, "LIMIT"));
    $sql_count = str_replace("*", "count(*)", $sql_count);
    $dbcount = $GLOBALS["MySQL"]->query($sql_count);
    $totalRow = $dbcount->fetchColumn();
    $db = $GLOBALS["MySQL"]->query($sql);
    $reslut = $db->fetchAll(PDO::FETCH_ASSOC);
    $totalPage = ceil($totalRow / $pageSize);
    $page = array("list" => $reslut, "pageNumber" => intval($pageNumber), "pageSize" => $pageSize, "totalPage" => $totalPage, "totalRow" => intval($totalRow));
    render($page);
}


//执行查询数据库
function select($TABLE, $SQL)
{
    $sql = sprintf("SELECT * FROM %s %s", $TABLE, AssembleSQL($SQL));
    $db = $GLOBALS["MySQL"]->query($sql);
    $reslut = $db->fetchAll(PDO::FETCH_ASSOC);
    render($reslut);
}

//执行插入数据库
function insert($TABLE, $SQL)
{
    $sql = sprintf("INSERT INTO %s %s", $TABLE, AssembleSQL($SQL));
    $reslut = $GLOBALS["MySQL"]->exec($sql);
    render($reslut);
}

//执行更新数据库
function update($TABLE, $SQL)
{
    $sql = sprintf("UPDATE %s %s", $TABLE, AssembleSQL($SQL));
    $reslut = $GLOBALS["MySQL"]->exec($sql);
    render($reslut);
}

//执行删除数据库
function delete($TABLE, $SQL)
{
    $sql = sprintf("DELETE FROM %s %s", $TABLE, AssembleSQL($SQL));
    $reslut = $GLOBALS["MySQL"]->exec($sql);
    render($reslut);
}

//视图
function render($reslut, $msg = null)
{
    $GLOBALS["MySQL"] = null;
    if (empty($msg)) {
        die(json_encode($reslut));
    } else {
        die(json_encode($msg));
    }
}

//判断表是否存在
function isTable($table)
{
    $result = $GLOBALS["MySQL"]->query("SHOW TABLES LIKE '" . $table . "'")->fetchAll(PDO::FETCH_GROUP);
    if ($result) {
        return true;
    } else {
        return false;
    }
}

HTTP代码

<!DOCTYPE html>
<html>

<head>
    <meta http-equiv="content-type" content="html/html; charset=utf-8">
    <link href="http://lib.sinaapp.com/js/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <script src="http://lib.sinaapp.com/js/jquery/2.2.4/jquery-2.2.4.min.js"></script>
    <script src="http://lib.sinaapp.com/js/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <title>Test</title>
</head>

<body>
    <h1> Test Ajax POST Request </h1>
    <hr>
    <button id="but1"> GET select </button>
    <hr>
    <button id="but2"> GET delete </button>
    <hr>
    <button id="but3"> GET update </button>
    <hr>
    <button id="but4"> GET insert </button>
    <hr>
    <button id="but5"> GET query </button>
    <span></span>

    <script>

        $(function () {

            $("#but5").on("click", function () {
                let URL = "http://127.0.0.1/index.php";
                let param = { "query": "mock_data", "page": [1] };
                $.post(URL, param, function (data) {
                    $("span").html(data);
                })
            })
            
            $("#but4").on("click", function () {
                let URL = "http://127.0.0.1/index.php";
                let param = { "insert": "mock_data", "values": [{ "username": "james" }, { "email": "[e-mail]" }] };
                $.post(URL, param, function (data) {
                    $("span").html(data);
                })
            })

            $("#but3").on("click", function () {
                let URL = "http://127.0.0.1/index.php";
                let param = { "update": "mock_data", "set": [{ "username": "james" }, { "email": "[e-mail]" }], "where": [{ "id": "1" }] };
                $.post(URL, param, function (data) {
                    $("span").html(data);
                })
            })

            $("#but2").on("click", function () {
                let URL = "http://127.0.0.1/index.php";
                let param = { "delete": "mock_data", "where": [{ "id": "1" }] };
                $.post(URL, param, function (data) {
                    $("span").html(data);
                })
            })

            $("#but1").on("click", function () {
                let URL = "http://127.0.0.1/index.php";
                let param = { "select": "mock_data", "where": [{ "id": "2" }], "limit": [0, 1] };
                $.post(URL, param, function (data) {
                    $("span").html(data);
                })
            })
        })
    </script>
</body>

</html>