Hello:
I am new to this forum so please forgive me if I am not posting in the correct location. I am attempting to write a PHP web application that talks to a MSSQL database. I am using PHP with ODBC. I am able to retrieve the FIRST row, but unable to retrieve any additional rows.
Here is my code:
<?php
//Set no caching
ini_set('max_execution_time', 123456);
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
?>
<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN>
<html>
<head>
</head>
<body>
<?php
$myDB = "Altiris";
//connection to the database
$dbhandle = odbc_connect($myDB,'','')
or die("Couldn't connect to SQL Server on $myServer.");
function row_color($i){
$bg1 = "#c0c0c0"; // color one
$bg2 = "#ffffff"; // color two
if ( $i%2 ) {
return $bg1;
} else {
return $bg2;
}
}
$search_query = "SELECT Name, _ResourceGuid FROM Inv_AeX_AC_Identification WHERE _ResourceGuid = '".$_GET['c']."' ORDER BY Name";
$search_result = odbc_exec($dbhandle,$search_query);
$search_numRows = odbc_num_rows($search_result);
echo $search_numRows."<br/>";
while (odbc_fetch_row($search_result))
{
$compName = odbc_result($search_result,"Name");
$compGUID = odbc_result($search_result,"_ResourceGuid");
echo $compGUID;
$sql = "DECLARE
@COMPGUID UNIQUEIDENTIFIER,
@OPERATINGSYSTEM NVARCHAR(64),
@DISTRIBUTIONSTATUS NVARCHAR(,
@SCOPECOLLECTIONGUID UNIQUEIDENTIFIER,
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@PCULTURE VARCHAR(10),
@FILTERCOLLECTION UNIQUEIDENTIFIER,
@TRUSTEESCOPE VARCHAR(MAX),
@VENDORGUID UNIQUEIDENTIFIER,
@CATEGORYGUID UNIQUEIDENTIFIER,
@VIEWMODE NVARCHAR(16),
@SUPERSEDENCESTATUS VARCHAR(20)
SELECT
@COMPGUID = '".$compGUID."',
@OPERATINGSYSTEM = '%',
@DISTRIBUTIONSTATUS = 'active',
@SCOPECOLLECTIONGUID = '00000000-0000-0000-0000-000000000000',
@STARTDATE = '1978-09-28T00:00:00',
@ENDDATE = GETDATE(),
@PCULTURE = 'en-US',
@FILTERCOLLECTION = '01024956-1000-4cdb-b452-7db0cff541b6',
@TRUSTEESCOPE = '{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{8609D1CE-1F9C-4BF9-8B41-7555B85FB94D},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}',
@VENDORGUID = '9D5F6BB8-8ADF-49D1-9D84-2932CA46CE1E',
@CATEGORYGUID = '00000000-0000-0000-0000-000000000000',
@SUPERSEDENCESTATUS = 'non-superseded'
DECLARE @Applicable TABLE
(
_Update NVARCHAR(250),
Name NVARCHAR(250),
Description NVARCHAR(500),
Guid UNIQUEIDENTIFIER
)
DECLARE @Installed TABLE
(
_Update NVARCHAR(250),
Name NVARCHAR(250),
Description NVARCHAR(500),
Guid UNIQUEIDENTIFIER
)
DECLARE @Rebootstatus TABLE
(
_ResourceGuid UNIQUEIDENTIFIER,
_ComputerName NVARCHAR(64),
_Compliance DECIMAL,
_ApplicableCount INT,
_InstalledCount INT,
_NotInstalledCount INT,
_RestartPending VARCHAR(4),
_OperatingSystem NVARCHAR(64),
_DistributionStatus NVARCHAR(,
_StartDate DATETIME,
_ENDDATE DATETIME
)
INSERT INTO @Applicable
select distinct rsu.name as '_Update', rsu.Name, rsu.Description, rsu.Guid from vPMCore_AdvertisementAppliesToResource apr
inner join ItemActive ia on ia.Guid = apr.AdvertisementGuid and Enabled = '1'
inner join vPMCore_SoftwareUpdateAdvertisement sua on sua.Guid = apr.AdvertisementGuid
inner join RM_ResourcePatch_Software_Update rsu on rsu.Guid = sua.SoftwareUpdateGuid
inner join vPMWindows_UpdateApplicable apc on apc.UpdateGuid = sua.SoftwareUpdateGuid and apc._ResourceGuid = @COMPGUID
--inner join vPMWindows_UpdateInstalled u on u.UpdateGuid = sua.SoftwareUpdateGuid and u._ResourceGuid = @COMPGUID
where apr.ResourceGuid = @COMPGUID
and sua.ClassGuid = '59705429-CFCC-4F7C-9BF6-D4FCE8C9622C'
and sua.ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
INSERT INTO @Installed
select distinct rsu.name as '_Update', rsu.Name, rsu.Description, rsu.Guid from vPMCore_AdvertisementAppliesToResource apr
inner join ItemActive ia on ia.Guid = apr.AdvertisementGuid and Enabled = '1'
inner join vPMCore_SoftwareUpdateAdvertisement sua on sua.Guid = apr.AdvertisementGuid
inner join RM_ResourcePatch_Software_Update rsu on rsu.Guid = sua.SoftwareUpdateGuid
inner join vPMWindows_UpdateApplicable apc on apc.UpdateGuid = sua.SoftwareUpdateGuid and apc._ResourceGuid = @COMPGUID
inner join vPMWindows_UpdateInstalled u on u.UpdateGuid = sua.SoftwareUpdateGuid and u._ResourceGuid = @COMPGUID
where apr.ResourceGuid = @COMPGUID
and sua.ClassGuid = '59705429-CFCC-4F7C-9BF6-D4FCE8C9622C'
and sua.ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
INSERT INTO @Rebootstatus
EXEC spPMWindows_ComplianceByComputer
@VENDORGUID,
@CATEGORYGUID,
@SUPERSEDENCESTATUS,
@OPERATINGSYSTEM,
@DISTRIBUTIONSTATUS,
@FILTERCOLLECTION,
@STARTDATE,
@ENDDATE,
@PCULTURE,
@SCOPECOLLECTIONGUID,
@TRUSTEESCOPE
select ((select COUNT(*) from @Installed)) as 'MyCount','Pending' as 'Status', a._Update, b.Name as 'Bulletin', b.Description from @Applicable a
inner join ResourceAssociation ra on ra.ChildResourceGuid = a.Guid and ra.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'
inner join RM_ResourceSoftware_Bulletin b on b.Guid = ra.ParentResourceGuid
Where a._Update not in (select _Update from @Installed)
UNION
select ((select COUNT(*) from @Installed)) as 'MyCount','Update Applied' as 'Status', a._Update, b.Name as 'Bulletin', b.Description from @Installed a
inner join ResourceAssociation ra on ra.ChildResourceGuid = a.Guid and ra.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'
inner join RM_ResourceSoftware_Bulletin b on b.Guid = ra.ParentResourceGuid
order by b.Name, a._Update, MyCount ASC";
if (odbc_error()) {
echo "I've found a problem: " . odbc_errormsg($conn);
}
$sql_result = odbc_exec($dbhandle,$sql);
if (!$sql_result) {
exit("Error in SQL");
}
while (odbc_fetch_row($sql_result)){
$col1=odbc_result($sql_result, "test_col");
echo "$col1\n";
}
}
?>
</body>
<html>
I have tried many different ODBC fetching options and none of them worked for me, but it is most likely because I wasn't using them correctly. Here is the error I am receiving:
Warning: odbc_fetch_row() [function.odbc-fetch-row]: No tuples available at this result index in C:\wamp\www\PM\get_update_info.php on line 176
Any help would be greatly appreciated!!!! Thank you!