Jump to content

Php Odbc Help

charleswelton

By charleswelton
in Microsoft SQL - MSSQL

 Share

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
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.