phpjquerycodeigniter-4

How to integrate a custom search option with dynamic filelds for a master data listing page?

Can anyone suggest the best way for integrating search/filter options for master data listing page?

I have around 20 fields/columns to search. I do not want to show 20 separate fields for this. Instead I want to provide a common field for choosing the search column and another field for entering the value.

I am not using any grid libraries for the listing page.

I am using CodeIgniter 4 with MySQL in the backend and jQuery in the frontend.

It will be a great help if someone can suggest a suitable method.

Solution

  • If you only need to make a single field and its corresponding value searchable, a dropdown is a clean and straightforward option for you. You mentioned that you're not using any libraries, I'm assugming you prefer the pure server-side approach without AJAX, so I will completely avoid any Javascript.

    Given that you didn't provide any code, I'll outline a generic solution with generic names for you.

    First, ensure you have the model and has all the fields declared. In your model, add this getFieldNames function.

    <?php
// app/Models/DataModel.php
namespace App\Models;

use CodeIgniter\Model;

class DataModel extends Model {
    protected $table = 'your_table_name';
    protected $primaryKey = 'id';
    protected $allowedFields = true; // Allow all fields
    // All your predefined columns here
   
    /**
    * Get all field names from the database table.
    *
    * @return array<string> Array of column names.
    */
    public function getFieldNames(): array {
        return $this->db->getFieldNames($this->table);
    }
}

    Then you need the controller, I will call it as DataController now:

    // app/Controllers/DataController.php
namespace App\Controllers;

use App\Models\DataModel;
use CodeIgniter\HTTP\ResponseInterface;

class DataController extends BaseController {
    public function index(): string {
        $model = new DataModel(); // Instantiate your model
        $columns = $model->getFieldNames(); // Fetch column names
        $data = $model->findAll(); // Fetch initial data if needed (e.g: for list)

        return view('your_view', [
            'columns' => $columns,
            'data' => $data
        ]);
    }

    public function search(): ResponseInterface {
        $column = $this->request->getPost('column') ?? '';
        $value = $this->request->getPost('value') ?? '';

        if (empty($column) || empty($value)) {
            return $this->response->setStatusCode(ResponseInterface::HTTP_BAD_REQUEST)
                                  ->setBody('Invalid parameters.');
        }

        $model = new DataModel();
        $results = $model->like($column, $value)->findAll();

        return $this->response->setJSON([
            'columns' => $model->getFieldNames(),
            'data' => $results
        ]);
    }
}

    You need to set the routes for it in app/Config/Routes.php:

    use App\Controllers\DataController;

$routes->get('/data', [DataController::class, 'index']); // Your list page with filter
$routes->post('/data/search', [DataController::class, 'search']); // Your search endpoint

    And the keypoint is your view, using a simple dropdown:

    <!-- app/Views/your_view.php -->
<?php
/**
 * @var array $columns
 * @var array $data
 */
?>
<!DOCTYPE html>
<html>
<head>
    <title>Data Listing</title>
</head>
<body>
    <form method="post" action="/data/search">
        <select name="column">
            <option value="">Select Column</option>
            <?php foreach ($columns as $column): ?>
                <option value="<?= htmlspecialchars($column, ENT_QUOTES, 'UTF-8') ?>">
                    <?= ucfirst(str_replace('_', ' ', $column)) ?>
                </option>
            <?php endforeach; ?>
        </select>

        <input type="text" name="value" placeholder="Enter search term">
        <button type="submit">Search</button>
    </form>

    <table border="1">
        <thead>
            <tr>
                <?php foreach ($columns as $column): ?>
                    <th><?= ucfirst(str_replace('_', ' ', $column)) ?></th>
                <?php endforeach; ?>
            </tr>
        </thead>
        <tbody>
            <?php if (!empty($data)): ?>
                <?php foreach ($data as $row): ?>
                    <tr>
                        <?php foreach ($columns as $column): ?>
                            <td><?= htmlspecialchars($row[$column] ?? '', ENT_QUOTES, 'UTF-8') ?></td>
                        <?php endforeach; ?>
                    </tr>
                <?php endforeach; ?>
            <?php else: ?>
                <tr>
                    <td colspan="<?= count($columns) ?>">No data found</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>
</body>
</html>

    Of course, this code can be refined further. For example, add more sophisticated validation, as not every column has the same data type, which can impact how searches should be handled. Or adittionaly moving model initialization to controller's construct, so no need to repeatedly instantiate the model in each endpoint, etc...

    But I hope this make sense. If you want me to do this with your code, provide me your controller, and model, view, structure, etc.. Additionally if you want to skip page reload, we can use a javascript solution without additional libraries (only using ajax and jquery).

    Let me know if this helped!