I can't properly fetch the firstname and lastname from users table and title from the books table

General
1

image
image1863×947 51.1 KB

The borrower’s name and title aren’t displaying as expected in my CodeIgniter 4 app. Despite trying various solutions, the issue persists. Could someone help troubleshoot and resolve it? Your assistance would be greatly appreciated.

public function getPendingTransactions($user_id = null)
{
    // Start the query builder
    $builder = $this->db->table('transactions')
        ->select([
            'transactions.transaction_id',
            'transactions.user_id',
            'transactions.book_id',
            'transactions.borrow_date',
            'transactions.due_date',
            'transactions.status',
            'users.firstname',
            'users.lastname',
            'books.title'
        ])
        ->join('users', 'users.user_id = transactions.user_id', 'left')  // Left join for users
        ->join('books', 'books.book_id = transactions.book_id', 'left')  // Left join for books
        ->where('transactions.status', 'pending');  // Filter by pending status

    // Add user filter if provided
    if ($user_id !== null) {
        $builder->where('transactions.user_id', $user_id);
    }

    // Debug: Log the compiled SQL query
    log_message('debug', 'SQL Query: ' . $builder->getCompiledSelect());

    // Fetch the transactions
    $transactions = $builder->get()->getResultArray();

    // Log the query result for debugging
    log_message('debug', 'Pending Transactions Query Result: ' . print_r($transactions, true));

    // Handle NULL values by setting defaults
    foreach ($transactions as &$transaction) {
        // Default to 'Unknown' for firstname, 'N/A' for title, and empty string for lastname
        $transaction['firstname'] = !empty($transaction['firstname']) ? $transaction['firstname'] : 'Unknown';
        $transaction['lastname'] = !empty($transaction['lastname']) ? $transaction['lastname'] : '';
        $transaction['title'] = !empty($transaction['title']) ? $transaction['title'] : 'N/A';
    }

    return $transactions;
}




public function showPendingTransactions($user_id = null)
{
    $pendingTransactions = $this->getPendingTransactions($user_id);

    if (empty($pendingTransactions)) {
        log_message('debug', 'No pending transactions found.');
    } else {
        log_message('debug', 'Pending Transactions: ' . print_r($pendingTransactions, true));
    }

    return view('admin/approve_reject_transactions', ['pendingTransactions' => $pendingTransactions]);
}




<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Approve or Reject Transactions</title>
    <!-- Bootstrap CSS -->
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" rel="stylesheet">
    <!-- SweetAlert CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/sweetalert2@10/dist/sweetalert2.min.css">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f7fc;
            margin: 0;
            padding: 0;
        }

        .container {
            width: 80%;
            margin: 20px auto;
            background-color: #fff;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
        }

        h2 {
            text-align: center;
            color: #333;
            margin-bottom: 20px;
        }

        .alert {
            padding: 15px;
            margin-bottom: 20px;
            background-color: #4CAF50;
            color: white;
            border-radius: 5px;
        }

        .table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }

        .table th, .table td {
            padding: 12px;
            text-align: left;
            border: 1px solid #ddd;
        }

        .table th {
            background-color: #007BFF;
            color: white;
        }

        .table tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        .btn {
            padding: 8px 16px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 14px;
            cursor: pointer;
            border-radius: 4px;
        }

        .btn-success {
            background-color: #28a745;
            color: white;
            border: none;
        }

        .btn-danger {
            background-color: #dc3545;
            color: white;
            border: none;
        }

        .btn:hover {
            opacity: 0.8;
        }

        form input[type="date"] {
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }

        form button {
            margin-left: 10px;
        }

        .actions {
            display: flex;
            justify-content: space-around;
            align-items: center;
        }

        .actions form {
            margin-right: 10px;
        }
    </style>
</head>

<body>
    <!-- Include the Navbar -->
    <?= $this->include('layout/navbar'); ?>

    <div class="container mt-5">
        <h2>Pending Borrowed Books</h2>

        <?php if (session()->getFlashdata('message')): ?>
            <div class="alert"><?= session()->getFlashdata('message') ?></div>
        <?php endif; ?>

        


        <table class="table">
            <thead>
                <tr>
                    <th>Transaction ID</th>
                    <th>Borrower Name</th>
                    <th>Book Title</th>
                    <th>Borrowed Date</th>
                    <th>Due Date</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
    <?php if (!empty($pendingTransactions)): ?>
        <?php foreach ($pendingTransactions as $transaction): ?>
            <tr>
                <td><?= ($transaction['transaction_id']) ?></td>
                            <td>
                <?= esc($transaction['firstname'] ?? 'Unknown') ?> 
                <?= esc($transaction['lastname'] ?? '') ?>
            </td>
            <td><?= esc($transaction['title'] ?? 'No Title') ?></td>
                <td><?= esc($transaction['borrow_date']) ?></td>
                <td>
                    <input type="date" value="<?= esc($transaction['due_date']) ?>" disabled>
                </td>
                <td><?= esc($transaction['status']) ?></td>
                <td class="actions">
                    <form action="<?= site_url('admin/approveTransaction/' . $transaction['transaction_id']) ?>" method="POST">
                        <input type="date" name="due_date" value="<?= esc($transaction['due_date']) ?>" required>
                        <button type="submit" class="btn btn-success">Approve</button>
                    </form>
                    <a href="<?= site_url('admin/rejectTransaction/' . $transaction['transaction_id']) ?>" class="btn btn-danger">Reject</a>
                </td>
            </tr>
        <?php endforeach; ?>
    <?php else: ?>
        <tr>
            <td colspan="7">No pending transactions.</td>
        </tr>
    <?php endif; ?>
</tbody>



        </table>
    </div>

    <!-- jQuery, Bootstrap JS, SweetAlert JS -->
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@10"></script>

    <script>
        // Add SweetAlert for better user experience on approve/reject actions
        $('.btn-danger').on('click', function (e) {
            e.preventDefault();
            const href = $(this).attr('href');

            Swal.fire({
                title: 'Are you sure?',
                text: "Do you want to reject this transaction?",
                icon: 'warning',
                showCancelButton: true,
                confirmButtonColor: '#dc3545',
                cancelButtonColor: '#6c757d',
                confirmButtonText: 'Yes, reject it!'
            }).then((result) => {
                if (result.isConfirmed) {
                    window.location.href = href;
                }
            });
        });
    </script>
</body>

</html>
2

From your provided code and details, the issue of “Unknown” names and “No Title” for books likely originates from your database query or data handling. Let’s troubleshoot this step by step.

Debugging Steps:

1. Verify Database Schema

  • Ensure the users and books tables contain valid data.
  • The transactions table should have valid user_id and book_id values.

2. Verify Relationships

  • Check that users.user_id matches transactions.user_id.
  • Check that books.book_id matches transactions.book_id.

3. Inspect the SQL Query

  • Enable query logging in CodeIgniter (already done using log_message).
  • Check the generated SQL query ($builder->getCompiledSelect()) in the debug logs.
  • Run the query directly in your database to see if it returns correct results.

4. Default Values Logic

  • You handle NULL values in the loop:
$transaction['firstname'] = !empty($transaction['firstname']) ? $transaction['firstname'] : 'Unknown';

This works only if firstname is returned as NULL. If the LEFT JOIN fails, the whole record might not join properly.

5. Fixing the Joins

  • Your joins look correct, but try explicitly aliasing columns to avoid conflicts:
->select([
    'transactions.transaction_id',
    'transactions.user_id',
    'transactions.book_id',
    'transactions.borrow_date',
    'transactions.due_date',
    'transactions.status',
    'users.firstname AS user_firstname',
    'users.lastname AS user_lastname',
    'books.title AS book_title'
])

6. Update the Data Loop

  • Adjust the default logic with aliases:
foreach ($transactions as &$transaction) {
    $transaction['user_firstname'] = !empty($transaction['user_firstname']) ? $transaction['user_firstname'] : 'Unknown';
    $transaction['user_lastname'] = !empty($transaction['user_lastname']) ? $transaction['user_lastname'] : '';
    $transaction['book_title'] = !empty($transaction['book_title']) ? $transaction['book_title'] : 'No Title';
}

7. Check View Logic

  • Ensure you use the correct keys in the view:
<td>
    <?= esc($transaction['user_firstname'] ?? 'Unknown') ?> 
    <?= esc($transaction['user_lastname'] ?? '') ?>
</td>
<td><?= esc($transaction['book_title'] ?? 'No Title') ?></td>

8. Validate Test Data

  • Insert a test transaction, ensuring the user_id and book_id fields are linked to existing records in users and books.

Updated Query Function:

public function getPendingTransactions($user_id = null)
{
    $builder = $this->db->table('transactions')
        ->select([
            'transactions.transaction_id',
            'transactions.user_id',
            'transactions.book_id',
            'transactions.borrow_date',
            'transactions.due_date',
            'transactions.status',
            'users.firstname AS user_firstname',
            'users.lastname AS user_lastname',
            'books.title AS book_title'
        ])
        ->join('users', 'users.user_id = transactions.user_id', 'left')
        ->join('books', 'books.book_id = transactions.book_id', 'left')
        ->where('transactions.status', 'pending');

    if ($user_id !== null) {
        $builder->where('transactions.user_id', $user_id);
    }

    log_message('debug', 'SQL Query: ' . $builder->getCompiledSelect());

    $transactions = $builder->get()->getResultArray();

    foreach ($transactions as &$transaction) {
        $transaction['user_firstname'] = !empty($transaction['user_firstname']) ? $transaction['user_firstname'] : 'Unknown';
        $transaction['user_lastname'] = !empty($transaction['user_lastname']) ? $transaction['user_lastname'] : '';
        $transaction['book_title'] = !empty($transaction['book_title']) ? $transaction['book_title'] : 'No Title';
    }

    return $transactions;
}

Updated View Snippet:

<tbody>
    <?php if (!empty($pendingTransactions)): ?>
        <?php foreach ($pendingTransactions as $transaction): ?>
            <tr>
                <td><?= esc($transaction['transaction_id']) ?></td>
                <td>
                    <?= esc($transaction['user_firstname'] ?? 'Unknown') ?> 
                    <?= esc($transaction['user_lastname'] ?? '') ?>
                </td>
                <td><?= esc($transaction['book_title'] ?? 'No Title') ?></td>
                <td><?= esc($transaction['borrow_date']) ?></td>
                <td>
                    <input type="date" value="<?= esc($transaction['due_date']) ?>" disabled>
                </td>
                <td><?= esc($transaction['status']) ?></td>
                <td class="actions">
                    <form action="<?= site_url('admin/approveTransaction/' . $transaction['transaction_id']) ?>" method="POST">
                        <input type="date" name="due_date" value="<?= esc($transaction['due_date']) ?>" required>
                        <button type="submit" class="btn btn-success">Approve</button>
                    </form>
                    <a href="<?= site_url('admin/rejectTransaction/' . $transaction['transaction_id']) ?>" class="btn btn-danger">Reject</a>
                </td>
            </tr>
        <?php endforeach; ?>
    <?php else: ?>
        <tr>
            <td colspan="7">No pending transactions.</td>
        </tr>
    <?php endif; ?>
</tbody>

Let me know if the issue persists!