CODEWITHSUNDEEP
phpmysqlxampp
Wes Wakeman
Wes Wakeman

Reputation: 31

PHP PDO - MySQL Query brings back different results on two servers

Hi I have the following query:

 select t.* 
 from (SELECT * 
       FROM Matches 
       where HID=:ID or AID=:ID 
       order by Date desc 
       limit 10
 ) t 
 order by t.Date, t.Time asc

as part of a PDO prepared statement I have two websites running one on ubuntu and one using Xampp on windows 7 the data is the same however on the ubuntu box it comes back the the last 10 games played in date then time order. On the windows box the game (first in list) of the ten is actually an earlier game (2 games were played on the same night one at 6:15pm the other at 7:00pm) the latter should be shown but isn't on the windows box. Which seems a little strange. Just wanted to know if anyone else had ever come across this at all?

Upvotes: 0

Views: 123

Answers (2)

Andrew
Andrew

Reputation: 1866

Data contains two games in one date but with different time. If you are not sorting games by time, then rows order is not determined. Without ORDER BY rows can appear at any place. In most cases first inserted into table rows will be first in output. But this is not documented behavior and you must not use it. No implicit sorting by time will be applied to result in your case. Today you will get 7:00pm at first row, tomorrow it can be 6:15pm.

In order to receive same results on different machines, you must do right sort with no unrelated behavior. Just add sorting by time in subquery and you will always get same result on any MySQL instance with same data.

SELECT t.* FROM (
  SELECT *
  FROM Matches
  WHERE HID = :ID OR AID = :ID
  ORDER BY `Date` DESC, `Time` DESC
  LIMIT 10
) t
ORDER BY t.Date, t.Time

This will return real 10 last games.

Upvotes: 1

Loek
Loek

Reputation: 4135

Do you use different MySQL installations? I can imagine different installations handling this differently.

Also, are you sure the 7:00 PM game record exists on your Windows box? Maybe something went wrong with pushing the record in your DB.

Upvotes: 0

Related Questions