0

I have a PHP based site that needs to pull some data from a mssql server hosted within the intranet in my office. I have written the sql to pull this data out and logically it works perfectly. During testing on the windows machine, my code returns all the data i require.

but, when i put the same query into php, using PHP DBO dblib, i get no return (ie false) which indicates there is some syntax issues. Now to answer any simple questions, i can connect no issues to the db, and many of my other queries work (from the same db, even the same table) but this specific slq doesn't seem to work.

Upon further investigation i have come to realize that there could be a limitation to 'php_mssql' in relation to 'bigint' data types. I had a read on php.net and found this it looks as if there is no predefined constant to handle 8bit int's.

just for clarification, this is the sql im trying to run:

SELECT dbo.Vehicle.VehicleID, dbo.Vehicle.CarNumber,
dbo.Driver.DriverID, dbo.Driver.DriverNumber, dbo.Driver.DriverName,
dbo.VehicleJobHistory.BookingID, dbo.VehicleJobHistory.PickupSuburb,
dbo.VehicleJobHistory.DestinationSuburb,
dbo.VehicleJobHistory.ReasonDispatchedToCar,
dbo.VehicleJobHistory.TimeJobRequired,
dbo.VehicleJobHistory.TimeCarAcceptedJob,
dbo.VehicleJobHistory.TimeCarPickedUp,
dbo.VehicleJobHistory.TimeCarCompletedJob,
dbo.VehicleJobHistory.KmToPickup, dbo.VehicleJobHistory.KmOfJob,
dbo.DispatchStatus.Alias 

FROM (dbo.tblDispatch  
RIGHT JOIN ((dbo.Vehicle  
LEFT JOIN dbo.VehicleJobHistory ON dbo.Vehicle.VehicleID =
dbo.VehicleJobHistory.VehicleID) LEFT JOIN dbo.Driver ON
dbo.VehicleJobHistory.DriverID = dbo.Driver.DriverID) ON
dbo.tblDispatch.BookingID = dbo.VehicleJobHistory.BookingID)  
LEFT JOIN dbo.DispatchStatus ON dbo.tblDispatch.StatusID =
dbo.DispatchStatus.DispatchStatusID


WHERE (dbo.Vehicle.VehicleID = ’$vid’) AND
(dbo.VehicleJobHistory.TimeJobRequired Between ‘$DateFrom’ AND
‘$DateTo’)

the issue is on the join using bookingID, which is defined on the sql server as 'bigint'

Basically i want to know if there is any work around available?

Mike
  • 511
  • 3
  • 10
  • 28
  • You should look into using [PDO Prepared Statements](http://php.net/manual/en/pdo.prepared-statements.php), as written your code is potentially susceptible to a SQL injection. – doublesharp Apr 08 '13 at 22:23
  • You also have "fancy" quotes (`‘` and `’`) around your variables and not single quotes (`'`), in your example. – doublesharp Apr 08 '13 at 22:25
  • the issue has nothing to do with the quotes within the sql, ive tried every different combination for this query, plus i have multiple other queries with joins working without issue. – Mike Apr 08 '13 at 22:34
  • i am also aware of the potential of sql injection attacks on this sql, i have intentionally left this sql easy to read so as to make it easier to answer my question. – Mike Apr 08 '13 at 22:35
  • I made a quick test and verified that you can query a bigint without any problems on PHP PDO DBLIB (created a tempdb table with a bigint field, inserted a bigint value and queried from PHP). Still, I had some issues with updates on PDO DBLIB due to the different Options that were set on PHP PDO and other clients (they differ even from php_mssql), at the end I ended up setting the same environment manually at each connection. – Capilé Apr 08 '13 at 23:03

0 Answers0