doujia7779
2017-03-01 19:17
浏览 51
已采纳

MSSQL Query使用PHP代码返回NULL

I have a query which fetches all values in my SQL database but when I run it PHP, the query returns "NULL" and doesn't display anything. Can someone please help? I've attached my query and code.

SQL:

    <?php 
                $newResult = <<<SQL
                    SELECT  
a.transactionTime,
a.currentLocation AS LOCATION,
a.consignmentNumber,
A.TrackingStatus,
A.MapStatus

FROM  (
    SELECT ctv.consignmentNumber,
              ctv.currentLocation,
              ctv.stateID,
              mcts.TrackingStatus,
              ctv.transactionTime,
              mcts.sortorder,
              CASE 
                   WHEN mcts.TrackingStatus = 'NEW' THEN 
                        'Shipment Booked/Picked from Customer(s) Dated: ' +
                        CONVERT(VARCHAR(21), ctv.transactionTime, 105)
                   WHEN mcts.TrackingStatus = 'ARRIVAL' THEN 
                        'Reached at Origin HUB for Processing ' + CONVERT(VARCHAR(21), ctv.transactionTime, 105)
                   WHEN mcts.TrackingStatus IN ('MANIFESTED', 'BAGGED', 'LOADED') THEN 
                        'Processed at Origin HUB for onward Forwarding to Destination ' 
                        +
                        CONVERT(VARCHAR(21), ctv.transactionTime, 105)
                   WHEN mcts.TrackingStatus IN ('UNLOAD', 'DEBAG', 'DEMANIFEST') THEN 
                        'Reached at Destination HUB for onward Delivery ' +
                        CONVERT(VARCHAR(21), ctv.transactionTime, 105)
                   WHEN mcts.TrackingStatus = 'RUNSHEET' THEN 
                        'Service Delivery Officer is departed from Operations for attempting at address ' 
                        +
                        CONVERT(VARCHAR(21), ctv.transactionTime, 105)
                   WHEN mcts.TrackingStatus = 'POD' THEN (
                            SELECT CASE 
                                        WHEN rc.Reason = '123' THEN (
                                                 (
                                                     SELECT l.AttributeValue
                                                     FROM   rvdbo.Lookup l
                                                     WHERE  l.Id = rc.Reason
                                                 ) 
                                                 + ' and ' +
                                                 'Received by :' + rc.receivedBy 
                                                 + ' on ' + CONVERT(VARCHAR(21), rc.[time], 105)
                                             )
                                        ELSE (
                                                 SELECT l.AttributeValue +
                                                        '' + rc.Reason + rc.receivedBy
                                                 FROM   rvdbo.Lookup l
                                                 WHERE  l.Id = (CASE WHEN rc.Reason IS NULL THEN '' ELSE rc.Reason END)
                                             )
                                   END
                            FROM   runsheetconsignment rc,
                                   runsheet r1
                            WHERE  ctv.consignmentnumber = rc.consignmentnumber
                                   AND ctv.runsheetnumber = rc.runsheetnumber
                                   AND r1.runsheetnumber = rc.runsheetnumber
                                   AND r1.branchcode = rc.branchcode
                                   AND r1.routecode = rc.routecode
                                   AND r1.createdBy = rc.createdBy
                        )
              END MapStatus,
              CASE 
                   WHEN ctv.StateID = '1' THEN ISNULL(
                            (
                                SELECT +
                                       'Consignment No: ' 
                                       + c.consignmentNumber 
                                       +
                                       ' was booked on :' 
                                       +
                                       CONVERT(VARCHAR(11), c.createdOn, 106) 
                                       +
                                       ' by User :' +
                                       zu.Name +
                                       ' on Location :' 
                                       + ec.name
                                FROM   Consignment c,
                                       ZNI_USER1 zu,
                                       Branches b,
                                       ExpressCenters 
                                       ec
                                WHERE  CONVERT(NVARCHAR, c.createdby) = 
                                       CONVERT(NVARCHAR, zu.U_ID)
                                       AND zu.branchcode = b.branchCode
                                       AND zu.ExpressCenter = ec.expressCentercode
                                       AND RTRIM(LTRIM(c.consignmentNumber)) = 
                                           RTRIM(LTRIM(ctv.consignmentNumber))
                                    AND consigneraccountno not in ('4B45','7240','4H86','4H87','4H88','4B87','7240','4B45','4H91','4H89','4H90','4F47')
                            ),
                            'New'
                        )
                   WHEN ctv.StateID = '2' THEN (
                            SELECT +'Manifest No :' +
                                   c.manifestNumber 
                                   +
                                   ' was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   mnp_Manifest c,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND c.manifestNumber = ctv.manifestNumber
                        )
                   WHEN ctv.StateID = '3' THEN (
                            SELECT +'Bag No: ' + c.bagNumber 
                                   +
                                   ' and Seal No: ' +
                                   c.sealNo +
                                   ' was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   Bag c,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND c.bagNumber = ctv.bagNumber
                        )
                   WHEN ctv.StateID = '4' THEN (
                            SELECT +'Loading No :' +
                                   CONVERT(VARCHAR, c.id) 
                                   + ' and Seal No: ' 
                                   + c.sealNo 
                                   +
                                   +
                                   ' was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   MnP_Loading c,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND CONVERT(NVARCHAR, c.id) = CONVERT(NVARCHAR, ctv.loadingNumber)
                        )
                   WHEN ctv.StateID = '18' THEN (
                            SELECT + 'Arrival No :' +
                                   CONVERT(NVARCHAR, c.Id) 
                                   +
                                   ' was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   ArrivalScan c,
                                   ArrivalScan_Detail 
                                   asd,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  c.Id = asd.ArrivalID
                                   AND CONVERT(NVARCHAR, c.createdBy) = 
                                       CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND asd.consignmentNumber = ctv.consignmentNumber
                                   AND c.Id = ctv.ArrivalID
                        )
                   WHEN ctv.StateID = '6' THEN (
                            SELECT +
                                   'DeBagging  was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   Bag c,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND c.bagNumber = ctv.bagNumber
                        )
                   WHEN ctv.StateID = '7' THEN (
                            SELECT +
                                   'DeManifest was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.DemanifestDate, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name
                            FROM   mnp_Manifest c,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.DemanifestBy) = 
                                   CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND c.manifestNumber = ctv.manifestNumber
                        )
                   WHEN ctv.StateID = '8' THEN (
                            SELECT +'Runsheet No :' +
                                   c.runsheetNumber 
                                   +
                                   ' was Generated on :' 
                                   +
                                   CONVERT(VARCHAR(11), c.createdOn, 106) 
                                   +
                                   ' by User :' + zu.Name 
                                   +
                                   ' on Location :' 
                                   + ec.name 
                                   +
                                   ' against Rider :' 
                                   + c.routeCode 
                                   + ' -' 
                                   + ctv.riderName
                            FROM   Runsheet c,
                                   RunsheetConsignment 
                                   rc,
                                   ZNI_USER1 zu,
                                   Branches b,
                                   ExpressCenters ec
                            WHERE  CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                                   AND zu.branchcode = b.branchCode
                                   AND zu.ExpressCenter = ec.expressCentercode
                                   AND c.runsheetNumber = rc.runsheetNumber
                                   AND c.routeCode = rc.RouteCode
                                   AND c.branchCode = rc.branchcode
                                   AND c.runsheetNumber = ctv.runsheetNumber
                                   AND ctv.consignmentNumber = rc.consignmentNumber
                        )
                   WHEN ctv.stateID = '10'
       AND LEN(ctv.riderName) <> 0 THEN (
               SELECT (CASE WHEN rc.Reason IS NULL THEN '' ELSE rc.Reason END)
               FROM   runsheetconsignment rc,
                      runsheet r1
               WHERE  ctv.consignmentnumber = rc.consignmentnumber
                      AND ctv.runsheetnumber = rc.runsheetnumber
                      AND r1.runsheetnumber = rc.runsheetnumber
                      AND r1.branchcode = rc.branchcode
                      AND r1.routecode = rc.routecode
                      AND r1.createdBy = rc.createdBy
           )
           WHEN ctv.stateID = '10'
       AND LEN(ctv.riderName) = 0
       AND ctv.reason =
           'UNDELIVERED' THEN (
               SELECT (CASE WHEN rc.Reason IS NULL THEN '' ELSE rc.Reason END)
               FROM   runsheetconsignment rc,
                      runsheet r1
               WHERE  ctv.consignmentnumber = rc.consignmentnumber
                      AND ctv.runsheetnumber = rc.runsheetnumber
                      AND r1.runsheetnumber = rc.runsheetnumber
                      AND r1.branchcode = rc.branchcode
                      AND r1.routecode = rc.routecode
                      AND r1.createdBy = rc.createdBy
           )
           WHEN ctv.stateID = '10'
       AND ctv.reason = 'DELIVERED'
       AND LEN(ctv.riderName) 
           = 0 THEN (
               SELECT (CASE WHEN rc.Reason IS NULL THEN '' ELSE rc.Reason END)
               FROM   runsheetconsignment rc,
                      runsheet r1
               WHERE  ctv.consignmentnumber = rc.consignmentnumber
                      AND ctv.runsheetnumber = rc.runsheetnumber
                      AND r1.runsheetnumber = rc.runsheetnumber
                      AND r1.branchcode = rc.branchcode
                      AND r1.routecode = rc.routecode
                      AND r1.createdBy = rc.createdBy
           )

           WHEN ctv.StateID = '20' THEN (
               SELECT +'Material Arrival No :' +
                      CONVERT(NVARCHAR, c.ArrivalID) 
                      +
                      ' was Generated on :' + CONVERT(VARCHAR(11), c.createdOn, 106) 
                      +
                      ' by User :' + zu.Name +
                      ' on Location :' 
                      + ec.name
               FROM   MNP_MaterialArrival c,
                      MNP_MaterialArrivalDetail asd,
                      ZNI_USER1          zu,
                      Branches           b,
                      ExpressCenters     ec
               WHERE  c.ArrivalID = asd.ArrivalID
                      AND CONVERT(NVARCHAR, c.createdBy) = CONVERT(NVARCHAR, zu.U_ID)
                      AND zu.branchcode = b.branchCode
                      AND zu.ExpressCenter = ec.expressCentercode
                      AND asd.ConsignmentNumber = ctv.consignmentNumber
           )
           ELSE ''
           END Detail
           FROM Consignment_Tracking_View ctv
           INNER JOIN (
               SELECT ctv2.stateID,
                      MAX(ctv2.transactionTime) TIME
               FROM   Consignment_Tracking_View ctv2
               WHERE  ctv2.consignmentNumber = '$cn'
               GROUP BY
                      ctv2.stateID
           ) a
           ON ctv.stateID = a.stateID
       AND ctv.transactionTime = a.[time]
           LEFT OUTER JOIN MNP_ConsginmentTrackingStatus mcts
           ON ctv.stateID = mcts.StatusID
           WHERE ctv.consignmentNumber = '$cn'
       AND mcts.[Active] = '1'
   ) A 

    GROUP BY
           a.consignmentNumber,
           A.sortorder,
           A.MapStatus,
           a.currentLocation,
           a.transactionTime,
           a.TrackingStatus
    ORDER BY
            a.transactionTime desc
          -- CAST(a.sortorder AS INT)

PHP;

        $results=mssql_query($newResult);
        $values = mssql_fetch_array($results);
        var_dump($values);
        //$rs_one = mssql_query($newResult);
        $num_rows_one = mssql_num_rows($results);

        if ($num_rows_one > 0){
            ?>
                <table class="col-md-12 table-bordered table-striped table-condensed cf track-history">
                    <thead class="cf">
                        <tr>
                            <td style="background: #f26522;color: #FFF;">DateTime </td>
                            <td style="background: #f26522;color: #FFF;">Status</td>
                            <td style="background: #f26522;color: #FFF;">Location </td>
                        </tr>
                    </thead>

                    <tbody>
                    <?php while($row_one = mssql_fetch_array($rs_one)){ ?>
                        <tr> 
                            <td> <?php echo $row_one["transactionTime"]; ?></td>
                            <td> <?php echo $row_one["MapStatus"]; ?></td>
                            <td> <?php echo $row_one["LOCATION"];?></td>
                        </tr>
                        <?php } ?>
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doubo7131 2017-03-01 19:44
    已采纳

    You have this part:

    while($row_one = mssql_fetch_array($rs_one)){
    

    but the line where you have $rs_one is commented out, so it is not set. Take a look at this line:

    $values = mssql_fetch_array($results);
    

    You need to use $values instead of $rs_once:

    while($row_one = mssql_fetch_array($values)){
    

    This is not necessarily a full solution to your problem, but it is certainly one problem to fix. If you give me more details about your problem, then this answer will be edited as well.

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题