portabletelly

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. Then PHP pulls all the details from the database and display the renewal details like below. 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. 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 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 Please go easy on me on by no means a coder.

Ok ill try and explain this as best i can. I have client.php which includes a file called 'Functions/Common_Functions.php'; on this clients.php page it has a form, the form is a mixture of html and script. include 'Functions/Common_Functions.php'; func_header(); header_client_search(); func_endheader(); func_TopMenu(); menu_items(); ?> <br><br> <form method="post" action=<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>> <?php if (isset($_GET['error'])) { ?> <p class="error"><?php echo $_GET['error']; ?></p> <?php } client_search(); client_search_script(); // $clientSearch = ($_POST['clientSearch']); ?> <input type="submit" value="Search"> </form> <?php // Check if the form is submitted if ($_SERVER["REQUEST_METHOD"] == "POST") { // Process form data if needed echo "formposted"; echo $clientSearch; $clientSearch = isset($_POST['clientSearch']) ? $_POST['clientSearch'] : ''; echo "Form posted with clientSearch value: " . htmlspecialchars($clientSearch); echo $clientSearch; } The two functions client_search(); and client_Search_script(); go out and query mysql clients table so as you start typing a clients name the options get smaller "suggestive text". This part is working well. However when I submit the form for the life of me I cannot get the clients name captured on submit. This is the script code located in common_functions.php function header_client_search(){ echo "<style>"; echo " #suggestions {"; echo " position: absolute;"; echo " max-height: 200px;"; echo " overflow-y: auto;"; echo " border: 1px solid #ccc;"; echo " background-color: #fff;"; echo "}"; echo ".suggestion {"; echo " padding: 8px;"; echo " cursor: pointer;"; echo " background-color: #cccccc;"; echo "}"; echo ".suggestion:hover {"; // echo " background-color: #f2f2f2;"; echo " background-color: #cccccc;"; echo " }"; echo "</style>"; } function client_search(){ echo "<h1 color=#00000>Client Search</h1>"; echo "<label for='clientSearch'>Search Clients:</label>"; echo "<input type='text' id='clientSearch' oninput='getSuggestions(this.value)'>"; echo "<div id='suggestions'></div>"; } function client_search_script(){ echo "<script>"; echo "function getSuggestions(query) {"; echo "if (query.length === 0) {"; echo "document.getElementById('suggestions').innerHTML = '';"; echo "return;"; echo " }"; //echo "// Make an asynchronous request to fetch suggestions"; echo "var xhttp = new XMLHttpRequest();"; echo "xhttp.onreadystatechange = function() {"; echo "if (this.readyState == 4 && this.status == 200) {"; echo "document.getElementById('suggestions').innerHTML = this.responseText;"; echo "}"; echo "};"; echo "xhttp.open('GET', 'get_suggestions.php?q=' + query, true);"; echo "xhttp.send();"; echo "}"; echo "function selectSuggestion(value) {"; echo "document.getElementById('clientSearch').value = value;"; echo "document.getElementById('suggestions').innerHTML = '';"; echo " }"; echo "</script>"; } and incase you need it this is the get_suggestions.php file <?php // Include your database connection logic here include('Functions/db_con.php'); // Get the search query from the URL parameter $q = $_GET['q']; // Prepare and execute the query to fetch client names based on the search query $query = "SELECT DISTINCT CompanyName FROM Customer WHERE CompanyName LIKE :query"; $stmt = $pdo->prepare($query); $stmt->bindValue(':query', '%' . $q . '%', PDO::PARAM_STR); $stmt->execute(); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); // Output the suggestions as a list foreach ($results as $row) { echo '<div class="suggestion" onclick="selectSuggestion(\'' . $row['CompanyName'] . '\')">' . $row['CompanyName'] . '</div>'; } ?> What I have tried to do is muck around with $clientSearch = isset($_POST['clientSearch']) ? $_POST['clientSearch'] : ''; echo "Form posted with clientSearch value: " . htmlspecialchars($clientSearch); and these $clientSearch = document.getElementById('clientSearch'); echo $clientSearch; but I can never get the customer name back after submitting form I just get client search value: blank like in the picture below. Where am I going wrong?

Please ignore above post about additional pages I jumped the gun I had to change the collum syncroID to be unique in mysql in order for this to work. Seems to work well.

Hmm, am I doing something wrong here. It appears that the following code just add new duplicate entries into the database. Clearing out the customer table, 9 pages of records are created on first run then an additional 9 pages total 18 pages on second run. <?php function Get_Customer() { set_time_limit(300); $customers = []; $totalPages = getTotalPages(); $page = 1; while ($page <= $totalPages) { $returnedCustomers = Customers($page); $customers = array_merge($customers, $returnedCustomers); $page++; } include('db_con.php'); //**************https://forums.phpfreaks.com/topic/317537-curl-rate-limits-and-time-outs-limit-rate-under-a-certain-amount-for-get-requests/ ************************* //updated syncroid //updated SQL query with duplicate key update $query = "INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE SyncroID = VALUES(SyncroID), CompanyName = VALUES(CompanyName), Address = VALUES(Address), City = VALUES(City), State = VALUES(State), Postcode = VALUES(Postcode)"; echo $query; try { $pdo->beginTransaction(); $stmt = $pdo->prepare($query); foreach ($customers as $customer) { if (!empty($customer['business_name'])) { //udpated Customer ID for where clause $stmt->execute([$customer['id'], $customer['business_name'], $customer['address'], $customer['city'], $customer['state'], $customer['zip']]); } } $pdo->commit(); } catch (\Throwable $e){ $pdo->rollback(); throw $e; } //******************************end of gizmola code *************************** return $customers; } function Customers($page = 1) { $url = "https://xxxxxxxxxxxxxxxxxxxxxmsp.com/api/v1/customers?sort=business_name&page=" . $page; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $customers = $data["customers"]; curl_close($cURL); return $customers; } function getTotalPages() { $url = "https://xxxxxxxxxxxxxxxxmsp.com/api/v1/customers"; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $totalPages = $data["meta"]["total_pages"]; curl_close($cURL); return $totalPages; } // Get_Customer(); // test /* echo "<br>"; echo $custname; echo " "; echo $customer["address"]; echo " "; echo $customer["city"]; echo ","; echo $customer["state"]; echo " "; echo $customer["zip"]; echo "<b>Customer ID:</b> "; echo " "; echo $customer["id"]; echo "<br>"; echo $query; echo "<br>"; */ //INSERT INTO `Customer` (`SyncroID`, `PrimaryContactID`, `CompanyName`, `Address`, `City`, `State`, `Postcode`, `Country`, `AccountStatus`, `UserName`, `Password_Hash`, `Password_Salt`, `Notes`, `BillingContactID`) //VALUES ('12321', NULL, 'test', NULL, NULL, 'QLD', NULL, 'Australia', 4, NULL, NULL, NULL, NULL, NULL); The $query variable returns INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE SyncroID = VALUES(SyncroID), CompanyName = VALUES(CompanyName), Address = VALUES(Address), City = VALUES(City), State = VALUES(State), Postcode = VALUES(Postcode) my primary key on the customer table is Customer_ID but I don't want duplicate entries based on Syncro_ID either.

So im trying to run a SQL query to insert data into a customer table. But I only want to insert date if the ID does not already exist. This code here works //************************************start of gizmola coe ************************* $query = "INSERT INTO Customer (SyncroID, CompanyName) VALUES (?, ?)"; try { $pdo->beginTransaction(); $stmt = $pdo->prepare($query); foreach ($customers as $customer) { if (!empty($customer['business_name'])) { $stmt->execute([$customer['id'], $customer['business_name']]); } } $pdo->commit(); } catch (\Throwable $e){ $pdo->rollback(); throw $e; } //******************************end of gizmola code *************************** However when I try to add the where clause in it just bails out on the below code, im pretty sure my where statement has bad syntax and then im not sure the execute is done right? //**************https://forums.phpfreaks.com/topic/317537-curl-rate-limits-and-time-outs-limit-rate-under-a-certain-amount-for-get-requests/ ************************* //updated syncroid $query = "INSERT INTO Customer (SyncroID, CompanyName, Address, City, State, Postcode) VALUES (?, ?, ?, ?, ?, ?) WHERE (SyncroID) != VALUES (?)"; try { $pdo->beginTransaction(); $stmt = $pdo->prepare($query); foreach ($customers as $customer) { if (!empty($customer['business_name'])) { //udpated Customer ID for where clause $stmt->execute([$customer['id'], $customer['business_name'], $customer['address'], $customer['city'], $customer['state'], $customer['zip'], [$customer['id']]); } } $pdo->commit(); } catch (\Throwable $e){ $pdo->rollback(); throw $e; } //******************************end of gizmola code ***************************

Just circling back to this. Finally got it working with gizmola's code thank you. This was the end result. Now i have to rewrite this code to add all the other fields in. Then after that is successful if the ID already exists in mysql then only update row if there's something different. I don't fully understand why this is faster and works though. Is it because it prepares the query outside of the for loop rather than inside the for loop and that's why it doesn't time out? <?php function Get_Customer() { set_time_limit(300); $customers = []; $totalPages = getTotalPages(); $page = 1; while ($page <= $totalPages) { $returnedCustomers = Customers($page); $customers = array_merge($customers, $returnedCustomers); $page++; } include('db_con.php'); //************************************start of gizmola coe ************************* $query = "INSERT INTO Customer (SyncroID, CompanyName) VALUES (?, ?)"; try { $pdo->beginTransaction(); $stmt = $pdo->prepare($query); foreach ($customers as $customer) { if (!empty($customer['business_name'])) { $stmt->execute([$customer['id'], $customer['business_name']]); } } $pdo->commit(); } catch (\Throwable $e){ $pdo->rollback(); throw $e; } //******************************end of gizmola code *************************** return $customers; } function Customers($page = 1) { $url = "https://xxxxxxxxxxxxxxxxxxxxxxxxxxmsp.com/api/v1/customers?sort=business_name&page=" . $page; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $customers = $data["customers"]; curl_close($cURL); return $customers; } function getTotalPages() { $url = "https://xxxxxxxxxxxxxxxmsp.com/api/v1/customers"; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $totalPages = $data["meta"]["total_pages"]; curl_close($cURL); return $totalPages; }

Barand yes your right the other 50% will go into a different table.

Thanks ill give that go . In Reply to gizmol the data below is additional data that I'll need to insert later after i can successfully insert just the ID and business name. I know that I'm currently not using but i hope to add it in later ill just comment it out for now. $customer["id"]; $customer["firstname"]; $customer["lastname"]; $customer["fullname"]; $customer["business_name"]; $customer["email"]; $customer["phone"]; $customer["mobile"]; $customer["address"]; $customer["city"]; $customer["state"]; $customer["zip"]; $customer["business_and_full_name"]; $customer["business_then_name"];

These are the execution and mem settings.

Continuing on with this thread the solutions was this, it returns a complete set of customer from all 6 pages. However <?php function Get_Customer() { set_time_limit(300); $customers = []; $totalPages = getTotalPages(); $page = 1; while ($page <= $totalPages) { $returnedCustomers = Customers($page); $customers = array_merge($customers, $returnedCustomers); $page++; } include('db_con.php'); //tedst // echo "Total Pages: " . $page . "<br>"; // echo "Total Customers: " . count($customers) . "<br>"; // loop through array of customer foreach ($customers as $customer) { $customer["id"]; $customer["firstname"]; $customer["lastname"]; $customer["fullname"]; $customer["business_name"]; $customer["email"]; $customer["phone"]; $customer["mobile"]; $customer["address"]; $customer["city"]; $customer["state"]; $customer["zip"]; $customer["business_and_full_name"]; $customer["business_then_name"]; if ($customer["business_name"] != "") { $custname = $customer["business_name"]; //Insert customer data into the customer table with syncroID and Company Name $query = "INSERT INTO Customer (SyncroID, CompanyName) VALUES ('" . $customer["id"] . "', '" . $customer["business_name"] ."');"; echo "<br>"; echo $query; echo "<br>"; //$stmt = $pdo->query($query); usleep(5000); } } return $customers; } function Customers($page = 1) { $url = "https://xxxxxxxxxxxxxxxxxxxxxxxxcom/api/v1/customers?sort=business_name&page=" . $page; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $customers = $data["customers"]; curl_close($cURL); return $customers; } function getTotalPages() { $url = "https://xxxxxxxxxxxxxxxxxsp.com/api/v1/customers"; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ "Content-Type: application/json", "Accept: application/json", "Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ]); $result = curl_exec($cURL); $data = json_decode($result, true); $totalPages = $data["meta"]["total_pages"]; curl_close($cURL); return $totalPages; } // Get_Customer(); // test /* echo "<br>"; echo $custname; echo " "; echo $customer["address"]; echo " "; echo $customer["city"]; echo ","; echo $customer["state"]; echo " "; echo $customer["zip"]; echo "<b>Customer ID:</b> "; echo " "; echo $customer["id"]; echo "<br>"; echo $query; echo "<br>"; */ //INSERT INTO `Customer` (`SyncroID`, `PrimaryContactID`, `CompanyName`, `Address`, `City`, `State`, `Postcode`, `Country`, `AccountStatus`, `UserName`, `Password_Hash`, `Password_Salt`, `Notes`, `BillingContactID`) //VALUES ('12321', NULL, 'test', NULL, NULL, 'QLD', NULL, 'Australia', 4, NULL, NULL, NULL, NULL, NULL); When I change this line of code to insert into my database //$stmt = $pdo->query($query); to $stmt = $pdo->query($query); I only get the first 80 rows before I get a gateway time out. I tried usleep in for and set_time_limit but they made no difference. Anyone got any ideas. I know the api is getting the full data set so I assume its some memory or time out limit.

Turns out API return was paginated. I was only receiving page 1 of 6.

So the situation is this I have a cloud based webapp that stores allot of our customer data. We are building in house CRM to manage a few things the app doesn't do. However I need to get and replace all the customer info from my subscription webapp via an api call to our inhouse crm mysql clients table. The first step was to do a curl get request. This code below seems to be 1/2 working it gets down to clients with the letter C. and then returns the page, I'm missing all clients from D to Z. How would I change this code to get all remaining pages from the curl get request? Also I need to keep my requests under 180 per second. <?php function Get_Customer() { $url = 'https://xxxxxxxxxxxxxxxxxx.xxxxxxmsp.com/api/v1/customers?sort=business_name'; $cURL = curl_init(); curl_setopt($cURL, CURLOPT_URL, $url); curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true); curl_setopt($cURL, CURLOPT_HTTPGET, true); curl_setopt($cURL, CURLOPT_HTTPHEADER, [ 'Content-Type: application/json', 'Accept: application/json', 'Authorization: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ]); $result = curl_exec($cURL); $data = json_decode($result, true); $customers = $data['customers']; // loop through array of customer foreach ($customers as $customer) { $customer['id']; $customer['firstname']; $customer['lastname']; $customer['fullname']; $customer['business_name']; $customer['email']; $customer['phone']; $customer['mobile']; $customer['business_and_full_name']; $customer['business_then_name']; echo $customer['business_name']; echo "<br>"; } curl_close($cURL); } ?>