{PHP connecting with MSSQL server

Get Started
1

I am writing this Query and it gives me error please check this (I am connecting MSSQL with ODBC connection)

$query ='Select sh.[No_], re.workorder, sl.[No_] As Partnumber, sl.[Qty_ to Ship] AS "quantity", sl.[Shipment Date]  AS "shipdate", porl.Description, porl.[Starting Date], DATEDIFF(day, porl.[Starting Date], GETDATE()) AS daydiff from PROD.dbo.[Shimco$Sales Header] sh LEFT JOIN PROD.dbo.[Shimco$Sales Line] sl ON sh.[No_] = sl.[Document No_] LEFT JOIN (Select A.[Source ID] as saleorder ,B.[Source ID] AS workorder, ROW_NUMBER() OVER (ORDER BY A.[Entry No_]) AS RowNumber From PROD.dbo.[Shimco$Reservation Entry] A inner Join PROD.dbo.[Shimco$Reservation Entry] B On A.[Entry No_] = B.[Entry No_] and A.[Source ID] != B.[Source ID]) re ON sh.[No_] = re.saleorder INNER JOIN PROD.dbo.[Shimco$Prod_ Order Routing Line] porl ON re.workorder = porl.[Prod_ Order No_] WHERE porl.Description = Shear and sl.[Line No_]=10000 and DATEDIFF(day, porl.[Starting Date], GETDATE()) = 0 and porl.[Routing Status] = 0';

Error is like **Warning: odbc_exec(): SQL error: [Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid column name ‘Shear’.,

Any idea

2

Strings need to be enclosed in quotes:

porl.Description = "Shear"

otherwise the DB engine will be looking for a column called Shear, hence the error msg.

3

That is the problem when i write porl.Description = “Shear”
i get this error

Warning: odbc_exec(): SQL error: [Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid column name ‘Shear’., SQL state S0022 in
SQLExecDirect

All comparison works except this
Again i am fetching data from MSSQL2008 and displaying data with PHP5.5

4

Try using single quotes - you’ll have to escape them though:
porl.Description = 'Shear'

5

Do you have a field called “shear” in more then one of the tables involved?

btw, when writing queries (for any database server), try not to do it in one huge line, space it out and make use of the “leading commas” convention, below is the same query from the OP but spaced out to make it more readable.

Select
      sh.[No_]
    , re.workorder
    , sl.[No_] As Partnumber
    , sl.[Qty_ to Ship] AS "quantity"
    , sl.[Shipment Date]  AS "shipdate"
    , porl.Description, porl.[Starting Date]
    , DATEDIFF(day, porl.[Starting Date]
    , GETDATE()) AS daydiff
from
    PROD.dbo.[Shimco$Sales Header] sh
LEFT JOIN
    PROD.dbo.[Shimco$Sales Line] sl
        ON sh.[No_] = sl.[Document No_]
LEFT JOIN
    (
        Select
              A.[Source ID] as saleorder 
            , B.[Source ID] AS workorder
            , ROW_NUMBER() OVER (ORDER BY A.[Entry No_]
    ) AS RowNumberFrom PROD.dbo.[Shimco$Reservation Entry] A
inner Join
    PROD.dbo.[Shimco$Reservation Entry] B
        On A.[Entry No_] = B.[Entry No_]
    and
        A.[Source ID] != B.[Source ID]) re
            ON sh.[No_] = re.saleorder
INNER JOIN
    PROD.dbo.[Shimco$Prod_ Order Routing Line] porl
        ON re.workorder = porl.[Prod_ Order No_]
WHERE
    porl.Description = Shear and sl.[Line No_]=10000
and
    DATEDIFF(day, porl.[Starting Date], GETDATE()) = 0
and
    porl.[Routing Status] = 0';

I’ve not encountered the use of [ and ] in a query before, is that how the odbc extension handles prepared statements?

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.