#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    PHP/ODBC with MSSQL Help


    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(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(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. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Thumbs up ODBC With PHP


    odbc with php u can see my phpden.blogspot.com website it is very useful

IMN logo majestic logo spyfu logo threadwatch logo seochat tools logo