I've been wanting to build multi-database apps for a while and I still can't find anything about how to go about it.
All I would like to know is the best way to handle SQLite and MySQL queries. Do I need to have an IF statement with two different queries for each query I want to run?
Or is there an easy way to convert a MySQL statement into SQLite?
Use PHP's PDO.
All you need to do is change the connection string and you can connect to different databases. As long as your SQL isn't written using functions specific to a particular database, it will just work with these databases:
CUBRID (PDO) — CUBRID Functions (PDO_CUBRID)
MS SQL Server (PDO) — Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Firebird/Interbase (PDO) — Firebird/Interbase Functions (PDO_FIREBIRD)
IBM (PDO) — IBM Functions (PDO_IBM)
Informix (PDO) — Informix Functions (PDO_INFORMIX)
MySQL (PDO) — MySQL Functions (PDO_MYSQL)
MS SQL Server (PDO) — Microsoft SQL Server Functions (PDO_SQLSRV)
Oracle (PDO) — Oracle Functions (PDO_OCI)
ODBC and DB2 (PDO) — ODBC and DB2 Functions (PDO_ODBC)
PostgreSQL (PDO) — PostgreSQL Functions (PDO_PGSQL)
SQLite (PDO) — SQLite Functions (PDO_SQLITE)
4D (PDO) — 4D Functions (PDO_4D)
It supports all the functions you want as well as many built in goodies preventing injection attacks and a bunch more.
Edit: Some nice links for further reading as suggested by eldbiz
http://php.net/manual/en/book.pdo.php http://www.php.net/manual/en/ref.pdo-sqlite.php http://www.php.net/manual/en/ref.pdo-mysql.php
It sounds like you might be looking for something like Doctrine... an ORM for PHP.
Many simple queries will run fine in both SQLite and Mysql, but PDO is not modifying your queries, only providing a mechanism to connect to each DB and send those queries. An ORM is a way of mapping object types to database tables and accessing them directly.
You'll still need "if" statements in some places, the ORM isn't going to automatically decide what to persist where or know automatically what database to find your data in.