CODEWITHSUNDEEP
phpsql-serverdatabasesqlsrv
Dashiphone
Dashiphone

Reputation: 59

PHP MS SQL, Query is not executed, but php return true

I created a long query for MS SQL, I checked it manually - it works. I try to do it with php, return success, but in fact the query is not executed.

<?php
    if(isset($_POST['show'])){
        $serverName = "SRV01\SIGMANEST";   
        $uid = "";     
        $pwd = "";    
        $databaseName = "Intranet";   
        $revice = "";
        $connectionInfo = array( "UID"=>$uid,                              
                                 "PWD"=>$pwd,                              
                                 "Database"=>$databaseName,
                                 "ReturnDatesAsStrings" => true);   

        /* Connect using SQL Server Authentication. */    
        $conn = sqlsrv_connect( $serverName, $connectionInfo);    

        $UpdateQuery = "USE [Intranet]
                        GO

                        /****** Object:  View [dbo].[STEEL_UT]    Script Date: 14/03/2020 23:32:25 ******/
                        SET ANSI_NULLS ON
                        GO

                        SET QUOTED_IDENTIFIER ON
                        GO

                        ALTER VIEW [dbo].[STEEL_UT] AS


                        WITH PRGCOUNT AS(
                        SELECT 
                        T0.Material,
                        T0.Thickness,
                        T0.Length,
                        T0.Width,
                        T0.MachineName,
                        T0.ProgramName,
                        COUNT(DISTINCT T0.ProgramName) AS 'PrNum',
                        COUNT(T0.ProgramName) AS 'FQty',
                        SUM(CAST(T0.Rep AS INT)) AS 'Rep',
                        AVG(CAST(T0.Yeld AS decimal)) AS 'Average'
                        FROM dbo.RecivePrograms T0
                        Where ProgramName Like '".$_POST['week']."%'

                        Group By T0.Material,T0.Thickness,
                        T0.Length,
                        T0.Width,
                        T0.MachineName,
                        T0.ProgramName)


                        SELECT 
                        T0.Material,
                        T0.Thickness,
                        T0.Length,
                        T0.Width,
                        T0.MachineName,
                        CASE WHEN(SUM(CAST(T0.Rep AS INT)/T1.Fqty)) =0 THEN T1.PrNum ELSE (SUM(CAST(T0.Rep AS INT)/T1.Fqty)) END AS 'VRrep',
                        AVG(CAST(T0.Yeld AS decimal)) AS 'Average'
                        FROM dbo.RecivePrograms T0
                        JOIN PRGCOUNT T1 ON T0.ProgramName=T1.ProgramName
                        Where T0.ProgramName Like '".$_POST['week']."%'

                        Group By T0.Material,T0.Thickness,
                        T0.Length,
                        T0.Width,
                        T0.MachineName
                        ,T0.ProgramName,
                        T1.PrNum


                        GO
                        ";

            if( $UpdateQuery === false ) {
                die( print_r( sqlsrv_errors(), true));
            }else {
                echo "Executed";
            }


        $ShowTable = "SELECT [Material]
              ,[Thickness]
              ,[Length]
              ,[Width]
              ,[MachineName]
              ,sum([VRrep]) as 'SHITS'
              ,avg([Average]) as 'avg'
          FROM [Intranet].[dbo].[STEEL_UT] 
          GROUP BY [Material]
              ,[Thickness]
              ,[Length]
              ,[Width]
              ,[MachineName]


          ORDER BY Material, Thickness";


        /* Execute the query. */

        $stmt = sqlsrv_query( $conn, $UpdateQuery);    
        $stmt2 = sqlsrv_query( $conn, $ShowTable);


        echo "
        <div class='table-responsive'>
            <table class='table table-bordered mb-4'>
                <thead>
                    <tr>
                        <th>Material</th>
                        <th>Thickness</th>
                        <th>Size</th>
                        <th>Machine Name</th>
                        <th class='text-center'>Sheets</th>
                        <th class='text-center'>Yeld</th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
        ";

        while( $row = sqlsrv_fetch_array($stmt2))    
        {  

            echo "<tr>";
            echo "<Td>".$row['Material']."</td>";
            echo "<Td>".$row['Thickness']."</td>";
            echo "<Td>".$row['Length']."x".$row['Width']."</td>";
            echo "<Td>".$row['MachineName']."</td>";
            echo "<Td class='text-center'>".$row['SHITS']."</td>";
            echo "<Td class='text-center'>".round($row['avg'],2)."</td>";
            echo "</tr>";
        }

        /* Free statement and connection resources. */    
        sqlsrv_free_stmt( $stmt2);    
        sqlsrv_close( $conn);

        echo"
                </tbody>
            </table>
        </div>
        ";
    }
?>

The process is that I have to execute $UpdateQuery first to update the 'temporary' table in SQL, which I then need to get by $ShowTable. PHP Returns the truth when I check if Query was done correctly. I have no idea why PHP lies.

Thank you

Upvotes: 0

Views: 138

Answers (1)

Zhorov
Zhorov

Reputation: 30023

You need to make two corrections in your code:

  • Remove the GO keyword. GO is not a T-SQL statement. As is explained in the documentation, GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities.
  • Execute the $UpdateQuery statement with sqlsrv_query() and then check the result from the execution. Now, with if($UpdateQuery === false), you are checking the statement's text, not the result from it's execution.

You may try with this script (based on the script in the question):

<?php
if(isset($_POST['show'])){
    $serverName = "SRV01\SIGMANEST";   
    $uid = "";     
    $pwd = "";    
    $databaseName = "Intranet";   
    $revice = "";
    $connectionInfo = array(
        "UID"=>$uid,                              
        "PWD"=>$pwd,                              
        "Database"=>$databaseName,
        "ReturnDatesAsStrings" => true
    );   

    /* Connect using SQL Server Authentication. */    
    $conn = sqlsrv_connect($serverName, $connectionInfo);    
    if ($conn === false) {
        echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    }

    $UpdateQuery = "
        USE [Intranet]
        SET ANSI_NULLS ON
        SET QUOTED_IDENTIFIER ON
        ALTER VIEW [dbo].[STEEL_UT] AS
            WITH PRGCOUNT AS(
            SELECT 
            T0.Material,
            T0.Thickness,
            T0.Length,
            T0.Width,
            T0.MachineName,
            T0.ProgramName,
            COUNT(DISTINCT T0.ProgramName) AS 'PrNum',
            COUNT(T0.ProgramName) AS 'FQty',
            SUM(CAST(T0.Rep AS INT)) AS 'Rep',
            AVG(CAST(T0.Yeld AS decimal)) AS 'Average'
            FROM dbo.RecivePrograms T0
            Where ProgramName Like '".$_POST['week']."%'

            Group By T0.Material,T0.Thickness,
            T0.Length,
            T0.Width,
            T0.MachineName,
            T0.ProgramName)


            SELECT 
            T0.Material,
            T0.Thickness,
            T0.Length,
            T0.Width,
            T0.MachineName,
            CASE WHEN(SUM(CAST(T0.Rep AS INT)/T1.Fqty)) =0 THEN T1.PrNum ELSE (SUM(CAST(T0.Rep AS INT)/T1.Fqty)) END AS 'VRrep',
            AVG(CAST(T0.Yeld AS decimal)) AS 'Average'
            FROM dbo.RecivePrograms T0
            JOIN PRGCOUNT T1 ON T0.ProgramName=T1.ProgramName
            Where T0.ProgramName Like '".$_POST['week']."%'

            Group By T0.Material,T0.Thickness,
            T0.Length,
            T0.Width,
            T0.MachineName
            ,T0.ProgramName,
            T1.PrNum
        ";
    $stmt = sqlsrv_query( $conn, $UpdateQuery);    
    if ($stmt === false) {
        die( print_r( sqlsrv_errors(), true));
    } else {
        echo "Executed";
    }

    $ShowTable = "SELECT [Material]
          ,[Thickness]
          ,[Length]
          ,[Width]
          ,[MachineName]
          ,sum([VRrep]) as 'SHITS'
          ,avg([Average]) as 'avg'
      FROM [Intranet].[dbo].[STEEL_UT] 
      GROUP BY [Material]
          ,[Thickness]
          ,[Length]
          ,[Width]
          ,[MachineName]


      ORDER BY Material, Thickness";
    $stmt2 = sqlsrv_query($conn, $ShowTable);
    if ($stmt2 === false) {
        die( print_r( sqlsrv_errors(), true));
    } else {
        echo "Executed";
    }

    echo "
    <div class='table-responsive'>
        <table class='table table-bordered mb-4'>
            <thead>
                <tr>
                    <th>Material</th>
                    <th>Thickness</th>
                    <th>Size</th>
                    <th>Machine Name</th>
                    <th class='text-center'>Sheets</th>
                    <th class='text-center'>Yeld</th>
                    <th></th>
                </tr>
            </thead>
            <tbody>
    ";

    while($row = sqlsrv_fetch_array($stmt2))    
    {  
        echo "<tr>";
        echo "<Td>".$row['Material']."</td>";
        echo "<Td>".$row['Thickness']."</td>";
        echo "<Td>".$row['Length']."x".$row['Width']."</td>";
        echo "<Td>".$row['MachineName']."</td>";
        echo "<Td class='text-center'>".$row['SHITS']."</td>";
        echo "<Td class='text-center'>".round($row['avg'],2)."</td>";
        echo "</tr>";
    }

    /* Free statement and connection resources. */    
    sqlsrv_free_stmt($stmt);    
    sqlsrv_free_stmt($stmt2);    
    sqlsrv_close($conn);

    echo"
            </tbody>
        </table>
    </div>
    ";
}
?>

Upvotes: 2

Related Questions