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