roopurt18 Posted October 4, 2007 Share Posted October 4, 2007 There's a test server and live server. Test server is MySQL Server version: 5.0.27-standard. Live server is MySQL Server version: 4.1.22-standard-log. The PHP code on both servers is identical. A database was copied from the live server to the test server to troubleshoot a problem. The particular web page is supposed to pull information from the database and just create a table. On the live server a table is generated. On the test server a message is generated saying that no documents could be found. These documents are not stored on disk so that is not the problem. Test Server Query SELECT t.*, o.*, t.RevNum AS TrackRevNum FROM POOptions o, wsjob j, IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum WHERE o.SubName=sn.IBSSubName AND sn.WVSubName='BBP' AND o.JobCode=j.job_code AND j.pro_code='7795' Live Server Query SELECT t.*, o.*, t.RevNum AS TrackRevNum FROM POOptions o, wsjob j, IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum WHERE o.SubName=sn.IBSSubName AND sn.WVSubName='BBP' AND o.JobCode=j.job_code AND j.pro_code='7795' Both queries are identical. Copying either query into phpMyAdmin on the test server gives: #1054 - Unknown column 'o.PONum' in 'on clause' Copying either query into phpMyAdmin on the live server gives 74 rows. I've not checked that they're identical, but I'm pretty sure they are. All four of the tables involved in the query have the same number of records according to phpMyAdmin, although I've not checked that they're identical. Any thoughts? Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/ Share on other sites More sharing options...
roopurt18 Posted October 4, 2007 Author Share Posted October 4, 2007 The only thing I can think of is a bug [fix] in the MySQL 5.0.x client. Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-361919 Share on other sites More sharing options...
Barand Posted October 4, 2007 Share Posted October 4, 2007 i'd write it like this and avoid the mix of join syntaxes SELECT t.*, o.*, t.RevNum AS TrackRevNum FROM POOptions o INNER JOIN wsjob j ON o.JobCode=j.job_code INNER JOIN IBSSubName2WVSubName sn ON o.SubName=sn.IBSSubName LEFT JOIN DocTrackingPOs t ON t.PONum=o.PONum WHERE sn.WVSubName='BBP' AND j.pro_code='7795' Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-361949 Share on other sites More sharing options...
roopurt18 Posted October 4, 2007 Author Share Posted October 4, 2007 Well that seems to work. Do you know why it is that makes the difference? I only need enough information to research it further myself. Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-361980 Share on other sites More sharing options...
Barand Posted October 4, 2007 Share Posted October 4, 2007 The only reason I can think of is that it gets bewildered by the mix of join methods (some using ON and others using WHERE) I always prefer the ON syntax as it separates the join structure from the selection criteria and makes it easier to see what's going on. I can't find the reference now but I'm sure I once read that ON syntax is more efficient. Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-361985 Share on other sites More sharing options...
fenway Posted October 6, 2007 Share Posted October 6, 2007 Well that seems to work. Do you know why it is that makes the difference? I only need enough information to research it further myself. The comma operator changed precedence in mysql 5, and is now lower than JOIN; so FROM POOptions o, wsjob j, IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t is being interpreted as FROM POOptions o, wsjob j, ( IBSSubName2WVSubName sn LEFT JOIN DocTrackingPOs t ) which is very much different. Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-363259 Share on other sites More sharing options...
fenway Posted October 6, 2007 Share Posted October 6, 2007 bottom line -- never use the comma operator. it's ambiguous, cryptic, and confusing. Quote Link to comment https://forums.phpfreaks.com/topic/71853-this-one-beats-the-hell-out-of-me/#findComment-363260 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.