php sql_srv check if DB has rows failing

Question

I'm an Android developer that has next to no knowledge with php. I've an app that uses Google push notifications and works fine. The problem i'm having is when the same phone registers with Google that regID from google must be stored in my DB. If i push the app on to the phone say 4 times then i will have 4 rows in the DB pointing to one phone.

I'm trying to check the DB to see if a record exists for a given regID. I'm trying to use sqlsrv_has_rows() to do this. The code below compiles but does not insert a record if the record exists or not.

Can anyone see what the problem is?

Thanks in advance,

matt

public function storeUser($companyid, $gcm_regid) {

    $strCompanyID = strval($companyid);
    $strRegID = strval($gcm_regid);

    $serverName = "LOCALHOST\SQLEXPRESS"; 
    $uid = "gcm";     
    $pwd = "gcm";    
    $databaseName = "gcm";   

    $connectionInfo = array(
        "UID" => $uid,
        "PWD" => $pwd,
        "Database" => $databaseName
    ); 

    $db = sqlsrv_connect($serverName, $connectionInfo)
              or die("Unable to connect to server");

    $query = "
        SELECT *
        FROM GcmUsers
        WHERE gcuRegID = " . $strRegID;
    $resultQueryRegID = sqlsrv_query($db, $query);

    if ($resultQueryRegID) {
        $rows = sqlsrv_has_rows($resultQueryRegID);

        if ($rows === true) {
            //echo "There are rows. <br />";
        } else { 
            // echo "There are no rows. <br />";
            $queryInsert = "
                INSERT INTO GcmUsers
                    (gcuCompanyID, gcuRegID)
                VALUES
                    ('$strCompanyID','$strRegID')
            ";
            $result = sqlsrv_query($db, $queryInsert);
        }
    }
}

[EDIT 1]

public

function storeUser($companyid, $gcm_regid) {

    $strCompanyID = strval($companyid);
    $strRegID = strval($gcm_regid);


    $serverName = "LOCALHOST\SQLEXPRESS";
    $uid = "gcm";
    $pwd = "gcm";
    $databaseName = "gcm";

    $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database" => $databaseName);

    $db = sqlsrv_connect($serverName, $connectionInfo) or die("Unable to connect to server");



    $sql = "SELECT * FROM GcmUsers where gcuRegID = ?";

    // Initialize parameters and prepare the statement.
    // Variables $qty and $id are bound to the statement, $stmt.

    $stmt = sqlsrv_prepare($db, $sql, array(&$strRegID));
    if (!$stmt) {
        die(print_r(sqlsrv_errors(), true));
    }

    $result = sqlsrv_execute($stmt);



    $rows = sqlsrv_has_rows($result);

    if ($rows === true) {
        // echo "There are rows. <br />";
    } else {
        // echo "There are no rows. <br />";
        $queryInsert = "INSERT INTO GcmUsers ( gcuCompanyID, gcuRegID) values ('$strCompanyID','$strRegID')";
        $result = sqlsrv_query($db, $queryInsert);
    }
}

[edit2]

$sql = "SELECT * FROM GcmUsers where gcuRegID = ?";

                        // Initialize parameters and prepare the statement. 
                        // Variables $qty and $id are bound to the statement, $stmt.

                        $stmt = sqlsrv_prepare( $db, $sql, array( &$strRegID));
                        if( !$stmt ) {
                            die( print_r( sqlsrv_errors(), true));
                        }           

                        $result = sqlsrv_execute( $stmt );





                            if (sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
                                // Got rows
                            }else{
                                // Not rows
                                 $queryInsert = "INSERT INTO GcmUsers ( gcuCompanyID, gcuRegID) values ('$strCompanyID','$strRegID')";
                                  $result = sqlsrv_query($db, $queryInsert);
                            }

Answer 1

Answer to original question:

PHP and SQL are different languages that run on different platforms. You basically have this:

$strRegID = strval($gcm_regid);
$query = "
    SELECT *
    FROM GcmUsers
    WHERE gcuRegID = " . $strRegID;

This PHP code generates SQL code and the result is probably something like this:

SELECT *
FROM GcmUsers
WHERE gcuRegID = ABCDEFG

I assume you can now spot the error—if you don't, you should at least learn some basic SQL.

To fix your code, please have a look at How to prevent SQL injection in PHP? and starting using prepared statements.

Finally, have a look at the usage examples in PHP manual and ensure you know how to fetch error messages from SQL Server. Otherwise, you'll be giving shots in the dark.

---

Answer to edited question:

Your check is this:

$rows = sqlsrv_has_rows($resultQueryRegID);
if ($rows === true) {
    //echo "There are rows. <br />";
} else { 
   // echo "There are no rows. <br />";
}

But that's not exactly what the manual says:

Returns TRUE if the specified statement has rows and FALSE if the statement does not have rows or if an error occurred.

Which translates to your code as "insert a new record if there isn't a previous one or if we were not able to determine it".

Using the same return value for two entirely different results is probably an arguably design decision but that's how the function works. I suppose that when you get FALSE you'll need to test whether there's an error or not.

Alternatively, just grabbing the row seems like a more straightforward method:

if (sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    // Got rows
}else{
    // Not rows
}

Answer 2

An alternative approach may be to let the database do the checking in the insert statement by adding a "where not exists" clause - so the insert sql would look something like

INSERT INTO GcmUsers ( gcuCompanyID, gcuRegID) 
select ('strCompanyID','strRegID')
where not exists ( select * from GcmUsers where gcuRegID = 'strRegID' )