Jump to content

Php Odbc Help

charleswelton

By charleswelton
in Microsoft SQL - MSSQL

Recommended Posts

charleswelton Newbie

charleswelton

Posted
Posted

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!

Link to comment
https://forums.phpfreaks.com/topic/270655-php-odbc-help/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.