PHP TableCrud

json_table crud.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require 'db.php';

// Check if the request body is JSON
$contentType = $_SERVER['CONTENT_TYPE'] ?? '';
if (strpos($contentType, 'application/json') !== false) {
    $input = json_decode(file_get_contents('php://input'), true);
} else {
    $input = $_POST;
}

$table = $input['table'] ?? '';
$action = $input['action'] ?? '';

if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
    http_response_code(400);
    echo json_encode(["error" => "Invalid table name."]);
    exit;
}

if ($action === 'get') {
    $id = $input['id'];
    $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ?");
    $stmt->execute([$id]);
    echo json_encode($stmt->fetch(PDO::FETCH_ASSOC));
    exit;
}

if ($action === 'create' || $action === 'update') {
    $columns = array_keys($input);
    unset($columns[array_search('table', $columns)]);
    unset($columns[array_search('action', $columns)]);
    unset($columns[array_search('id', $columns)]);

    $values = [];
    foreach ($columns as $col) {
        $values[] = $input[$col];
    }

    if ($action === 'create') {
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        $sql = "INSERT INTO `$table` (" . implode(', ', $columns) . ") VALUES ($placeholders)";
    } else {
        $setClause = implode(', ', array_map(fn($col) => "$col = ?", $columns));
        $sql = "UPDATE `$table` SET $setClause WHERE id = ?";
        $values[] = $input['id'];
    }

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
        if ($action === 'create') {
            $id = $pdo->lastInsertId();
            echo json_encode(["success" => true, "id" => $id, "message" => "Record created successfully."]);
        } else {
            echo json_encode(["success" => true, "message" => "Record updated successfully."]);
        }
    } catch (PDOException $e) {
        http_response_code(500);
        echo json_encode([
            "error" => "Database error: " . $e->getMessage(),
            "sql" => $sql,
            "values" => $values
        ]);
    }
    exit;
}

if ($action === 'delete') {
    $id = $input['id'];
    $stmt = $pdo->prepare("DELETE FROM `$table` WHERE id = ?");
    $stmt->execute([$id]);
    echo json_encode(["success" => true, "message" => "Record deleted successfully."]);
    exit;
}
?>

Yes, you can send a JSON request to table_crud.php if you adjust your script to handle JSON input. By default, PHP reads POST data as form-encoded (application/x-www-form-urlencoded), but if you send Content-Type: application/json, you’ll need to parse the JSON body.

1. Update table_crud.php to Handle JSON Requests

Modify the script to support JSON input:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require 'db.php';

// Check if the request body is JSON
$contentType = $_SERVER['CONTENT_TYPE'] ?? '';
if (strpos($contentType, 'application/json') !== false) {
    $input = json_decode(file_get_contents('php://input'), true);
} else {
    $input = $_POST;
}

$table = $input['table'] ?? '';
$action = $input['action'] ?? '';

if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
    http_response_code(400);
    echo json_encode(["error" => "Invalid table name."]);
    exit;
}

if ($action === 'get') {
    $id = $input['id'];
    $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = ?");
    $stmt->execute([$id]);
    echo json_encode($stmt->fetch(PDO::FETCH_ASSOC));
    exit;
}

if ($action === 'create' || $action === 'update') {
    $columns = array_keys($input);
    unset($columns[array_search('table', $columns)]);
    unset($columns[array_search('action', $columns)]);
    unset($columns[array_search('id', $columns)]);

    $values = [];
    foreach ($columns as $col) {
        $values[] = $input[$col];
    }

    if ($action === 'create') {
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));
        $sql = "INSERT INTO `$table` (" . implode(', ', $columns) . ") VALUES ($placeholders)";
    } else {
        $setClause = implode(', ', array_map(fn($col) => "$col = ?", $columns));
        $sql = "UPDATE `$table` SET $setClause WHERE id = ?";
        $values[] = $input['id'];
    }

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
        if ($action === 'create') {
            $id = $pdo->lastInsertId();
            echo json_encode(["success" => true, "id" => $id, "message" => "Record created successfully."]);
        } else {
            echo json_encode(["success" => true, "message" => "Record updated successfully."]);
        }
    } catch (PDOException $e) {
        http_response_code(500);
        echo json_encode([
            "error" => "Database error: " . $e->getMessage(),
            "sql" => $sql,
            "values" => $values
        ]);
    }
    exit;
}

if ($action === 'delete') {
    $id = $input['id'];
    $stmt = $pdo->prepare("DELETE FROM `$table` WHERE id = ?");
    $stmt->execute([$id]);
    echo json_encode(["success" => true, "message" => "Record deleted successfully."]);
    exit;
}
?>

Sending JSON Requests Using Postman

Now, you can send requests in JSON format.

Create Record

• Method: POST
• URL: https://yourdomain.com/path/to/table_crud.php
• Headers:

  Content-Type: application/json

• Body (raw JSON):

  {
    "action": "create",
    "table": "your_table_name",
    "name": "Example Name",
    "description": "Example Description",
    "created_at": "2024-11-15 12:34:56"
  }

Update Record

• Body (raw JSON):

  {
    "action": "update",
    "table": "your_table_name",
    "id": 123,
    "name": "Updated Name",
    "description": "Updated Description",
    "created_at": "2024-11-15 14:45:00"
  }

Get Record

• Body (raw JSON):

  {
    "action": "get",
    "table": "your_table_name",
    "id": 123
  }

Delete Record

• Body (raw JSON):

  {
    "action": "delete",
    "table": "your_table_name",
    "id": 123
  }

Response Example

{
  "success": true,
  "message": "Record updated successfully."
}

Troubleshooting Tips

• Ensure Content-Type is set to application/json in Postman.
• Check the console logs in your PHP script for any debugging information (error_reporting is enabled).
• Verify the table and column names match the structure in your database.

This setup allows you to interact with table_crud.php using JSON, which is more flexible and common for modern APIs. Let me know if you need further assistance!