PHP TutorialCompile PHP ExtensionsContributing to the PHP CoreContributing to the PHP ManualCreate PDF files in PHPInstalling a PHP environment on WindowsPHP Alternative Syntax for Control StructuresPHP APCuPHP Array iterationPHP ArraysPHP Asynchronous programmingPHP Autoloading PrimerPHP BC Math (Binary Calculator)PHP Built in serverPHP CachePHP Classes and ObjectsPHP ClosurePHP Coding ConventionsPHP Command Line Interface (CLI)PHP CommentsPHP Common ErrorsPHP Compilation of Errors and WarningsPHP Composer Dependency ManagerPHP ConstantsPHP Control StructuresPHP CookiesPHP CryptographyPHP DateTime ClassPHP DebuggingPHP Dependency InjectionPHP Design PatternsPHP Docker deploymentPHP Exception Handling and Error ReportingPHP Executing Upon an ArrayPHP File handlingPHP Filters & Filter FunctionsPHP Functional ProgrammingPHP FunctionsPHP GeneratorsPHP Headers ManipulationPHP How to break down an URLPHP How to Detect Client IP AddressPHP HTTP AuthenticationPHP Image Processing with GDPHP ImagickPHP IMAPPHP Installing on Linux/Unix EnvironmentsPHP JSONPHP LocalizationPHP LoopsPHP Machine learningPHP Magic ConstantsPHP Magic MethodsPHP Manipulating an ArrayPHP mongo-phpPHP Multi Threading ExtensionPHP MultiprocessingPHP MySQLiPHP MySQLi affected rows returns 0 when it should return a positive integerPHP NamespacesPHP Object SerializationPHP OperatorsPHP Output BufferingPHP Outputting the Value of a VariablePHP Parsing HTMLPHP Password Hashing FunctionsPHP PDOPHP PerformancePHP PHPDocPHP Processing Multiple Arrays TogetherPHP PSRPHP Reading Request DataPHP RecipesPHP ReferencesPHP ReflectionPHP Regular Expressions (regexp/PCRE)PHP Secure Remeber MePHP SecurityPHP Sending EmailPHP SerializationPHP SessionsPHP SimpleXMLPHP SOAP ClientPHP SOAP ServerPHP SocketsPHP SPL data structuresPHP SQLite3PHP StreamsPHP String formattingPHP String Parsing

PHP MySQLi affected rows returns 0 when it should return a positive integer

From WikiOD

This script is designed to handle reporting devices (IoT), when a device is not previously authorized (in the devices table in the database), I add the new device to a new_devices table. I run an update query, and if affected_rows returns < 1, I insert.

When I have a new device report, the first time $stmt->affected_rows runs it returns 0, subsequent communication returns 1, then 1, 0, 2, 2, 2, 0, 3, 3, 3, 3, 3, 3, 0, 4, 0, 0, 6, 6, 6, etc

It's as if the update statement is failing. Why?

PHP's $stmt->affected_rows intermittently returning 0 when it should return a positive integer[edit | edit source]

    // if device exists, update timestamp
    $stmt = $mysqli->prepare("UPDATE new_devices SET nd_timestamp=? WHERE nd_deviceid=?");
    $stmt->bind_param('ss', $now, $device);
    //echo "Affected Rows: ".$stmt->affected_rows; // This line is where I am checking the status of the update query.

    if ($stmt->affected_rows < 1){ // Because affected_rows sometimes returns 0, the insert code runs instead of being skipped. Now I have many duplicate entries.

        $ins = $mysqli->prepare("INSERT INTO new_devices (nd_id,nd_deviceid,nd_timestamp) VALUES (nd_id,?,?)");
        $ins -> bind_param("ss",$device,$now);
        $ins -> execute();
        $ins -> store_result();
        $ins -> free_result();