Mixing SQLite and PHP: SQLite3::createFunction()

bool SQLite::createFunction ( string sqlite_function_name, mixed php_function_name [, int num_args])

So far you are probably thinking that SQLite is a nice little portable database library, but in fact the real magic is yet to come. You see, SQLite support is literally built into PHP; the two work in absolute tandem, as one program. As a result, it is possible to make the two work together in unprecedented ways.

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");

    mysqli_query($db, "CREATE TABLE sqlite_test (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255));");
    mysqli_query($db, "INSERT INTO sqlite_test (Name) VALUES ('Peter Hutchinson');");
    mysqli_query($db, "INSERT INTO sqlite_test (Name) VALUES ('Jeanette Shieldes');");

    $db = new SQLite3("employees");
    $db->query("CREATE TABLE employees (ID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(255));");
    $db->query("INSERT INTO employees (Name) VALUES ('James Fisher');");
    $db->query("INSERT INTO employees (Name) VALUES ('Peter Hutchinson');");
    $db->query("INSERT INTO employees (Name) VALUES ('Richard Hartis');");

    function ExistsInBoth($name) {
        GLOBAL $db;

        if (mysqli_num_rows(mysqli_query($db, "SELECT ID FROM sqlite_test WHERE Name = '$name';"))) {
            return 1;
        } else {
            return 0;
        }
    }

    $db->createFunction("EXISTS_IN_BOTH", "ExistsInBoth");

    $result = $db->query("SELECT Name FROM employees WHERE EXISTS_IN_BOTH(Name)");

    while($row = $result->fetchArray($query, SQLITE_ASSOC)) {
        extract($row);
        print "$Name is in both databases\n";
    }
?>

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Normalisation >>

Previous chapter: Advanced usage: SQLite3::lastInsertRowID() and SQLite3::querySingle()

Jump to:

Home: Table of Contents