The Silent Loop: Why Your PHP Query Returns Only One Row
You have a database table containing multiple records for companyid = 1, yet your PHP script returns data for only the first row. This is a classic logic error that often trips up developers migrating from legacy code or quickly prototyping an API. The issue isn't with your SQL query; in fact, the query itself is perfectly capable of returning all matching rows. The problem lies strictly within how you are iterating over the result set and building your response array.
The culprit is the scope of your variables inside a while loop. In your provided code snippet, you initialize the $product array to an empty state inside the loop:
while($row = mysql_fetch_assoc($result)){
$product = array();
$product["pid"] = $row["pid"];
$product["productname"] = $row["productname"];
}
Every time the loop executes, you create a fresh, empty $product array. You then populate it with the current row's data and immediately discard the previous content. When the loop finishes after processing the first row (or subsequent rows), the variable $product holds only the data from the very last iteration. However, because your logic inside the loop overwrites $product every single time, you effectively lose all intermediate data before you ever reach the point where you push it to the main response array.
Furthermore, even if the loop logic were corrected, there is a secondary flaw in how you populate $response["product"]. You initialize this array outside the loop but then attempt to array_push inside it. In your specific code structure shown, the push happens after the loop completes. Because of the variable shadowing issue described above, you are pushing a single object (the last one seen) rather than an accumulation of all rows.
To fix this, you must separate data construction from iteration logic. Initialize your accumulator array before the loop begins. Inside the loop, append each new row directly to that accumulator. Here is the corrected approach using mysql_fetch_assoc:
$response["product"] = []; // Initialize outside the loop
while($row = mysql_fetch_assoc($result)) {
$response["product"][] = [
"pid" => $row["pid"],
"productname" => $row["productname"]
];
}
By moving $response["product"] = [] outside the while block, you ensure that every iteration appends to the existing list rather than replacing it. Using the shorthand $array[] = ... syntax is generally cleaner and less error-prone than manually creating an index or using array_push.
It is also worth noting a significant security vulnerability in your SQL query: direct variable interpolation into the string (WHERE companyid = $companyid). This exposes your application to SQL injection attacks. You should always use prepared statements with bound parameters, such as those provided by PDO or the MySQLi extension. The deprecated mysql_* functions you are currently using have been removed from PHP core for years due to security and functionality issues.
If you are building a modern API, consider switching to Laravel. Its query builder and Eloquent ORM handle fetching multiple rows with syntax like ->get() which returns a collection of models, automatically serializing them correctly when returned in a JSON response via the framework's rendering engine. For more on securing your queries and adopting modern practices, resources like LaravelCompany offer guidance on transitioning from legacy MySQL extensions to secure, prepared-statement-based architectures.
Finally, remember that mysql_fetch_array behaves identically to mysql_fetch_assoc regarding this specific logic error. The function choice does not matter; the structural placement of your accumulator array is the deciding factor. Once you refactor the loop to accumulate results rather than overwrite them, your JSON response will correctly contain an array of all products for the given company ID.