Increment The Variable $rows Inside a Loop in PHP

This PHP script processes a search query for user login data, allowing users to filter results by password or email. It fetches the matching rows from the database and displays them in a table, including options to edit or delete records. If no matches are found, it outputs “No Record Found.

PHP Code (with Errors):

code<?php 
// Check if the form is submitted
if (isset($_GET['submit'])) {
    
    // Retrieve and sanitize the search input
    $search = $_GET["search"];
    $search_safe = mysqli_real_escape_string($conn, $search);

    // Execute the SQL query using LIKE for password and email search
    $result = mysqli_query($conn, "SELECT * FROM login WHERE password LIKE '%$search_safe%' OR email LIKE '%$search_safe%'");

    // Initialize a counter for rows
    $rows_found = 0;

    // Loop through the result set and display the data
    while ($row = mysqli_fetch_array($result)) {
    ?>     
          <tr>
              <td><?php echo htmlspecialchars($row['email']); ?></td>
              <td><?php echo htmlspecialchars($row['password']); ?></td>
              <td>
                  <a href="edit.php?id=<?php echo urlencode($row['id']); ?>">Edit</a> , 
                  <a href="assets/php/del.php?id=<?php echo urlencode($row['id']); ?>">Del</a>
              </td>
          </tr>
    <?php
          $rows_found++;  // Increment row counter for each fetched row
    }

    // If no rows are found, display a message
    if ($rows_found == 0) {
        echo "No Record Found";
    }
}
?>

Key Changes and Fixes:

The LIKE query now uses wildcards (%) on both sides of the search term for a broader match.

SQL Injection Prevention:

Used mysqli_real_escape_string() to sanitize the input for preventing SQL injection.

This ensures that any special characters or malicious code in the $_GET['search'] input are escaped properly.

Correct Variable Name in Loop:

Changed $rows to $row inside the while loop because $rows was being used for counting the number of records, but $row should refer to the current row of data fetched from the database.

Proper Increment:

The $rows++ is kept to count the number of rows found, but it’s outside the mysqli_fetch_array assignment to avoid overwriting data.

Escaping Output:

Used htmlspecialchars() for displaying sensitive data such as email and password, to prevent Cross-Site Scripting (XSS) attacks.

Fixed URL Parameters:

Used urlencode() to safely encode the URL parameters, preventing any malformed links.

Fixed SQL Query Logic:

Corrected PHP Code:

code<?php
// Check if the form has been submitted
if (isset($_GET['submit'])) {
    // Retrieve the search input from the form
    $search = $_GET['search'];

    // Execute the SQL query to search for the email or password in the login table
    $result = mysqli_query($conn, "SELECT * FROM login WHERE password LIKE '%" . mysqli_real_escape_string($conn, $search) . "%' OR email LIKE '%" . mysqli_real_escape_string($conn, $search) . "%'");

    // Initialize a variable to track the number of rows
    $rows = 0;

    // Fetch the query results and display them in a table
    while ($row = mysqli_fetch_array($result)) {
        ?>
        <tr>
            <td><?php echo htmlspecialchars($row['email']); ?></td>
            <td><?php echo htmlspecialchars($row['password']); ?></td>
            <td>
                <a href="edit.php?id=<?php echo urlencode($row['id']); ?>">Edit</a> ,
                <a href="assets/php/del.php?id=<?php echo urlencode($row['id']); ?>">Del</a>
            </td>
        </tr>
        <?php
        $rows++;
    }

    // If no rows were found, display a message
    if ($rows == 0) {
        echo "No Record Found";
    }
}
?>

Explanation:

Preventing Common Errors in PHP Forms and SQL Queries

When developing web applications, ensuring security and functionality in PHP forms and SQL queries is essential. One common mistake developers encounter is handling user input in queries without proper sanitization, leading to vulnerabilities such as SQL Injection. Here’s how we can fix such issues and create a more secure and error-free script:

1. SQL Injection Prevention: SQL injection occurs when a hacker injects malicious SQL code into a form field. To prevent this, we must sanitize user inputs before using them in a database query. Functions like mysqli_real_escape_string() in PHP ensure that special characters in input are properly escaped, which prevents harmful SQL code from being executed.
2. Using Proper Loop Variables: In loops that process data from a database, it’s important to ensure that the variables inside the loop are correctly named. For example, in a while loop fetching results from mysqli_fetch_array(), we should use $row to represent each fetched row, not $rows, which we use for counting the total number of results.
3. Escaping Output for Security: When outputting data (such as user-provided email or password) on a webpage, it’s crucial to escape the data using htmlspecialchars(). This prevents XSS attacks, where an attacker could inject harmful scripts into your website by providing malicious data.
4. Handling URLs Safely: When generating URLs dynamically with query parameters, functions like urlencode() ensure that special characters are safely encoded, preventing issues with malformed URLs.

By incorporating these best practices, developers can create more secure, reliable, and maintainable code. Always remember to test your scripts for security vulnerabilities, especially when dealing with user input and database operations.