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 } ?>

SQL case when tableA（name1,name2）

liberty-

7个回复