Problem updating mysql db by collecting details from user on button click

MySQL database
#1

Hi, I’m a beginner in php. I have a table named ‘portfolio’ which I’m displaying row-wise using php, and for each row there’s an update button in the action column. On pressing it there’ll be an alert asking for input in single line where input details by user should be separated by ‘#’ delimiter. The data is collected properly and there’s connection to the DB too, but the MySQL query is not being executed. To be precise, control is not transferred to the if block having the MySQL query and hence query isn’t executed and db is not updated. This is my code (file is named uchk.php)-

<!DOCTYPE html>
<html>
<body>
<?php

$servername = "localhost";
$username = "....";
$password = "....";
$dbname = "sample";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Check if row update form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['upd'])) {
 echo "<script> alert('Row data received successfully!'); </script>";
  $rno = $_POST['rno'];
  $chk = $_POST['chk'];
  $upd = $_POST['upd'];
  $status = $_POST['status'];

  $sql = "UPDATE portfolio SET checked_by='$chk', updated_on='$upd', status='$status' WHERE relationship_number = '$rno'";

  if ($conn->query($sql) === TRUE) {
      echo "<script>alert('Row updated successfully!'); window.location.href = 'dashboard.php';</script>";
      exit;
  } else {
      echo "<script>alert('Error updating row: " . $conn->error . "');</script>";
  }
}

// Query to select all rows from the portfolio table
$sql = "SELECT * FROM portfolio ORDER BY updated_on";

// Execute the query
$result = $conn->query($sql);

// Check if there are any rows returned
if ($result->num_rows > 0) {
    // Start the HTML table
    echo "<table border='1' cellpadding='5' cellspacing='0'>";
    echo "<tr>";
    echo "<th>Relationship Name</th>";
    echo "<th>Relationship Number</th>";
    echo "<th>Portfolio Name</th>";
    echo "<th>Investment Banker</th>";
    echo "<th>Model Name</th>";
    echo "<th>Portfolio Value</th>";
    echo "<th>Created on</th>";
    echo "<th>Created by</th>";
    echo "<th>Updated on</th>";
    echo "<th>Checked by</th>";
    echo "<th>Previuos Rebalance Date</th>";
    echo "<th>Rebalance Open Date</th>";
    echo "<th>Status</th>";
    echo "<th>Action</th>";
    echo "</tr>";

    // Loop through the rows and display the data in table rows
    while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row["relationship_name"] . "</td>";
        echo "<td>" . $row["relationship_number"] . "</td>";
        echo "<td>" . $row["portfolio_name"] . "</td>";
        echo "<td>" . $row["investment_banker"] . "</td>";
        echo "<td>" . $row["model_name"] . "</td>";
        echo "<td>" . $row["portfolio_value"] . "</td>";
        echo "<td>" . $row["created_on"] . "</td>";
        echo "<td>" . $row["created_by"] . "</td>";
        echo "<td>" . $row["updated_on"] . "</td>";
        echo "<td>" . $row["checked_on"] . "</td>";
        echo "<td>" . $row["previous_rebalance_date"] . "</td>";
        echo "<td>" . $row["rebalance_open_date"] . "</td>";
        echo "<td>" . $row["status"] . "</td>";
        echo "<td>";
        echo "<button onclick=\"rowUpdateForm()\">Update</button>";
        echo "</td>";
        echo "</tr>";
    }

    // Close the table
    echo "</table>";
} else {
    // If no records exist, print a message
    echo "No records exist.";
}

// Close the database connection
$conn->close();
?>

<script>
function rowUpdateForm() {
  var delimiter = '#'; // Delimiter to separate input fields
  var updateForm = prompt("Enter details for updating the row separated by '" + delimiter + "':\n\n" +
    "Checked by:\n" +
    "Updated on (YYYY-MM-DD):\n" +
    "Relationship number:\n" +
    "Status:");

  if (updateForm !== null) {
    var details = updateForm.split(delimiter);

    var data = "chk=" + encodeURIComponent(details[0]) +
               "&upd=" + encodeURIComponent(details[1]) +
               "&rno=" + encodeURIComponent(details[2]) +
               "&status=" + encodeURIComponent(details[3]);

    var url = "uchk.php"; 

    // Create a new XMLHttpRequest
    var xhr = new XMLHttpRequest();
    xhr.open('POST', url, true);
    xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
    xhr.onreadystatechange = function () {
    if (xhr.readyState === 4) {
      console.log(xhr.responseText); // Log the response from the server
      if (xhr.status === 200) {
        alert('successful!');
        location.reload(); // Refresh the page to show the updated data
      } else {
        alert('Error: ' + xhr.responseText);
      }
    }
  };
xhr.send(data);

  }
}
</script>
</body>
</html>

Please help me fix the issue. Preferably I want relationship_number of that particular row to be passed automatically to the function instead of getting it from user.

#2

How exactly did you determine that the execution doesn’t take this path? When I tried your code it does attempt to execute the sql query.

It you came to this conclusion because you didn’t see an alert box in the browser with the ‘Row data received successfully!’ message, it is because you are using an ajax request and the response that the javascript receives in xhr.responseText is only being sent to the console log. Did you check the browser’s developer tools, console tab?

This is an extremely unfriendly User Interface (UI). Requiring a user to enter the values this way will result in typos, missing, and miss formatted values, especially since you don’t have server-side validation logic. The UI should contain an actual form field for each value, that are pre-populated with the existing data, so that the user only needs to make any changes and submit the data.

Next, you cannot just add an ajax request to an existing page. The response to the ajax request should either be a string or a json encoded object that the javascrpt code uses. You can return javascript, such as the alert box, but for it to operate, you need to put it into a html element on the page. Your current code is returning the entire html document as the response.

The code for any page should be laid out in this general order -

  1. initialization
  2. post method form processing
  3. get method business logic - get/produce data needed to display the page
  4. html document

If you are going to make an ajax request to the same page as the current web page, you would need to detect in the code if the request is an ajax request ($_SERVER[‘HTTP_X_REQUESTED_WITH’] will be set and it will contain the string ‘xmlhttprequest’), then setup the response you are going to return to the ajax code, output that response, and halt php code execution after step #2 on the above code organizational list.

Also, don’t put external, unknown, dynamic values directly into sql query statements, where an sql special characters in a value can break the sql query syntax, which is how sql injection is accomplished. Use a prepared query instead.

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service