PHP DB - Weird ORDER BY problem with PHP an MS ACCESS

Author

Weird ORDER BY problem with PHP an MS ACCESS

Constantin Wolber

2006-03-23, 6:58 pm

Hi,

I searched quite a while for a solution but I just don't find one.

I use:
- MS ACCESS as the database (I know there are better solutions but I
have to use MS ACCESS 2002)
- PHP Version 5.0.5
- Windows 2000
- Apache/2.0.54

I've got the following query:

SELECT table1.col1,
table1.col2,
table2.col1,
table3.col1,
table3.col2
FROM (table1
LEFT OUTER JOIN table2
ON table1.fkcol=table2.idcol)
LEFT OUTER JOIN table3
ON table1.fkcol2=table3.idcol
ORDER BY table2.col1,table1.col2

This Query works fine. The php page lists the results corresponding to
the ORDER Statement.

Now the user can limit the result with a parameter so the SQL statemant
looks like:

SELECT table1.col1,
table1.col2,
table2.col1,
table3.col1,
table3.col2
FROM (table1
LEFT OUTER JOIN table2
ON table1.fkcol=table2.idcol)
LEFT OUTER JOIN table3
ON table1.fkcol2=table3.idcol
WHERE table2.idcol=id
ORDER BY table2.col1,table1.col2

The result isn't sorted anymore, I just don't know why this can happen.

I tried the exact same query in an ODBC program and in MS ACCESS. Both
times the result is sorted the correct way. Only in PHP the result isn't
sorted in anyway.

Has anybody an idea what might be the problem?

Greets

Constantin

Constantin Wolber

2006-03-23, 6:58 pm

Constantin Wolber wrote:
> SELECT table1.col1,
> table1.col2,
> table2.col1,
> table3.col1,
> table3.col2
> FROM (table1
> LEFT OUTER JOIN table2
> ON table1.fkcol=table2.idcol)
> LEFT OUTER JOIN table3
> ON table1.fkcol2=table3.idcol
> WHERE table2.idcol=id
> ORDER BY table2.col1,table1.col2
>
> The result isn't sorted anymore, I just don't know why this can happen.

Ok seems like te solution ist to change the where clause from
table2.idcol to table1.fkcol.

Don't really know what the difference is, but it works for me.

Sponsored Links