Basic Device Management Database Editor Tool Using PHP

[paypal56_ab6mk6y7]

This is a simple, no-nonsense device information handler based on PHP that handles device information stored in a database. It provides an intuitive user interface for inserting, updating, and querying devices stored within a database. Be it simple product listings or a device inventory, this tool enables users to organize and update device data with ease in one place in real time.

The application will help users manage basic device information, such as device name, brand, model, variation, and description, coupled with its image-easy to fill all in one form. This form also has space for associating devices with similar models in order to make a database more connected and organized. It allows devices to be categorized by type and enables users to provide general tags from a predefined list for easy filtering and searching of devices by various attributes.

This application supports a variety of key features, including: - **Minimalistic Interface**: The application has a simple layout that has been kept neat, clean, and very functional. Such an interface minimizes clutter and focuses on primary device management activities. - **Data Integrity**: The database editor keeps the records tidy, making it easier for users to update and maintain device information with minimum unnecessary complexities.
3. **Device Linking**: It is a one-of-a-kind feature that permits users to connect similar devices; thus, it would be simpler to keep track of various related products.
4. **Tagging and Category**: Each device can be tagged with numerous relevant tags, which can later be filtered to find specific device types to enhance searchability.
5. **File Upload**: It is very easy to upload and attach images with the devices so that each and every device could visually be represented.
6. **Type**: Selection to be used to assign device type - from a predefined list for maintaining consistency in categorization.
7. **Website URL**: Provide the possibility of giving a record of the device with a link to an external website so the user can then go directly to a particular device's website for more information.

With a highly customizable approach, the tool lets the developer freely change the interface and extend the functionality as per needs. This database editor strikes that optimum balance of simplicity and power required by an average business enterprise for managing one's devices or products.

Designed in such a manner for better security, it avoids common vulnerabilities such as SQL injection through prepared statements or validation checks for input fields to maintain data safety and keep user information intact.

In all, this would be the perfect tool for a person needing only a simple, effective database management system, such as device or similar product tracking. In summary, its structure is compact and user-friendly, which makes the task of maintaining an updated inventory fairly easy. No matter if you run a small e-commerce or manage a warehouse of devices, it will facilitate your work, speed up the time factor, and make your data management process more accurate.

Therefore, with this tool, you get all the needed features without adding any extra complexity; hence, it will be the best fit for anyone who wants to manage device data without breaking a sweat.

[paypal56_ab6mk6y7]

Part 1: Setting Up Database Connection (`db.php`)

The first step is setting up the connection to the database. We'll use PDO for database interaction.

```php

Code: Select all

<?php
$host = 'localhost';
$dbname = 'devices_db';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

```

### Part 2: Creating Devices Table (`create_devices_table.php`)

To begin, let's create a table for storing device information.

```php

Code: Select all

<?php
include 'db.php';

$sql = "CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    brand VARCHAR(255) NOT NULL,
    model VARCHAR(255) NOT NULL,
    variation VARCHAR(255),
    description TEXT,
    image VARCHAR(255),
    device_type_id INT,
    tags VARCHAR(255),
    similar_devices VARCHAR(255),
    website_url VARCHAR(255)
)";

$pdo->exec($sql);
echo "Table 'devices' created successfully!";
?>

```

### Part 3: Creating Categories Table (`create_device_types_table.php`)

Next, we will create a table for device types.

```php

Code: Select all

<?php
include 'db.php';

$sql = "CREATE TABLE IF NOT EXISTS device_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL
)";

$pdo->exec($sql);
echo "Table 'device_types' created successfully!";
?>

```

### Part 4: Creating Tags Table (`create_tags_table.php`)

Now, let's create a table for tags.

```php

Code: Select all

<?php
include 'db.php';

$sql = "CREATE TABLE IF NOT EXISTS tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(255) NOT NULL
)";

$pdo->exec($sql);
echo "Table 'tags' created successfully!";
?>

```

### Part 5: Adding New Devices (`add_device.php`)

Here is the PHP code to add new devices to the database.

```php

Code: Select all

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $brand = $_POST['brand'];
    $model = $_POST['model'];
    $variation = $_POST['variation'];
    $description = $_POST['description'];
    $device_type = $_POST['device_type'];
    $tags = implode(',', $_POST['tags']);
    $similar_devices = $_POST['similar_devices'];
    $website_url = $_POST['website_url'];
    $image = 'uploads/' . basename($_FILES['image']['name']);
    move_uploaded_file($_FILES['image']['tmp_name'], $image);

    $stmt = $pdo->prepare("INSERT INTO devices 
                            (name, brand, model, variation, description, image, device_type_id, tags, similar_devices, website_url) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->execute([$name, $brand, $model, $variation, $description, $image, $device_type, $tags, $similar_devices, $website_url]);

    echo "Device added successfully!";
    header("Location: add_device.php"); // Redirect after form submission
    exit;
}
?>

<form method="POST" enctype="multipart/form-data">
    <label>Name:</label>
    <input type="text" name="name" required><br>

    <label>Brand:</label>
    <input type="text" name="brand" required><br>

    <label>Model:</label>
    <input type="text" name="model" required><br>

    <label>Variation:</label>
    <input type="text" name="variation"><br>

    <label>Description:</label>
    <textarea name="description"></textarea><br>

    <label>Image:</label>
    <input type="file" name="image"><br>

    <label>Device Type:</label>
    <select name="device_type" required>
        <?php
        $stmt = $pdo->query("SELECT * FROM device_types");
        while ($row = $stmt->fetch()) {
            echo "<option value='{$row['id']}'>{$row['type_name']}</option>";
        }
        ?>
    </select><br>

    <label>Tags:</label>
    <select name="tags[]" multiple required>
        <?php
        $stmt = $pdo->query("SELECT * FROM tags");
        while ($row = $stmt->fetch()) {
            echo "<option value='{$row['id']}'>{$row['tag_name']}</option>";
        }
        ?>
    </select><br>

    <label>Similar Devices:</label>
    <input type="text" name="similar_devices"><br>

    <label>Website URL:</label>
    <input type="url" name="website_url"><br>

    <input type="submit" value="Add Device">
</form>

```

### Part 6: Viewing Devices (`view_devices.php`)

Here is a script that will allow viewing the added devices.

```php

Code: Select all

<?php
include 'db.php';

$stmt = $pdo->query("SELECT * FROM devices");

echo "<table>";
echo "<tr><th>Name</th><th>Brand</th><th>Model</th><th>Action</th></tr>";
while ($row = $stmt->fetch()) {
    echo "<tr>";
    echo "<td>{$row['name']}</td>";
    echo "<td>{$row['brand']}</td>";
    echo "<td>{$row['model']}</td>";
    echo "<td><a href='edit_device.php?id={$row['id']}'>Edit</a> | <a href='delete_device.php?id={$row['id']}'>Delete</a></td>";
    echo "</tr>";
}
echo "</table>";
?>

```

### Part 7: Editing Device (`edit_device.php`)

This script allows editing an existing device’s information.

```php

Code: Select all

<?php
include 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    // Get device information by ID
    $stmt = $pdo->prepare("SELECT * FROM devices WHERE id = ?");
    $stmt->execute([$id]);
    $device = $stmt->fetch();

    if (!$device) {
        echo "Device not found!";
        exit;
    }

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $name = $_POST['name'];
        $brand = $_POST['brand'];
        $model = $_POST['model'];
        $variation = $_POST['variation'];
        $description = $_POST['description'];
        $device_type = $_POST['device_type'];
        $tags = implode(',', $_POST['tags']);
        $similar_devices = $_POST['similar_devices'];
        $website_url = $_POST['website_url'];
        $image = $device['image'];

        if ($_FILES['image']['name']) {
            $image = 'uploads/' . basename($_FILES['image']['name']);
            move_uploaded_file($_FILES['image']['tmp_name'], $image);
        }

        $stmt = $pdo->prepare("UPDATE devices 
                                SET name = ?, brand = ?, model = ?, variation = ?, description = ?, image = ?, 
                                    device_type_id = ?, tags = ?, similar_devices = ?, website_url = ? 
                                WHERE id = ?");
        $stmt->execute([$name, $brand, $model, $variation, $description, $image, $device_type, $tags, $similar_devices, $website_url, $id]);

        echo "Device updated successfully!";
        header("Location: view_devices.php");
        exit;
    }
} else {
    echo "Device ID is required!";
    exit;
}
?>

<form method="POST" enctype="multipart/form-data">
    <label>Name:</label>
    <input type="text" name="name" value="<?= htmlspecialchars($device['name']) ?>" required><br>

    <label>Brand:</label>
    <input type="text" name="brand" value="<?= htmlspecialchars($device['brand']) ?>" required><br>

    <label>Model:</label>
    <input type="text" name="model" value="<?= htmlspecialchars($device['model']) ?>" required><br>

    <label>Variation:</label>
    <input type="text" name="variation" value="<?= htmlspecialchars($device['variation']) ?>"><br>

    <label>Description:</label>
    <textarea name="description"><?= htmlspecialchars($device['description']) ?></textarea><br>

    <label>Image:</label>
    <input type="file" name="image"><br>
    <img src="<?= htmlspecialchars($device['image']) ?>" alt="Device Image" width="100"><br>

    <label>Device Type:</label>
    <select name="device_type" required>
        <?php
        $stmt = $pdo->query("SELECT * FROM device_types");
        while ($row = $stmt->fetch()) {
            $selected = $row['id'] == $device['device_type_id'] ? 'selected' : '';
            echo "<option value='{$row['id']}' $selected>{$row['type_name']}</option>";
        }
        ?>
    </select><br>

    <label>Tags:</label>
    <select name="tags[]" multiple required>
        <?php
        $stmt = $pdo->query("SELECT * FROM tags");
        $selected_tags = explode(',', $device['tags']);
        while ($row = $stmt->fetch()) {
            $selected = in_array($row['id'], $selected_tags) ? 'selected' : '';
            echo "<option value='{$row['id']}' $selected>{$row['tag_name']}</option>";
        }
        ?>
    </select><br>

    <label>Similar Devices:</label>
    <input type="text" name="similar_devices" value="<?= htmlspecialchars($device['similar_devices']) ?>"><br>

    <label>Website URL:</label>
    <input type="url" name="website_url" value="<?= htmlspecialchars($device['website_url']) ?>"><br>

    <input type="submit" value="Update Device">
</form>

```

### Part 8: Deleting Device (`delete_device.php`)

To delete a device, you can use the following PHP script:

```php

Code: Select all

<?php
include 'db.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    // Delete the device by its ID
    $stmt = $pdo->prepare("DELETE FROM devices WHERE id = ?");
    $stmt->execute([$id]);

    echo "Device deleted successfully!";
    header("Location: view_devices.php");
    exit;
} else {
    echo "Device ID is required!";
    exit;
}
?>

```

### Part 9: Listing Device Types and Tags (`manage_device_types.php`)

To manage device types and tags, you can add the following functionality:

#### Manage Device Types:
```php

Code: Select all

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $type_name = $_POST['type_name'];

    // Insert new device type
    $stmt = $pdo->prepare("INSERT INTO device_types (type_name) VALUES (?)");
    $stmt->execute([$type_name]);

    echo "Device type added successfully!";
    header("Location: manage_device_types.php");
    exit;
}

$stmt = $pdo->query("SELECT * FROM device_types");
echo "<h2>Device Types</h2>";
echo "<ul>";
while ($row = $stmt->fetch()) {
    echo "<li>{$row['type_name']}</li>";
}
?>

<form method="POST">
    <label>Device Type Name:</label>
    <input type="text" name="type_name" required><br>
    <input type="submit" value="Add Device Type">
</form>

```

#### Manage Tags:
```php

Code: Select all

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $tag_name = $_POST['tag_name'];

    // Insert new tag
    $stmt = $pdo->prepare("INSERT INTO tags (tag_name) VALUES (?)");
    $stmt->execute([$tag_name]);

    echo "Tag added successfully!";
    header("Location: manage_tags.php");
    exit;
}

$stmt = $pdo->query("SELECT * FROM tags");
echo "<h2>Tags</h2>";
echo "<ul>";
while ($row = $stmt->fetch()) {
    echo "<li>{$row['tag_name']}</li>";
}
?>

<form method="POST">
    <label>Tag Name:</label>
    <input type="text" name="tag_name" required><br>
    <input type="submit" value="Add Tag">
</form>

```

### Part 10: File Upload Folder (`uploads/`)

Make sure to create an `uploads` folder in your project directory, where images will be stored. You can also set appropriate permissions to ensure the web server can write to this folder.

### Final Structure:

Your project should now have the following files:

- `db.php` — Database connection.
- `create_devices_table.php` — Creates devices table.
- `create_device_types_table.php` — Creates device types table.
- `create_tags_table.php` — Creates tags table.
- `add_device.php` — Form to add new device.
- `view_devices.php` — Displays all devices.
- `edit_device.php` — Edit device details.
- `delete_device.php` — Delete a device.
- `manage_device_types.php` — Manage device types.
- `manage_tags.php` — Manage tags.
- `uploads/` — Folder to store uploaded device images.

[paypal56_ab6mk6y7]

## Part 11: Pagination for Device Listing (`view_devices.php`)

If you have a large number of devices, it's a good idea to implement pagination. Below is how you can implement pagination in the `view_devices.php` script:

```php

Code: Select all

<?php
include 'db.php';

$limit = 10; // Number of devices per page
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Fetch total number of devices
$stmt = $pdo->query("SELECT COUNT(*) FROM devices");
$total_devices = $stmt->fetchColumn();

// Fetch devices for current page
$stmt = $pdo->prepare("SELECT * FROM devices LIMIT :limit OFFSET :offset");
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

echo "<h2>Device List</h2>";
echo "<table>";
echo "<tr><th>Name</th><th>Brand</th><th>Model</th><th>Actions</th></tr>";

while ($device = $stmt->fetch()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($device['name']) . "</td>";
    echo "<td>" . htmlspecialchars($device['brand']) . "</td>";
    echo "<td>" . htmlspecialchars($device['model']) . "</td>";
    echo "<td>
            <a href='edit_device.php?id=" . $device['id'] . "'>Edit</a> | 
            <a href='delete_device.php?id=" . $device['id'] . "'>Delete</a>
          </td>";
    echo "</tr>";
}
echo "</table>";

// Pagination
$total_pages = ceil($total_devices / $limit);
echo "<div class='pagination'>";
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='view_devices.php?page=$i'>$i</a> ";
}
echo "</div>";
?>

```

### Part 12: Device Search Functionality (`search_devices.php`)

You can add search functionality to allow users to search for devices based on name, brand, model, or any other criteria.

#### Search Form (`search_devices.php`):

```php

Code: Select all

<?php
include 'db.php';

$search_query = isset($_GET['search']) ? $_GET['search'] : '';

if ($_SERVER['REQUEST_METHOD'] == 'GET' && !empty($search_query)) {
    $stmt = $pdo->prepare("SELECT * FROM devices WHERE name LIKE :search OR brand LIKE :search OR model LIKE :search");
    $stmt->execute(['search' => '%' . $search_query . '%']);
} else {
    $stmt = $pdo->query("SELECT * FROM devices");
}

echo "<h2>Device Search</h2>";
echo "<form method='GET'>
        <input type='text' name='search' value='" . htmlspecialchars($search_query) . "' placeholder='Search for a device...'>
        <input type='submit' value='Search'>
      </form>";

echo "<table>";
echo "<tr><th>Name</th><th>Brand</th><th>Model</th><th>Actions</th></tr>";

while ($device = $stmt->fetch()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($device['name']) . "</td>";
    echo "<td>" . htmlspecialchars($device['brand']) . "</td>";
    echo "<td>" . htmlspecialchars($device['model']) . "</td>";
    echo "<td>
            <a href='edit_device.php?id=" . $device['id'] . "'>Edit</a> | 
            <a href='delete_device.php?id=" . $device['id'] . "'>Delete</a>
          </td>";
    echo "</tr>";
}
echo "</table>";
?>

```

### Part 13: File Upload Validation (Image Handling)

To prevent unwanted file uploads (e.g., security risks), it’s important to validate images before saving them.

#### File Upload Validation in `add_device.php` and `edit_device.php`:

Add the following validation to ensure that only image files are uploaded:

```php

Code: Select all

if ($_FILES['photo']['error'] == UPLOAD_ERR_OK) {
    $tmp_name = $_FILES['photo']['tmp_name'];
    $name = basename($_FILES['photo']['name']);
    $upload_dir = 'uploads/';
    $file_path = $upload_dir . $name;

    // Validate file type (only images)
    $allowed_types = ['image/jpeg', 'image/png', 'image/gif'];
    if (in_array(mime_content_type($tmp_name), $allowed_types)) {
        // Move file to the upload directory
        if (move_uploaded_file($tmp_name, $file_path)) {
            echo "File uploaded successfully.";
        } else {
            echo "Error moving file.";
        }
    } else {
        echo "Invalid file type. Only images are allowed.";
    }
} else {
    echo "Error uploading file.";
}

```

This code checks if the uploaded file is a valid image type (JPEG, PNG, or GIF) and only allows those files to be uploaded.

### Part 14: Security Improvements

While the application is functional, it's important to implement some basic security features to make sure that user inputs are safe:

#### Prepared Statements

Ensure that all database queries are using prepared statements to prevent SQL injection.

```php

Code: Select all

$stmt = $pdo->prepare("SELECT * FROM devices WHERE id = ?");
$stmt->execute([$device_id]);
$device = $stmt->fetch();

```

#### XSS Protection

Use `htmlspecialchars()` on all user inputs displayed in the browser to prevent Cross-Site Scripting (XSS) attacks.

```php

Code: Select all

echo htmlspecialchars($device['name']);

```

#### File Upload Security

Validate file extensions and MIME types to avoid malicious uploads, and consider using a random filename for uploaded files to avoid conflicts and potential exploits.

### Part 15: Final Considerations

#### Error Handling

Make sure you handle database connection errors and file upload errors properly. You can display user-friendly error messages in the UI instead of exposing raw error details.

```php

Code: Select all

try {
    // Database connection code
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

```

#### Adding User Authentication

For security purposes, consider adding user authentication to your system. You can use PHP sessions to allow only authorized users to access device management features.

```php

Code: Select all

session_start();
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit;
}

```