Send PHP Error if No Rows Created

I’m trying to prevent duplicate rows from being created. My current statement works, meaning it prevents duplicates from being created, but it always returns the execution as a success, meaning I don’t get an “error” message if no rows were created.

Thanks

$tsql = "
    IF NOT EXISTS ( SELECT 1 FROM SOMETABLE WHERE SOMEROW = 'VALUE' )
        BEGIN
            INSERT INTO SOMETABLE (
                SOMEROW
            )

            VALUES (
                'SOMETHING'
            )
            END
    ";


$stmt = sqlsrv_query( $conn, $tsql);


if(gettype($stmt)=='boolean'){
    if($stmt){
        echo "Success"; 
    } else {
        echo "This Row already Exists";
        die( print_r( sqlsrv_errors(), true));  
    } 

I would like to fail the sql execution if nothing was created, which isn’t done in this case. (It’s always sent back as a success.

Thanks

Answer

Solution 1:

This solution is based on approach, that you expect and raises error in SQL statemwent with RAISEERROR(), so sqlsrv_query() will return FALSE.

<?php
# Connection
$server = 'serverinstance,port';
$database = 'database';
$uid = 'username';
$pwd = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $uid,
    "PWD" => $pwd
);
$conn = sqlsrv_connect($server, $cinfo);
if ( $conn === false ) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# INSERT statement
$tsql = 
    "
    IF NOT EXISTS (SELECT 1 FROM SOMETABLE WHERE SOMEROW = 'VALUE5' ) BEGIN
        INSERT INTO SOMETABLE (SOMEROW) VALUES ('VALUE5')
        END
    ELSE BEGIN
        RAISERROR (N'This row already exists.', 10, 1);
    END;
    ";
$stmt = sqlsrv_query($conn, $tsql);  
if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
} else {
    sqlsrv_free_stmt($stmt);
    echo "Success"; 
}   

# End
sqlsrv_close($conn);
?>

Solution 2:

It is possible to return output from your statement and then check the results with sqlsrv_fetch_array():

<?php
# Connection
$server = 'serverinstance,port';
$database = 'database';
$uid = 'username';
$pwd = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $uid,
    "PWD" => $pwd
);
$conn = sqlsrv_connect($server, $cinfo);
if ( $conn === false ) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# INSERT statement
$tsql = 
    "
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM SOMETABLE WHERE SOMEROW = 'VALUE' ) BEGIN
        INSERT INTO SOMETABLE (SOMEROW) VALUES ('VALUE')
        SELECT @@ROWCOUNT AS RC
        END
    ELSE BEGIN
        SELECT 0 AS RC
    END;
    ";
$stmt = sqlsrv_query($conn, $tsql);  
if ($stmt === false) {
    die( print_r( sqlsrv_errors(), true));
}

# Row count information
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $rc = $row['RC'];
}
if ($rc == 0) {
    echo "This Row already Exists";
} else {
    echo "Success"; 
}

# End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

Notes:

SQLSRV driver supports sqlsrv_rows_affected() and sqlsrv_num_rows() functions, which can be used with some restrictions.