Sqlite, emulating on duplicate update functionnality

On my last article i’ve spoken about what a common MySQL developer needs to know to use Sqlite. In this article we’ll try to simulate a missing feature which is updating if key exists “on duplicate update on MySQL” using PHP Exceptions.


            try {
                // init database cnx.
                $db = new PDO('sqlite:' . $databasePath);
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $sql = "INSERT INTO table
                        (id, content)
                        VALUES
                        (:id, :content)";

                // Prepare statement
                $stmt = $db->prepare($sql);

                // bind the params
                $stmt->bindParam(':id', $id);
                $stmt->bindParam(':content', $content);

                //
                $stmt->execute();
            // Exception code for constraint violation is 23000
            // That's why we catch this error code and do update :
            } catch (Exception $e) {
                if ($e->getCode() == 23000) {
                    try {
                        $sql = "UPDATE
                            table
                            SET
                            content = :content
                            WHERE
                            id = :id";
                        $stmt = $db->prepare($sql);
                        $stmt->bindParam(':content', $content);
                        $stmt->bindParam(':id', $id);
                        $stmt->execute();
                    } catch (Exception $e) {
                        echo $e->getMessage();
                    }
                }
            }

2 Replies to “Sqlite, emulating on duplicate update functionnality”

Leave a Reply