Fixing Nested Loops and Deprecation Issues in PHP Database Fetching
The core issue in your current code lies in the logic of your loops. You are collecting every row into an array inside a while loop, which is unnecessary for rendering a table immediately. More critically, you are nesting a foreach loop inside that same while loop. This structure causes the inner foreach to execute repeatedly for every single database record fetched in the outer loop. As a result, your table rows multiply exponentially with every query execution, leading to duplicate data and infinite loops if not handled carefully.
To resolve this, you should iterate through the results directly using the while loop, assigning each row to a variable as it is fetched. This approach ensures that each database record corresponds to exactly one iteration of your rendering logic. Since you are building a table where each row represents a distinct project, there is no need to collect all data into an array first unless you plan to perform complex aggregation operations later.
<table class="table table-striped table-condensed" id="tblData">
<thead>
<tr>
<th>Project Name</th>
<th>Project Number</th>
<th>Project Status</th>
</tr>
</thead>
<tbody>
<?php
// Fetch the first record to check if data exists
$records = mysql_query('SELECT project_num, project_status, project_name FROM tblprojects');
if ($records && mysqli_num_rows($records) > 0) {
while ($row = mysqli_fetch_assoc($records)) {
?>
<tr>
<td><?php echo htmlspecialchars($row['project_name']); ?></td>
<td><?php echo htmlspecialchars($row['project_num']); ?></td>
<td><?php echo htmlspecialchars($row['project_status']); ?></td>
</tr>
<?php
}
} else {
// Handle the case where no records are found to prevent empty tables or errors
?>
<tr>
<td colspan="3">No projects found.</td>
</tr>
<?php
}
// Close the connection
mysqli_free_result($records);
?>
</tbody>
</table>
Beyond fixing the loop logic, you must address the deprecation of the mysql_* functions. The functions starting with mysql_, such as mysql_query and mysql_fetch_assoc, were deprecated in PHP 5.5 and completely removed in PHP 7.0. Relying on them will cause fatal errors if you upgrade your version of PHP, which is highly recommended for security and performance reasons. The modern standard is to use the mysqli_* extension or PDO (PHP Data Objects).
In the corrected example above, I have switched to mysqli_query and mysqli_fetch_assoc. This change ensures compatibility with modern PHP versions. Additionally, I included htmlspecialchars() when echoing data from the database. This is a critical security practice that prevents Cross-Site Scripting (XSS) vulnerabilities by escaping special characters like <, >, and quotes in user-generated content before rendering them in HTML.
When dealing with database columns, it is easy to assume names match perfectly. In your original code, you selected project_name but tried to access $row['proj_name']. Always ensure the keys in your associative array match the column names returned by the query exactly. If your database uses underscores (e.g., project_num) but your application expects camelCase (e.g., projectNum), you will need an additional step to rename the keys after fetching, though selecting the correct columns directly is cleaner.
For larger applications, especially those moving towards modern frameworks like Laravel, manual connection handling often gives way to database migrations and Eloquent ORM models. In a Laravel environment, you would typically define a model for Project with fields matching your table (project_num, project_status, project_name). The query would then be replaced by a simple $projects = Project::all(); statement. This abstraction layer handles connection management, row fetching, and escaping automatically, reducing the risk of syntax errors and security flaws.
If you are maintaining legacy code that must run on older PHP versions while still avoiding deprecated functions, consider using PDO with prepared statements. PDO provides a consistent interface for both mysql_* and mysqli_* functionality and is generally preferred for new development due to its robustness against SQL injection attacks.