Jump to content


Photo

Php Odbc Help


  • Please log in to reply
1 reply to this topic

#1 charleswelton

charleswelton

    Newbie

  • New Members
  • Pip
  • 1 posts

Posted 13 November 2012 - 08:17 PM

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(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.gif' class='bbc_emoticon' alt='8)' />,
		 @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(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.gif' class='bbc_emoticon' alt='8)' />,
				 _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!

#2 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 21 November 2012 - 10:19 AM

why aren't you using the sqlsrv driver?
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com