The first step in designing a database is creating the database itself and defining its tables.

Step 1: Create the Database

CREATE DATABASE farming_database;
USE farming_database;

Step 2: Create Tables with Primary and Foreign Keys

CREATE TABLE farmers (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    location VARCHAR(100),
    contact VARCHAR(20)
);

CREATE TABLE crops (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    type VARCHAR(50) NOT NULL,
    planting_date DATE,
    harvest_date DATE,
    yield INT,
    price DECIMAL(10, 2),
    farmer_id INT,
    FOREIGN KEY (farmer_id) REFERENCES farmers(id)
);

Key concepts:

• PRIMARY KEY: Uniquely identifies each record (e.g., farmer's id)
• FOREIGN KEY: Links a table to another table (crops.farmer_id references farmers.id)
• NOT NULL: Field must contain a value
• VARCHAR(n): Variable-length string with maximum n characters
• DECIMAL(p, s): Decimal number with p total digits and s digits after decimal

The ALTER TABLE statement modifies an existing table structure.

Adding a New Column

ALTER TABLE farmers ADD COLUMN email VARCHAR(50);

To verify the change:

DESCRIBE farmers;

Creating an Index

Indexes improve data retrieval speed:

CREATE INDEX idx_crops_name ON crops(name);

Creating a View

A view is a virtual table based on the result of a query:

CREATE VIEW crop_farmer_information AS
SELECT c.id AS crop_id, c.name AS crop_name, c.planting_date,
       c.harvest_date, c.yield, c.price, f.id AS farmer_id, 
       f.name AS farmer_name, f.age, f.location, f.contact
FROM crops c
JOIN farmers f ON c.farmer_id = f.id;

The DROP statement removes database objects permanently.

Dropping an Index

DROP INDEX idx_crops_name ON crops;

To verify:

SHOW INDEX FROM crops;

PHP is a server-side scripting language used to create dynamic web pages that interact with databases.

PHP Syntax

<?php
    // This is a single-line comment
    /* This is a 
       multi-line comment */
    $name = "John";
    $age = 25;
    echo "My name is " . $name . ". I am " . $age . " years old.";
?>

Key points:

• PHP code goes inside <?php ?> tags
• Variables start with $ (e.g., $name)
• Statements end with semicolon (;)
• Use . to concatenate strings

PHP can connect to MySQL using either MySQLi or PDO extensions.

Method 1: Using MySQLi

<?php
$serverName = "localhost";
$username = "root";
$password = "";
$databaseName = "farming_database";

// Create connection
$conn = mysqli_connect($serverName, $username, $password, $databaseName);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully<br>";
?>

Inserting Data

<?php
$sql = "INSERT INTO crops (name, yield_per_hectare) VALUES ('Wheat', 4000)";

if (mysqli_query($conn, $sql)) {
    echo "New crop record created successfully<br>";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

Fetching Data

<?php
$sql = "SELECT id, name, yield_per_hectare FROM crops";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. 
             " - Yield: " . $row["yield_per_hectare"]. "kg<br>";
    }
} else {
    echo "0 results found<br>";
}
?>

Closing Connection

<?php
mysqli_close($conn);
?>

Method 2: Using PDO (Recommended)

PDO works with multiple database systems and supports prepared statements for security.

Connecting to Database

<?php
$host = 'localhost';
$db = 'farming_database';
$user = 'root';
$pass = '';
$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);
    echo "Connected successfully<br>";
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

Inserting Data Securely

<?php
$sql = "INSERT INTO crops (name, yield_per_hectare) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Corn', 3000]);
echo "New record created successfully";
?>

Fetching Data

<?php
$sql = "SELECT id, name, yield_per_hectare FROM crops";
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch()) {
    echo "id: {$row['id']} - Name: {$row['name']} - Yield: {$row['yield_per_hectare']}kg<br>";
}
?>

Closing Connection

<?php
$pdo = null;
?>

PHP code can be embedded within HTML to create dynamic web pages.

<!DOCTYPE html>
<html>
<head>
    <title>Farming Crops</title>
    <style>
        .crop { border: 1px solid #ccc; padding: 10px; margin: 10px; }
    </style>
</head>
<body>
    <h1>Farming Crops</h1>
    <div class="crop-gallery">
        <?php
        // Assuming $result contains fetched crop data
        while($row = $result->fetch_assoc()) {
            echo '<div class="crop">';
            echo '<h3>' . $row["crop_name"] . '</h3>';
            echo '<p>Yield: ' . $row["yield_per_hectare"] . ' kg</p>';
            echo '</div>';
        }
        ?>
    </div>
</body>
</html>

CRUD stands for Create, Read, Update, Delete — the four basic operations for managing data in a database.

Complete CRUD Example with PDO

<?php
// Database connection
$host = 'localhost';
$db = 'farming_database';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    // CREATE: Insert new record
    $sql = "INSERT INTO crops (name, yield_per_hectare) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['Rice', 3500]);
    
    // READ: Fetch all records
    $sql = "SELECT * FROM crops";
    $stmt = $pdo->query($sql);
    while ($row = $stmt->fetch()) {
        echo $row['name'] . ": " . $row['yield_per_hectare'] . "kg<br>";
    }
    
    // UPDATE: Modify existing record
    $sql = "UPDATE crops SET yield_per_hectare = 4000 WHERE name = 'Rice'";
    $pdo->exec($sql);
    
    // DELETE: Remove record
    $sql = "DELETE FROM crops WHERE name = 'Rice'";
    $pdo->exec($sql);
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$pdo = null;
?>

A Tanzanian small business owner at Mwanza's Bugando market can use PHP and MySQL to manage their shop inventory. For example, a duka la vinywaji ( beverage shop) owner can create a database to track soft drinks, water, and juices — recording product names, quantities in stock, and prices in Tanzanian shillings. A PHP web interface then allows the shopkeeper to easily add new stock, view low-inventory alerts, and generate daily sales reports, replacing manual paper-based记录-keeping with a efficient digital system.