Jump to content

Unable to pull data from database table

portabletelly

By portabletelly
in PHP Coding Help

 Share

Recommended Posts

portabletelly Member

portabletelly

Posted
Posted

 

When I click on my All Renewals button it filters all the renewals in the database and puts a button on the end called view details.

image.thumb.png.24d8af179e7fe2b20935834942e85cd8.png

Then PHP pulls all the details from the database and display the renewal details like below.

 

image.thumb.png.7695a9004b297cb67b0e0e0a446d0531.png

 

I then proceed to click update Notes and I get a Text box when it should be showing the current notes in the database "Maintenance contract for firewall support"

as well as adding new notes. Both are not working and I spent a whole day on it trying to work it out.

image.thumb.png.483b53e6dd9917fa36a56f786d627c01.png

 

This is my Renewals.php file  

<?php
session_start();
include 'Functions/Common_Functions.php';  // Includes menu_items function
include 'Functions/functions_renewals.php';  // Includes all renewal button functions
include 'Functions/db_con.php';  // Database connection

// Display main menu
func_header();
menu_items();

// Start the main container
echo "<div class='main-container'>";

// Left-side menu
echo "<div class='side-menu'>";
echo "<h4 style='color: white; text-align: center;'>Renewals</h4>";
renderRenewalButtons();
echo "</div>";

// Right-side content
echo "<div class='right-content'>"; 
echo "<h2>All Renewals</h2>";  

if (isset($_GET['filter']) && $_GET['filter'] === 'allrenewals') {
    $query = "SELECT * FROM Renewals";
    $result = $pdo->query($query);

    echo "<table class='table table-bordered'>"; 
    echo "<tr>
            <th>Company Name</th>
            <th>Service Name</th>
            <th>Renewal Date</th>
            <th>Status</th>
            <th>Notes</th>
            <th>Action</th>
          </tr>";

    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['Customer_ID']) . "</td>"; // Replace with actual company name if available
        echo "<td>" . htmlspecialchars($row['Service_Name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Renewal_Date']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Status']) . "</td>";
        echo "<td>" . htmlspecialchars($row['Notes']) . "</td>";
        echo "<td><button class='view-details-btn' data-id='" . $row['Renewals_ID'] . "'>View Details</button></td>";
        echo "</tr>";
    }
    echo "</table>";
}

echo "<div id='renewal-details' style='display:none; padding: 20px; border: 1px solid #ddd; margin-top: 20px;'></div>";

echo "</div>"; // End right-side content
echo "</div>"; // End main container
?>
<!-- ✅ Update Notes Modal -->
<div id="updateNotesModal" style="display:none; position:fixed; top:50%; left:50%; transform:translate(-50%, -50%); background:white; padding:20px; border-radius:8px; box-shadow: 0px 4px 6px rgba(0,0,0,0.2);">
    <h3>Update Notes</h3>
    <p id="currentNotesText"></p>
    <textarea id="newNotesInput" rows="4" style="width:100%;"></textarea>
    <br><br>
    <button id="confirmUpdateNotes">Save Notes</button>
    <button onclick="closeNotesModal()">Cancel</button>
</div>
<script>
document.addEventListener("DOMContentLoaded", function() {
    console.log("✅ DOM fully loaded and parsed.");

    // Attach event listeners to all "View Details" buttons
    document.querySelectorAll(".view-details-btn").forEach(button => {
        button.addEventListener("click", function() {
            let renewalId = this.getAttribute("data-id");

            console.log("🔄 Fetching details for Renewal ID:", renewalId);

            fetch("get_renewal_details.php?renewal_id=" + renewalId)
                .then(response => response.text())
                .then(data => {
                    document.getElementById("renewal-details").innerHTML = data;
                    document.getElementById("renewal-details").style.display = "block";

                    console.log("✅ Renewal details loaded.");

                    // ✅ Reattach event listeners after loading new content
                    setTimeout(attachButtonEvents, 500);
                })
                .catch(error => console.error("❌ Error fetching renewal details:", error));
        });
    });
});

// ✅ Function to reattach events to dynamically loaded buttons
function attachButtonEvents() {
    console.log("🔄 Reattaching event listeners...");

    // Attach "Change Status" button event
    document.querySelectorAll(".change-status-btn").forEach(button => {
        button.addEventListener("click", function() {
            console.log("✅ Change Status button clicked.");
            openStatusModal(this);
        });
    });

    // Attach "Delete" button event
    document.querySelectorAll(".delete-btn").forEach(button => {
        button.addEventListener("click", function() {
            console.log("✅ Delete button clicked.");
            deleteRenewal(this);
        });
    });

    // Attach "Update Notes" button event
    document.querySelectorAll(".update-notes-btn").forEach(button => {
        button.addEventListener("click", function() {
            console.log("✅ Update Notes button clicked.");
            openNotesModal(this);
        });
    });;
}

// ✅ Open Change Status Modal
function openStatusModal(button) {
    let currentStatus = button.getAttribute("data-current-status");
    let renewalId = button.getAttribute("data-id");

    console.log("📢 Opening modal for Renewal ID:", renewalId, "Current Status:", currentStatus);
    
    document.getElementById("currentStatusText").innerText = currentStatus;
    document.getElementById("confirmChangeStatus").setAttribute("data-id", renewalId);
    
    document.getElementById("changeStatusModal").style.display = "block";
}

// ✅ Close Modal
function closeStatusModal() {
    document.getElementById("changeStatusModal").style.display = "none";
}

// ✅ Handle Changing Status (Auto-Refresh)
document.addEventListener("click", function(event) {
    if (event.target.id === "confirmChangeStatus") {
        let renewalId = event.target.getAttribute("data-id");
        let newStatus = document.getElementById("statusDropdown").value;

        console.log("⏳ Sending status change request...");

        fetch("change_status.php?renewal_id=" + renewalId + "&new_status=" + newStatus, { method: "GET" })
            .then(response => response.text())
            .then(data => {
                alert("✅ Status Updated: " + data);
                closeStatusModal();

                // ✅ Auto-refresh after status update
                setTimeout(() => {
                    location.reload();
                }, 1000); // Refresh after 1 second
            })
            .catch(error => {
                alert("❌ Error updating status: " + error);
            });
    }
});

// ✅ Delete function
function deleteRenewal(button) {
    let renewalId = button.getAttribute("data-id");
    if (confirm("⚠️ Are you sure you want to delete this renewal?")) {
        console.log("⏳ Deleting renewal...");

        fetch("delete_renewal.php?renewal_id=" + renewalId, { method: "GET" })
            .then(response => response.text())
            .then(data => {
                alert("✅ Server Response: " + data);
                document.getElementById("renewal-details").innerHTML = "<p style='color: green;'>Renewal successfully deleted.</p>";
            })
            .catch(error => {
                alert("❌ Error deleting renewal: " + error);
            });
    }
}

// ✅ Open Update Notes Modal
function openNotesModal(button) {
    let renewalId = button.getAttribute("data-id");
    let currentNotes = button.getAttribute("data-notes") || "No previous notes.";

    console.log("📢 Opening Notes Modal for Renewal ID:", renewalId);

    document.getElementById("currentNotesText").innerText = "Current Notes: " + currentNotes;
    document.getElementById("newNotesInput").value = ""; // Clear previous input
    document.getElementById("confirmUpdateNotes").setAttribute("data-id", renewalId);

    document.getElementById("updateNotesModal").style.display = "block";
}

// ✅ Close Notes Modal
function closeNotesModal() {
    document.getElementById("updateNotesModal").style.display = "none";
}

// ✅ Handle Updating Notes (Auto-Refresh)
document.addEventListener("click", function(event) {
    if (event.target.id === "confirmUpdateNotes") {
        let renewalId = event.target.getAttribute("data-id");
        let newNotes = document.getElementById("newNotesInput").value.trim();

        if (!newNotes) {
            alert("❌ Please enter new notes.");
            return;
        }

        console.log("⏳ Sending notes update request...");

        fetch("update_notes.php?renewal_id=" + renewalId + "&new_notes=" + encodeURIComponent(newNotes), { method: "GET" })
            .then(response => response.text())
            .then(data => {
                alert("✅ Notes Updated: " + data);
                closeNotesModal();

                // ✅ Auto-refresh after updating notes
                setTimeout(() => {
                    location.reload();
                }, 1000); // Refresh after 1 second
            })
            .catch(error => {
                alert("❌ Error updating notes: " + error);
            });
    }
});

</script>

This is my get_renewal_details.php file  

<?php
include 'Functions/db_con.php';  // Database connection

if (!isset($_GET['renewal_id'])) {
    die("<p style='color: red;'>Error: No Renewal ID received.</p>");
}

$renewal_id = intval($_GET['renewal_id']);

// Fetch renewal details with JOINs for status name and customer name
$stmt = $pdo->prepare("
    SELECT r.*, 
           rs.RenewalsStatusName, 
           c.CompanyName 
    FROM Renewals r
    LEFT JOIN RenewalsStatus rs ON r.Status = rs.RenewalsStatusID
    LEFT JOIN Customer c ON r.Customer_ID = c.CustomerID
    WHERE r.Renewals_ID = ?
");

$stmt->execute([$renewal_id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$row) {
    die("<p style='color: red;'>Error: No details found for Renewal ID: $renewal_id</p>");
}

// Display renewal details
echo "<h3>Renewal Details</h3>";
echo "<p><strong>Customer:</strong> " . htmlspecialchars($row['CompanyName'] ?? 'Unknown') . "</p>";
echo "<p><strong>Service Name:</strong> " . htmlspecialchars($row['Service_Name']) . "</p>";
echo "<p><strong>Service Type:</strong> " . htmlspecialchars($row['Service_Type']) . "</p>";
echo "<p><strong>Vendor:</strong> " . htmlspecialchars($row['Vendor']) . "</p>";
echo "<p><strong>Contract ID:</strong> " . htmlspecialchars($row['Contract_ID']) . "</p>";
echo "<p><strong>Start Date:</strong> " . htmlspecialchars($row['Start_Date']) . "</p>";
echo "<p><strong>End Date:</strong> " . htmlspecialchars($row['End_Date']) . "</p>";
echo "<p><strong>Renewal Date:</strong> " . htmlspecialchars($row['Renewal_Date']) . "</p>";
echo "<p><strong>Status:</strong> " . htmlspecialchars($row['RenewalsStatusName']) . "</p>";
echo "<p><strong>Recurrence:</strong> " . htmlspecialchars($row['Recurrence']) . "</p>";
echo "<p><strong>Amount:</strong> $" . number_format($row['Amount'], 2) . "</p>";
echo "<p><strong>Alert Days Before:</strong> " . htmlspecialchars($row['Alert_Days_Before']) . " days</p>";
echo "<p><strong>Last Alert Sent:</strong> " . ($row['Last_Alert_Sent'] ? htmlspecialchars($row['Last_Alert_Sent']) : "N/A") . "</p>";
echo "<p><strong>Notes:</strong> " . nl2br(htmlspecialchars($row['Notes'])) . "</p>";

// Buttons
echo "<div style='display: flex; justify-content: center; gap: 10px; margin-top: 20px;'>
        <button class='change-status-btn' 
                data-id='" . (int) $renewal_id . "' 
                data-current-status='" . htmlspecialchars($row['RenewalsStatusName'] ?? '', ENT_QUOTES, 'UTF-8') . "' 
                style='background-color: blue; color: white; padding: 10px; border: none; cursor: pointer;'>Change Status</button>

<button class='update-notes-btn' 
    data-id='" . $renewal_id . "' 
    data-notes='" . htmlspecialchars($row['Notes'] ?? '', ENT_QUOTES) . "' 
    style='background-color: green; color: white; padding: 10px; border: none; cursor: pointer;'>
    Update Notes
</button>

<p>DEBUG Notes: " . htmlspecialchars($row['Notes'] ?? 'NO NOTES', ENT_QUOTES) . "</p> <!-- Debugging -->



        <button class='delete-btn' 
                data-id='" . (int) $renewal_id . "' 
                style='background-color: red; color: white; padding: 10px; border: none; cursor: pointer;'>Delete</button>
      </div>";


// Status Change Modal
echo "<div id='changeStatusModal' style='display: none; position: fixed; top: 50%; left: 50%; transform: translate(-50%, -50%); background: white; padding: 20px; border: 1px solid #ddd; box-shadow: 0px 0px 10px rgba(0,0,0,0.3); z-index: 1000;'>
        <h3>Change Renewal Status</h3>
        <p>Current Status: <span id='currentStatusText'></span></p>
        <label for='statusDropdown'>Select New Status:</label>
        <select id='statusDropdown'>
            <option value='1'>Active</option>
            <option value='2'>Pending</option>
            <option value='3'>Expired</option>
            <option value='4'>Renewed</option>
            <option value='5'>Cancelled</option>
        </select>
        <br><br>
        <button id='confirmChangeStatus' style='background-color: blue; color: white; padding: 10px; border: none; cursor: pointer;'>Change Status</button>
        <button onclick='closeStatusModal()' style='background-color: gray; color: white; padding: 10px; border: none; cursor: pointer;'>Close</button>
    </div>";

// Notes Modal
echo "<div id='updateNotesModal' style='display: none; position: fixed; top: 50%; left: 50%; transform: translate(-50%, -50%); background: white; padding: 20px; border: 1px solid #ddd; box-shadow: 0px 0px 10px rgba(0,0,0,0.3); z-index: 1000; width: 400px;'>
        <h3>Update Renewal Notes</h3>
        
        <label for='existingNotes'><strong>Current Notes:</strong></label>
        <textarea id='existingNotes' readonly style='width: 100%; height: 100px; margin-bottom: 10px; background-color: #f0f0f0;'></textarea>
        
        <label for='newNotes'><strong>New Notes:</strong></label>
        <textarea id='newNotes' style='width: 100%; height: 100px;'></textarea>
        
        <br><br>
        <button id='confirmUpdateNotes' style='background-color: green; color: white; padding: 10px; border: none; cursor: pointer;'>Update</button>
        <button id='clearNotes' style='background-color: red; color: white; padding: 10px; border: none; cursor: pointer;'>Clear Notes</button>
        <button onclick='closeNotesModal()' style='background-color: gray; color: white; padding: 10px; border: none; cursor: pointer;'>Close</button>
    </div>";



?>

<script>
document.addEventListener("DOMContentLoaded", function() {
    console.log("✅ DOM fully loaded and parsed.");

    // ✅ Open Change Status Modal
    document.querySelectorAll(".change-status-btn").forEach(button => {
        button.addEventListener("click", function() {
            let renewalId = this.getAttribute("data-id");
            let currentStatus = this.getAttribute("data-current-status");

            document.getElementById("currentStatusText").innerText = currentStatus;
            document.getElementById("confirmChangeStatus").setAttribute("data-id", renewalId);
            document.getElementById("changeStatusModal").style.display = "block";
        });
    });

    // ✅ Change Status Action
    document.getElementById("confirmChangeStatus").addEventListener("click", function() {
        let renewalId = this.getAttribute("data-id");
        let newStatus = document.getElementById("statusDropdown").value;

        fetch("change_status.php?renewal_id=" + renewalId + "&new_status=" + newStatus, { method: "GET" })
        .then(response => response.text())
        .then(data => {
            alert("✅ Status Updated: " + data);
            closeStatusModal();
            setTimeout(() => { location.reload(); }, 1000);
        })
        .catch(error => {
            alert("❌ Error updating status: " + error);
        });
    });

    function closeStatusModal() {
        document.getElementById("changeStatusModal").style.display = "none";
    }

    // ✅ Open Update Notes Modal
    document.querySelectorAll(".update-notes-btn").forEach(button => {
        button.addEventListener("click", function () {
            let renewalId = this.getAttribute("data-id");
            let existingNotes = this.getAttribute("data-notes") || "";

            console.log("📝 Opening Update Notes modal...");
            console.log("📌 Renewal ID:", renewalId);
            console.log("📌 Existing Notes Data:", existingNotes); // Debugging

            // ✅ Ensure "Current Notes" field is found
            let existingNotesField = document.getElementById("existingNotes");
            let newNotesField = document.getElementById("newNotes");

            if (!existingNotesField || !newNotesField) {
                console.error("❌ ERROR: Notes text fields not found in the modal.");
                return;
            }

            // ✅ Directly Assign Notes Value
            existingNotesField.value = existingNotes;
            console.log("✅ Assigned Existing Notes to Text Field:", existingNotesField.value);

            // ✅ Clear the "New Notes" field
            newNotesField.value = "";

            // ✅ Store the renewal ID inside the update button
            document.getElementById("confirmUpdateNotes").setAttribute("data-id", renewalId);

            // ✅ Show the modal
            document.getElementById("updateNotesModal").style.display = "block";
        });
    });
	
    // ✅ Save Notes Button (Using POST)
     document.getElementById("confirmUpdateNotes").addEventListener("click", function () {
        let renewalId = this.getAttribute("data-id");
        let newNotes = document.getElementById("newNotes").value.trim();

        console.log("🔍 Checking new notes input:", newNotes);

        if (!renewalId) {
            alert("❌ Missing renewal ID.");
            return;
        }

        if (!newNotes || newNotes.length === 0) {
            alert("❌ Please enter new notes.");
            return;
        }

        console.log("🛠 Sending POST request with:", { renewal_id: renewalId, new_notes: newNotes });

        fetch("update_notes.php", {
            method: "POST",
            headers: { "Content-Type": "application/x-www-form-urlencoded" },
            body: "renewal_id=" + encodeURIComponent(renewalId) + "&new_notes=" + encodeURIComponent(newNotes)
        })
            .then(response => response.text())
            .then(data => {
                console.log("✅ Server response:", data);
                alert("✅ Notes Updated: " + data);
                closeNotesModal();
                setTimeout(() => { location.reload(); }, 1000);
            })
            .catch(error => {
                alert("❌ Error updating notes: " + error);
            });
    });

    function closeNotesModal() {
        document.getElementById("updateNotesModal").style.display = "none";
    }
});
</script>

and this is my update_notes.php file 

<?php
include 'Functions/db_con.php'; // Database connection

if (!isset($_GET['renewal_id']) || !isset($_GET['new_notes'])) {
    die("Error: Missing renewal ID or new notes.");
}

$renewal_id = intval($_GET['renewal_id']);
$new_notes = trim($_GET['new_notes']);

$stmt = $pdo->prepare("UPDATE Renewals SET Notes = ? WHERE Renewals_ID = ?");
if ($stmt->execute([$new_notes, $renewal_id])) {
    echo "Notes updated successfully.";
} else {
    echo "Error updating notes.";
}
?>

This is the section of code i think has the issue 

    // ✅ Save Notes Button (Using POST)
     document.getElementById("confirmUpdateNotes").addEventListener("click", function () {
        let renewalId = this.getAttribute("data-id");
        let newNotes = document.getElementById("newNotes").value.trim();

        console.log("🔍 Checking new notes input:", newNotes);

        if (!renewalId) {
            alert("❌ Missing renewal ID.");
            return;
        }

        if (!newNotes || newNotes.length === 0) {
            alert("❌ Please enter new notes.");
            return;
        }

and this is what comes up when i click update notes

image.png.1ffe6ee338dbc5b6c444f5e56cd2f15f.png

 

So two issues. 1. Current Notes data is not being pulled into text box. 2. not able to update notes in the database

 

Pressing F12 show the following when clicking on update image.thumb.png.c6df2409c3f137349fef3a4709cbc8e1.png

Please go easy on me on by no means a coder.

Link to comment
https://forums.phpfreaks.com/topic/326838-unable-to-pull-data-from-database-table/
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

the most immediate problem is you are reusing id="updateNotesModal" for two modals. the modal you see is the one defined in get_renewal_details.php, but the javascript code that's running when you click the "update notes" button is what is defined in Renewals.php and this doesn't match the elements in the modal that is displayed.

best suggestion is that get_renewal_details.php should only get and display the details. there should only be one id="updateNotesModal" modal defined, so there won't be any confusion about which one gets displayed and operated on.

mac_gyver Prolific Member

mac_gyver

Posted
Posted

after reviewing the code more, let me introduce you to 'event delegation'. this will let you simplify all the code for attaching events to the buttons. this works by attaching the event to a parent container, such as the div with class='right-content', that all the buttons will exist in, regardless of when they are created. you would then find which button has been clicked by testing an attribute value from the button, such as a class name. the code would look like - 

document.addEventListener("DOMContentLoaded", function() {
    console.log("✅ DOM fully loaded and parsed.");

	// use event delegation for dynamically added elements (buttons)
	// attach the event to a common parent element - class='right-content'
	const buttonWrapper = document.querySelector('.right-content');

	// add the click event to everything in the common element, now or in the future
	buttonWrapper.addEventListener('click', function (event) {

		// examine the className of the clicked element
		console.log('target class: ',event.target.className);
		
		switch(event.target.className)
		{
			case 'view-details-btn':
				view_details(event.target);
			break;
			case 'change-status-btn':
				openStatusModal(event.target);
			break;
			case 'update-notes-btn':
				openNotesModal(event.target);
			break;
			case 'delete-btn':
				deleteRenewal(event.target);
			break;
			case 'closeModal':
				document.getElementById(event.target.getAttribute("data-modal-id")).style.display = "none";
			break;
			case 'confirmChangeStatus':
				confirmChangeStatus(event.target);
			break;
			case 'confirmUpdateNotes':
				confirmUpdateNotes(event.target);
			break;
		}
	});
});

 

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.