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