PHP odbc_exec not returning all rows

This simple query

SELECT 
    DBA.Goods.ID,  /* PK in the Goods table */
    DBA.Goods.Name,
    DBA.GoodsGroup.Name as GdGrp,
    DBA.InvoiceItem.Qty,
    DBA.InvoiceItem.ID,   /* PK in the InvoiceItem table */
    DBA.InvoiceItem.PrintName,
    DBA.InvoiceItem.Price,
    DBA.InvoiceItem.InvoiceID, /* PK in the Invoice table, a parent table of a sort */
    DBA.InvoiceItem.InvoiceItemOrder,
    DBA.InvoiceItem.ProcPrice,
FROM (
    DBA.Goods INNER JOIN DBA.InvoiceItem
        ON DBA.Goods.GoodsSk = DBA.InvoiceItem.GoodsSK
)
INNER JOIN DBA.GoodsGroup
    ON DBA.Goods.GroupID = DBA.GoodsGroup.ID
WHERE DBA.InvoiceItem.InvoiceID = $invoiceID /* ID of a specific invoice, used here to get to its contents, i.e. specific items within that invoice */

when run in DBExplorer (a DB tool for executing raw SQL queries), returns 5 rows (contents of an invoice). Everything is setup in such a way that an invoice may contain multiple goods of the same ID (but with different prices and procurement prices, printing names – e.g. bucket-white, bucket-blue, bucket-red, etc). Each of the items gets a unique ID within the Items table. The aforementioned 5 rows are the expected result (i.e. the goods in the list are the actual goods sold).

However, when the query is sent to the database via:

$result = odbc_exec($conn,$query);

odbc_num_rows($results) is not 5, as expected and seen in DBExplorer, but 3 – the (percieved) duplicates are not returned (of all the buckets in the previous example, only one is returned).

Furthermore, while looping through the results with

while($rowItems = odbc_fetch_object($result) {
    ...
}

there are only 3 iterations, instead of the expected 5.

Thinking that the column order might be causing problems (silly, I know), I moved the DBA.InvoiceItem.ID to the top, making it the first column in the result set. However, that did nothing.

Why is this happening, and how can I make it return the full result set?

Note

I am aware that there are similar questions on SO:

php odbc_exec not returning all results

why doesn’t this code return all rows?

Not getting all rows from a query with odbc_exec in php

but none of them actually address the issue presented here – how to get ALL of the rows as seen in the DB tool, instead of a reduced result set.

Answer

The trouble was with the query. As soon as I’d changed it by changing the joining order, everything worked as expected. The key was to make DBA.InvoiceItem the main table, and join everything with it. I still have no (clear) idea why this worked.

Advertisement

Answer