Mixing SQLite and PHP

bool sqlite_create_function ( resource db_handle, 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
    mysql_connect
("localhost", "phpuser", "alm65z");
    
mysql_select_db("phpdb");

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

    
$conn = sqlite_open("employees");
    
sqlite_query($conn, "CREATE TABLE employees (ID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(255));");
    
sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('James Fisher');");
    
sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('Peter Hutchinson');");
    
sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('Richard Hartis');");

    function
ExistsInBoth($name) {
        if (
mysql_num_rows(mysql_query("SELECT ID FROM sqlite_test WHERE Name = '$name';"))) {
            return
1;
        } else {
            return
0;
        }
    }

    
sqlite_create_function($conn, "EXISTS_IN_BOTH", "ExistsInBoth");

    
$query = sqlite_query($conn, "SELECT Name FROM employees WHERE EXISTS_IN_BOTH(Name)");

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

 

Next chapter: Normalisation >>

Previous chapter: Advanced functions

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar