PHP 5.4 to 7.4 mssql to sqlsrv sql query not running

Question

A little preface I'm a year one system administrator and I've been migrating servers to get this company up to date. I've run into a server running php 5.4 and I'm trying to move it to php 7.4. Everything was originally written in mssql and I'm moving it to sqlsrv. I have the connection working. I can do some queries successfully, but I can't get these queries that were written for mssql to work with sqlsrv.

Things I know:

• $record is returning as an array I tested that with gettype.
• $conn works because I can query tables from the DB with a simple query.

The sql queries run correctly in sql server.

Any advice would be appreciated because I'm feeling like I'm going to need to rewrite this whole script because I've been struggling with this for a few days now. The snippet below is just one of many queries built into an if else chain.

Original code written in php 5.4:

$query = "Select * INTO #temptable FROM (
    SELECT Employee
        ,transdate
        ,sum([RegHrs]) as RegHrs
        ,sum([OvtHrs]) as OvtHrs
    
    FROM [dbo].[tkDetail]
    WHERE TransDate >= cast('" . $startdate . "' as datetime) and TransDate <= cast('" . $enddate . "' as datetime)
    GROUP BY Employee, transdate) as x

    SELECT LastName,FirstName,emmain.Employee, emcom.PayType, cast(data.TransDate as date) as TransDate
    ,data.reghrs
    ,data.ovthrs
    from dbo.emmain emmain
    Left Join #temptable data on emmain.employee = data.employee
    Left Join dbo.EMCompany emcom on emmain.employee = emcom.employee
    Left Join dbo.EmployeeCustomTabFields custom on emmain.employee = custom.employee
    WHERE custom.custFullTimePartTime = 'Full Time' and emcom.status = 'A'
    ";
    $result = mssql_query($query);
    while ( $record = mssql_fetch_array($result) ) {
        // BUGFIX: Salary reporting shows no regular hour entry as null instead of 0.0
        if ($record['reghrs'] == null) {
            $record['reghrs'] = "0.0";
        }
        
        // BUGFIX: Salary reporting shows no overtime hour entry as null instead of 0.0
        if ($record['ovthrs'] == null) {
            $record['ovthrs'] = "0.0";
        }
        
        if (($record['reghrs'] + $record['ovthrs'] <= 4) && ($record['reghrs'] + $record['ovthrs'] > -1)) {
            print "<tr>
";
            print "<td>" . $record['Employee'] . "</td>
";
            print "<td>" . $record['FirstName'] . " " . $record['LastName'] . "</td>
";
            print "<td>" . $record['PayType'] . "</td>
";
            print "<td>" . number_format((float) $record['reghrs'], 3) . "</td>
";
            print "<td>" . number_format((float) $record['ovthrs'], 3) . "</td>
";
            print "<td>" . $record['TransDate'] . "</td>
";
            print "</tr>
";
            $reccount += 1;
        }
    }

What I've tried to do:

$query = "Select * INTO #temptable FROM (
    SELECT Employee
        ,transdate
        ,sum([RegHrs]) as RegHrs
        ,sum([OvtHrs]) as OvtHrs
    
    FROM [dbo].[tkDetail]
    WHERE TransDate >= cast('" . $startdate . "' as datetime) and TransDate <= cast('" . $enddate . "' as datetime)
    GROUP BY Employee, transdate) as x

    SELECT LastName,FirstName,emmain.Employee, emcom.PayType, cast(data.TransDate as date) as TransDate
    ,data.reghrs
    ,data.ovthrs
    from dbo.emmain emmain
    Left Join #temptable data on emmain.employee = data.employee
    Left Join dbo.EMCompany emcom on emmain.employee = emcom.employee
    Left Join dbo.EmployeeCustomTabFields custom on emmain.employee = custom.employee
    WHERE custom.custFullTimePartTime = 'Full Time' and emcom.status = 'A'
    ";

    $result = sqlsrv_query($conn, $query);
    if( $result ) {
        echo "result true";
    }else{
        echo "result false <br />";
        die( print_r( sqlsrv_errors(), true));
    }    
    while ($record = sqlsrv_fetch_array($result)) 
        {
            
        // BUGFIX: Salary reporting shows no regular hour entry as null instead of 0.0
        if ($record["reghrs"] == null) {
            $record["reghrs"] = "0.0";
        }
        
        
        // BUGFIX: Salary reporting shows no overtime hour entry as null instead of 0.0
        if ($record['ovthrs'] == null) {
            $record['ovthrs'] = "0.0";
        }
        
        if (($record['reghrs'] + $record['ovthrs'] <= 4) && ($record['reghrs'] + $record['ovthrs'] > -1)) {
            print "<tr>
";
            print "<td>" . $record['Employee'] . "</td>
";
            print "<td>" . $record['FirstName'] . " " . $record['LastName'] . "</td>
";
            print "<td>" . $record['PayType'] . "</td>
";
            print "<td>" . number_format((float) $record['reghrs'], 3) . "</td>
";
            print "<td>" . number_format((float) $record['ovthrs'], 3) . "</td>
";
            print "<td>" . $record['TransDate'] . "</td>
";
            print "</tr>
";
            $reccount += 1;
        }
    }

question from:https://stackoverflow.com/questions/65889154/php-5-4-to-7-4-mssql-to-sqlsrv-sql-query-not-running

Answer 1

Waitting for answers