Join two MySQL tables with PHP
You have a working setup where index.php passes an ID to citys.php, which retrieves data from a citys table. The logic is straightforward: fetch the city details and display them. However, you now need to enrich that view by including related comments stored in a separate comments table. This requires moving from a simple single-table query to a join operation.
The core issue lies in how the data is retrieved and iterated. Your initial attempt used an implicit join syntax (FROM citys, comments WHERE...). While this can work for simple cases, it often leads to Cartesian products if not handled with strict filtering conditions. More critically, you encountered a problem where iterating over the results with mysql_fetch_array() seemed to skip the first row when multiple comments existed.
The Join Logic
To connect citys and comments, you need to match records based on a common column. In your schema, the relationship is defined by the City name. The correct approach is to use an explicit INNER JOIN or LEFT JOIN.
Here is the corrected SQL query structure:
$sql = "SELECT * FROM citys
LEFT JOIN comments ON comments.city = citys.city
WHERE citys.id = ?";
Using a LEFT JOIN ensures that even if a city has no comments, the city data still appears in the results. If you use an INNER JOIN, cities without comments will be omitted entirely. Given your requirement to show the city details alongside available feedback, LEFT JOIN is generally safer.
Crucially, you must replace the direct variable interpolation (WHERE citys.id=$id) with a prepared statement or ensure the ID is sanitized. Directly inserting $_GET["id"] into the query string introduces severe SQL injection vulnerabilities. Since your source material uses mysql_* functions (which are deprecated), assume this is legacy code being migrated. In modern PHP, you would use PDO or MySQLi with prepared statements.
Handling Multiple Rows
The specific error you describedâskipping the first row in a loopâis almost certainly a logic error in how the HTML is generated after fetching the data, rather than a flaw in the SQL join itself.
When you run a join between citys and comments, and there are three comments for Dusseldorf, the result set will contain three distinct rows, one for each comment. The citys columns (ID, City, Country, Population) will repeat on these rows.
Your loop logic needs to account for this repetition. If you attempt to print $row['name'] inside a while loop, it will correctly iterate through all three comments. The only reason the first row would "disappear" is if there was an error in your previous code block that prevented the loop from starting, or if you were accidentally accessing a scalar variable instead of iterating the result set.
Consider this robust iteration pattern:
if ($result = mysql_query($sql)) {
while ($row = mysql_fetch_array($result)) {
echo "<br><br>";
// Display user data only if it exists in this row
if (!empty($row['name'])) {
echo $row['name'];
echo "<br>";
echo $row['comment'];
}
}
}
Using mysql_fetch_array() inside a while loop is the correct way to handle multiple rows. The query itself returns a multi-row result set, and the loop processes each iteration sequentially. There is no automatic skipping of the first record by MySQL; the behavior is entirely dependent on the PHP loop condition and how you output the data.
Modernizing the Stack
While fixing the join logic resolves your immediate display issue, relying on mysql_* functions is a critical technical debt. These extensions were deprecated in PHP 5.5 and removed in PHP 7.0. If you are building new features or refactoring legacy applications, you should migrate to PDO or MySQLi.
Transitioning to prepared statements not only fixes the SQL injection risk but also simplifies handling dynamic IDs:
$stmt = $pdo->prepare("SELECT * FROM citys LEFT JOIN comments ON comments.city = citys.city WHERE citys.id = :id");
$stmt->execute(['id' => $_GET['id']]);
$rows = $stmt->fetchAll();
With PDO, you can fetch all rows at once using fetchAll(), which returns an array of arrays. This makes it trivial to loop through and render the comments without worrying about fetching row-by-row logic errors. For modern Laravel projects, this pattern is encapsulated within Eloquent relationships, but understanding the raw SQL join remains essential for debugging and performance tuning.
If you are looking for resources on optimizing database queries or securing your PHP backend in a scalable architecture, check out the guides at https://laravelcompany.com.