kuunganisha PHP na MySQL, PHP database connection, MySQLi connection, PDO connection, PHP MySQL tutorial, database connection, localhost PHP, XAMPP MySQL

Utangulizi

Ili website iliyotengenezwa kwa PHP iweze kuhifadhi na kusoma taarifa kama vile majina ya watumiaji, wanafunzi, bidhaa, machapisho au matokeo, ni lazima PHP iunganishwe na database.

Katika somo hili tutajifunza:

• Jinsi ya kutengeneza MySQL database
• Jinsi ya kutengeneza table
• Jinsi ya kuunganisha PHP na MySQL kwa kutumia MySQLi
• Jinsi ya kutumia PDO
• Jinsi ya kuhifadhi taarifa kwa usalama
• Jinsi ya kuonyesha taarifa kutoka database
• Namna ya kutatua hitilafu za database connection

Vitu Vinavyohitajika

Kabla ya kuanza, hakikisha una:

1. Web server kama XAMPP, WAMP, Laragon au hosting yenye PHP
2. PHP 8 au toleo la karibu
3. MySQL au MariaDB
4. phpMyAdmin
5. Code editor kama Visual Studio Code
6. Browser kama Chrome au Firefox

Kama unatumia XAMPP, washa huduma hizi:

• Apache
• MySQL

Kisha fungua phpMyAdmin kupitia:

http://localhost/phpmyadmin

Hatua ya Kwanza: Tengeneza Database

Fungua phpMyAdmin, chagua sehemu ya SQL, kisha tumia code hii:

CREATE DATABASE faulink_blog
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Database yetu itaitwa:

faulink_blog

Matumizi ya utf8mb4 yanawezesha database kuhifadhi herufi mbalimbali, alama maalumu na emoji bila matatizo.

Hatua ya Pili: Tengeneza Table

Baada ya kutengeneza database, chagua database ya faulink_blog, kisha tumia SQL ifuatayo:

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table hii ina columns zifuatazo:

• id – Namba ya kipekee ya kila mtumiaji
• name – Jina la mtumiaji
• email – Barua pepe ya mtumiaji
• created_at – Tarehe na muda taarifa zilipohifadhiwa

Njia ya Kwanza: Kuunganisha PHP na MySQL kwa MySQLi

Tengeneza file linaloitwa:

database.php

Weka code hii:

<?php

$host = 'localhost';
$dbname = 'faulink_blog';
$username = 'root';
$password = '';

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die('Imeshindikana kuunganisha database.');
}

$conn->set_charset('utf8mb4');

Maana Taarifa za Connection

$host = 'localhost';

Hili ni jina la server inayohifadhi database. Kwa XAMPP mara nyingi ni localhost.

$dbname = 'faulink_blog';

Hili ni jina la database tuliyotengeneza.

$username = 'root';

Kwa XAMPP, username ya kawaida ni root.

$password = '';

Kwa XAMPP, mara nyingi user wa root hana password. Kwenye hosting, lazima utumie password uliyopewa au uliyoitengeneza.

Kupima Kama Database Imeunganishwa

Kwa majaribio unaweza kutumia code hii:

<?php

$host = 'localhost';
$dbname = 'faulink_blog';
$username = 'root';
$password = '';

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

$conn->set_charset('utf8mb4');

echo 'Database imeunganishwa vizuri.';

Ukiifungua kwenye browser na kuona ujumbe:

Database imeunganishwa vizuri.

basi PHP imefanikiwa kuwasiliana na MySQL.

Usionyeshe maelezo kamili ya database error kwenye website ya production kwa sababu yanaweza kufichua taarifa muhimu za mfumo wako.

Jinsi ya Kuhifadhi Taarifa kwa Usalama

Tengeneza file linaloitwa:

add_user.php

Weka code hii:

<?php

require_once 'database.php';

$message = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = trim($_POST['name'] ?? '');
    $email = trim($_POST['email'] ?? '');

    if ($name === '' || $email === '') {
        $message = 'Tafadhali jaza sehemu zote.';
    } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $message = 'Weka anuani sahihi ya barua pepe.';
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO users (name, email) VALUES (?, ?)'
        );

        $stmt->bind_param('ss', $name, $email);

        if ($stmt->execute()) {
            $message = 'Mtumiaji amehifadhiwa kwa mafanikio.';
        } else {
            $message = 'Imeshindikana kuhifadhi taarifa.';
        }

        $stmt->close();
    }
}
?>

<!DOCTYPE html>
<html lang="sw">
<head>
    <meta charset="UTF-8">
    <meta
        name="viewport"
        content="width=device-width, initial-scale=1.0"
    >
    <title>Ongeza Mtumiaji</title>

    <style>
        * {
            box-sizing: border-box;
        }

        body {
            margin: 0;
            padding: 20px;
            background: #f1f5f9;
            color: #0f172a;
            font-family: Arial, sans-serif;
        }

        .container {
            width: 100%;
            max-width: 650px;
            margin: 40px auto;
            padding: 28px;
            background: #ffffff;
            border-radius: 16px;
            box-shadow: 0 10px 35px rgba(15, 23, 42, 0.10);
        }

        h1 {
            margin-top: 0;
        }

        label {
            display: block;
            margin: 18px 0 7px;
            font-weight: 700;
        }

        input {
            width: 100%;
            padding: 13px 14px;
            border: 1px solid #cbd5e1;
            border-radius: 9px;
            font-size: 16px;
        }

        input:focus {
            border-color: #2563eb;
            outline: 3px solid rgba(37, 99, 235, 0.12);
        }

        button {
            width: 100%;
            margin-top: 22px;
            padding: 14px;
            border: 0;
            border-radius: 9px;
            background: #2563eb;
            color: #ffffff;
            font-size: 16px;
            font-weight: 700;
            cursor: pointer;
        }

        button:hover {
            background: #1d4ed8;
        }

        .message {
            margin-bottom: 18px;
            padding: 12px;
            border-radius: 8px;
            background: #e0f2fe;
        }

        @media (max-width: 600px) {
            body {
                padding: 10px;
            }

            .container {
                margin: 10px auto;
                padding: 20px 15px;
                border-radius: 12px;
            }
        }
    </style>
</head>

<body>
    <main class="container">
        <h1>Ongeza Mtumiaji</h1>
        <p>Jaza taarifa zifuatazo ili kuhifadhi mtumiaji.</p>

        <?php if ($message !== ''): ?>
            <div class="message">
                <?= htmlspecialchars($message, ENT_QUOTES, 'UTF-8') ?>
            </div>
        <?php endif; ?>

        <form method="POST">
            <label for="name">Jina kamili</label>
            <input
                type="text"
                id="name"
                name="name"
                maxlength="100"
                required
            >

            <label for="email">Barua pepe</label>
            <input
                type="email"
                id="email"
                name="email"
                maxlength="150"
                required
            >

            <button type="submit">Hifadhi Mtumiaji</button>
        </form>
    </main>
</body>
</html>

Kwa Nini Tumetumia Prepared Statement?

Prepared statement husaidia kuzuia shambulio linaloitwa SQL Injection.

Usitumie njia hii:

$sql = "INSERT INTO users (name, email)
        VALUES ('$name', '$email')";

Njia hiyo si salama kwa sababu taarifa za mtumiaji zinaingizwa moja kwa moja ndani ya SQL query.

Njia salama zaidi ni:

$stmt = $conn->prepare(
    'INSERT INTO users (name, email) VALUES (?, ?)'
);

$stmt->bind_param('ss', $name, $email);
$stmt->execute();

Herufi ss zina maana kuwa values zote mbili ni strings.

Jinsi ya Kuonyesha Taarifa Kutoka Database

Tengeneza file linaloitwa:

users.php

Weka code hii:

<?php

require_once 'database.php';

$result = $conn->query(
    'SELECT id, name, email, created_at
     FROM users
     ORDER BY id DESC'
);
?>

<!DOCTYPE html>
<html lang="sw">
<head>
    <meta charset="UTF-8">
    <meta
        name="viewport"
        content="width=device-width, initial-scale=1.0"
    >
    <title>Orodha ya Watumiaji</title>

    <style>
        * {
            box-sizing: border-box;
        }

        body {
            margin: 0;
            padding: 20px;
            background: #f8fafc;
            color: #0f172a;
            font-family: Arial, sans-serif;
        }

        .container {
            width: 100%;
            max-width: 1100px;
            margin: auto;
        }

        .table-wrapper {
            width: 100%;
            overflow-x: auto;
            background: #ffffff;
            border-radius: 14px;
            box-shadow: 0 8px 30px rgba(15, 23, 42, 0.08);
        }

        table {
            width: 100%;
            min-width: 650px;
            border-collapse: collapse;
        }

        th,
        td {
            padding: 14px;
            border-bottom: 1px solid #e2e8f0;
            text-align: left;
        }

        th {
            background: #0f172a;
            color: #ffffff;
        }

        tr:hover {
            background: #f1f5f9;
        }

        .empty {
            padding: 25px;
            text-align: center;
        }

        @media (max-width: 600px) {
            body {
                padding: 10px;
            }
        }
    </style>
</head>

<body>
    <main class="container">
        <h1>Orodha ya Watumiaji</h1>

        <div class="table-wrapper">
            <?php if ($result && $result->num_rows > 0): ?>
                <table>
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Jina</th>
                            <th>Barua pepe</th>
                            <th>Tarehe</th>
                        </tr>
                    </thead>

                    <tbody>
                        <?php while ($user = $result->fetch_assoc()): ?>
                            <tr>
                                <td>
                                    <?= (int) $user['id'] ?>
                                </td>

                                <td>
                                    <?= htmlspecialchars(
                                        $user['name'],
                                        ENT_QUOTES,
                                        'UTF-8'
                                    ) ?>
                                </td>

                                <td>
                                    <?= htmlspecialchars(
                                        $user['email'],
                                        ENT_QUOTES,
                                        'UTF-8'
                                    ) ?>
                                </td>

                                <td>
                                    <?= htmlspecialchars(
                                        $user['created_at'],
                                        ENT_QUOTES,
                                        'UTF-8'
                                    ) ?>
                                </td>
                            </tr>
                        <?php endwhile; ?>
                    </tbody>
                </table>
            <?php else: ?>
                <div class="empty">
                    Hakuna watumiaji waliohifadhiwa.
                </div>
            <?php endif; ?>
        </div>
    </main>
</body>
</html>

Tumetumia htmlspecialchars() kuzuia HTML au JavaScript hatarishi kuonyeshwa moja kwa moja kwenye ukurasa.

Njia ya Pili: Kuunganisha PHP na MySQL kwa PDO

PDO ni njia nyingine nzuri na salama ya kuunganisha PHP na database.

Tengeneza file linaloitwa:

pdo_database.php

Weka code hii:

<?php

$host = 'localhost';
$dbname = 'faulink_blog';
$username = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

$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, $username, $password, $options);
} catch (PDOException $e) {
    error_log($e->getMessage());

    exit('Imeshindikana kuunganisha database.');
}

Kuhifadhi Data kwa PDO

<?php

require_once 'pdo_database.php';

$name = 'Faustine Maiga';
$email = 'faustine@example.com';

$sql = 'INSERT INTO users (name, email) VALUES (:name, :email)';

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':name' => $name,
    ':email' => $email
]);

echo 'Taarifa zimehifadhiwa kwa mafanikio.';

Kusoma Data kwa PDO

<?php

require_once 'pdo_database.php';

$stmt = $pdo->query(
    'SELECT id, name, email, created_at
     FROM users
     ORDER BY id DESC'
);

$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo htmlspecialchars(
        $user['name'],
        ENT_QUOTES,
        'UTF-8'
    );

    echo '<br>';
}

Taarifa za Database Kwenye Hosting

Ukiweka website kwenye hosting, taarifa zinaweza kuwa tofauti na za localhost.

Mfano:

<?php

$host = 'localhost';
$dbname = 'accountname_faulink_blog';
$username = 'accountname_dbuser';
$password = 'PASSWORD_YAKO_IMARA';

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    error_log($conn->connect_error);

    exit('Imeshindikana kuunganisha database.');
}

$conn->set_charset('utf8mb4');

Taarifa utakazohitaji ni:

• Database Host
• Database Name
• Database Username
• Database Password
• Database Port, kama hosting yako inahitaji

Unaweza kuzipata kupitia cPanel, Plesk au dashboard ya hosting yako.

Kulinda Taarifa za Database

Usiweke username na password za database kwenye GitHub ya public.

Unaweza kuhifadhi taarifa hizo kwenye environment variables:

<?php

$host = getenv('DB_HOST');
$dbname = getenv('DB_NAME');
$username = getenv('DB_USER');
$password = getenv('DB_PASSWORD');

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    error_log($conn->connect_error);

    exit('Imeshindikana kuunganisha database.');
}

$conn->set_charset('utf8mb4');

Kwenye hosting yenye uwezo wa kutumia .env, unaweza kuwa na taarifa kama hizi:

DB_HOST=localhost
DB_NAME=faulink_blog
DB_USER=faulink_user
DB_PASSWORD=password_imara

Hakikisha file la .env haliwezi kufunguliwa moja kwa moja kupitia browser.

Kwa Apache, unaweza kulizuia kwa kutumia .htaccess:

<Files ".env">
    Require all denied
</Files>

Hitilafu Zinazotokea Mara kwa Mara

1. Access denied for user

Mfano wa error:

Access denied for user 'root'@'localhost'

Sababu zinazowezekana:

• Username si sahihi
• Password si sahihi
• User hana ruhusa ya kutumia database
• Database user hajaunganishwa na database kwenye cPanel

Suluhisho:

• Hakikisha username na password ni sahihi
• Kwenye cPanel, fungua MySQL Databases
• Ongeza database user kwenye database
• Mpe ruhusa zinazohitajika

2. Unknown database

Mfano:

Unknown database 'faulink_blog'

Maana yake database yenye jina hilo haipo.

Hakikisha jina lililo kwenye PHP linafanana kabisa na jina la database:

$dbname = 'faulink_blog';

3. Connection refused

Mfano:

Connection refused

Angalia kama:

• MySQL imewashwa
• Host ni sahihi
• Port ni sahihi
• Hosting inaruhusu database connection hiyo

Kwa XAMPP, fungua Control Panel na uwashe MySQL.

4. Class “mysqli” not found

Hii inaonyesha kuwa extension ya MySQLi haijawashwa kwenye PHP.

Kwenye php.ini, hakikisha extension hii imewezeshwa:

extension=mysqli

Baada ya kubadilisha, restart Apache.

5. Herufi za Kiswahili Kuonekana Vibaya

Hakikisha connection inatumia:

$conn->set_charset('utf8mb4');

Na HTML iwe na:

<meta charset="UTF-8">

Database na tables pia zinapaswa kutumia utf8mb4.

MySQLi au PDO: Ipi Ni Bora?

Kama project yako itatumia MySQL pekee, MySQLi inatosha. Kama unahitaji mfumo unaoweza kuhamia kwenye aina nyingine ya database, PDO ni chaguo zuri zaidi.

Muundo Unaopendekezwa wa Project

my_project/
│
├── database.php
├── add_user.php
├── users.php
├── index.php
└── assets/
    ├── css/
    ├── js/
    └── images/

Kwa project kubwa zaidi, usichanganye database connection, HTML na business logic yote kwenye file moja. Tenganisha files ili mfumo uwe rahisi kutunza na kuboresha.

Vidokezo Muhimu vya Usalama

1. Tumia prepared statements kila wakati.
2. Usionyeshe database password kwenye browser.
3. Usionyeshe technical error kamili kwa watumiaji wa kawaida.
4. Tumia htmlspecialchars() wakati wa kuonyesha taarifa.
5. Hakikisha inputs zinakaguliwa kabla ya kuhifadhiwa.
6. Tumia password imara kwenye hosting.
7. Mpe database user ruhusa anazohitaji pekee.
8. Tumia HTTPS kwenye website ya production.
9. Usihifadhi passwords za watumiaji kama maandishi ya kawaida.
10. Tumia password_hash() na password_verify() kwa passwords za watumiaji.

Mfano wa kuhash password:

$passwordHash = password_hash(
    $_POST['password'],
    PASSWORD_DEFAULT
);

Mfano wa kuhakiki password wakati wa login:

if (password_verify($password, $passwordHash)) {
    echo 'Password ni sahihi.';
}

Hitimisho

Kuunganisha MySQL database na PHP ni hatua muhimu katika kutengeneza website inayoweza kuhifadhi na kusimamia taarifa.

Kwa ufupi:

1. Tengeneza database na table.
2. Weka taarifa sahihi za connection.
3. Tumia MySQLi au PDO kuunganisha database.
4. Tumia prepared statements kuhifadhi data.
5. Kagua data kabla ya kuiingiza kwenye database.
6. Linda taarifa za database na usionyeshe errors nyeti kwa watumiaji.

Ukifuata hatua hizi, utaweza kutengeneza mifumo kama:

• Mfumo wa kusajili wanafunzi
• Mfumo wa kuingiza matokeo
• Mfumo wa uhasibu
• Blog
• Mfumo wa watumiaji
• Mfumo wa bidhaa na mauzo
• Mfumo wa login na registration

Kwa mazingira ya production, PDO pamoja na prepared statements, environment variables na error logging ni msingi mzuri wa connection salama na ya kitaalamu.