PHP PDO and test

api.php

<?php
require 'db_config.php';

header('Content-Type: application/json');

// Basic Auth credentials
define('USERNAME', 'u');
define('PASSWORD', 'p');

// Function to check basic auth credentials
function authenticate() {
    if (!isset($_SERVER['PHP_AUTH_USER']) || !isset($_SERVER['PHP_AUTH_PW'])) {
        header('WWW-Authenticate: Basic realm="My Realm"');
        header('HTTP/1.0 401 Unauthorized');
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    } else {
        if ($_SERVER['PHP_AUTH_USER'] !== USERNAME || $_SERVER['PHP_AUTH_PW'] !== PASSWORD) {
            header('WWW-Authenticate: Basic realm="My Realm"');
            header('HTTP/1.0 401 Unauthorized');
            echo json_encode(['error' => 'Unauthorized']);
            exit;
        }
    }
}

// Authenticate the user
authenticate();

$method = $_SERVER['REQUEST_METHOD'];
$table = $_GET['table'] ?? '';

if (empty($table)) {
    echo json_encode(['error' => 'Table parameter is required']);
    exit;
}

switch ($method) {
    case 'GET':
        handleGet($pdo, $table);
        break;
    case 'POST':
        $action = $_GET['action'] ?? '';
        if ($action === 'insert') {
            handleInsert($pdo, $table);
        } elseif ($action === 'update') {
            handleUpdate($pdo, $table);
        } elseif ($action === 'delete') {
            handleDelete($pdo, $table);
        } else {
            echo json_encode(['error' => 'Invalid action']);
        }
        break;
    default:
        echo json_encode(['error' => 'Unsupported request method']);
}

function handleGet($pdo, $table) {
    $limit = $_GET['limit'] ?? 10;
    $page = $_GET['page'] ?? 1;
    $search = $_GET['search'] ?? '';
    $offset = ($page - 1) * $limit;

    $query = "SELECT * FROM $table";
    $params = [];
    if ($search) {
        $query .= " WHERE title LIKE :search"; //OR description LIKE :search";
        $params['search'] = "%$search%";
    }
    $query .= " LIMIT :limit OFFSET :offset";
    
    $stmt = $pdo->prepare($query);
    $stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
    if ($search) {
        $stmt->bindValue(':search', $params['search'], PDO::PARAM_STR);
    }
    $stmt->execute();
    $results = $stmt->fetchAll();

    echo json_encode($results);
}

function handleInsert($pdo, $table) {
    $data = json_decode(file_get_contents('php://input'), true);
    $columns = array_keys($data);
    $placeholders = array_map(fn($col) => ":$col", $columns);
    
    $query = "INSERT INTO $table (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
    $stmt = $pdo->prepare($query);
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    
    if ($stmt->execute()) {
        echo json_encode(['id' => $pdo->lastInsertId()]);
    } else {
        echo json_encode(['error' => 'Failed to insert record']);
    }
}

function handleUpdate($pdo, $table) {
    $id = $_GET['id'] ?? '';
    if (empty($id)) {
        echo json_encode(['error' => 'ID parameter is required']);
        return;
    }

    $data = json_decode(file_get_contents('php://input'), true);
    $columns = array_keys($data);
    $placeholders = array_map(fn($col) => "$col = :$col", $columns);
    
    $query = "UPDATE $table SET " . implode(', ', $placeholders) . " WHERE id = :id";
    $stmt = $pdo->prepare($query);
    foreach ($data as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    
    if ($stmt->execute()) {
        echo json_encode(['status' => 'Record updated successfully']);
    } else {
        echo json_encode(['error' => 'Failed to update record']);
    }
}

function handleDelete($pdo, $table) {
    $id = $_GET['id'] ?? '';
    if (empty($id)) {
        echo json_encode(['error' => 'ID parameter is required']);
        return;
    }
    
    $query = "DELETE FROM $table WHERE id = :id";
    $stmt = $pdo->prepare($query);
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    
    if ($stmt->execute()) {
        echo json_encode(['status' => 'Record deleted successfully']);
    } else {
        echo json_encode(['error' => 'Failed to delete record']);
    }
}

db_config.php

<?php
$host = 'localhost';
$db = '44444';
$user = '4444';
$pass = '4444@12';
$charset = 'utf8mb4';


$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}

test.sh

#!/bin/bash

# Set the username and password for basic authentication
USERNAME="your_username"
PASSWORD="your_password"

# Base URL for the API
BASE_URL="https://zoomcarft.000webhostapp.com/proj-react/backend/api2.php"

# Function to perform a GET request
get_tasks() {
  curl -u $USERNAME:$PASSWORD -X GET "$BASE_URL?table=demo_tasks&limit=10&page=1&search=$1"
}

# Function to perform a POST request to insert data
insert_task() {
  curl -u $USERNAME:$PASSWORD -X POST "$BASE_URL?table=demo_tasks&action=insert" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "New Task",
    "description": "Description of the new task",
    "file_path": "/path/to/file"
  }'
}

# Function to perform a POST request to update data
update_task() {
  curl -u $USERNAME:$PASSWORD -X POST "$BASE_URL?table=demo_tasks&action=update&id=$1" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Updated Task Title",
    "description": "Updated description",
    "file_path": "/new/path/to/file"
  }'
}

# Function to delete the last task
delete_last_task() {
  # Get all tasks
  tasks=$(curl -u $USERNAME:$PASSWORD -X GET "$BASE_URL?table=demo_tasks")
  
  # Extract the ID of the last task
  last_id=$(echo "$tasks" | jq -r '.[-1].id')

  if [ -z "$last_id" ]; then
    echo "No tasks found or unable to retrieve the last task ID."
    return 1
  fi

  echo "Deleting task with ID: $last_id"

  # Delete the last task
  curl -u $USERNAME:$PASSWORD -X POST "$BASE_URL?table=demo_tasks&action=delete&id=$last_id"
}

# Function to perform a GET request to fetch all tasks
get_all_tasks() {
  curl -u $USERNAME:$PASSWORD -X GET "$BASE_URL?table=demo_tasks"
}


# Function to perform a POST request to delete data
delete_task() {
  curl -u $USERNAME:$PASSWORD -X POST "$BASE_URL?table=demo_tasks&action=delete&id=$1"
}

# Example usage
echo "Getting tasks with search term 'report'"
get_tasks "g"

echo "Inserting a new task"
insert_task

echo "Updating task with ID 1"
update_task 2

echo "Deleting task with ID 1"
delete_task 2


# Example usage
echo "Fetching all tasks"
get_all_tasks

echo "Deleting the last task"
delete_last_task