2

I am porting a site running PHP with an MS Access DB on a windows machine to a Mac with an SQLite DB.

the original PHP script uses the following code to connect to the database:

$db = 'S:\~myhome\mydata.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db");

What would be the SQLite equivalent?

Edit: I tried 

$db = 'sqlite:'.__DIR__.'/mydata.sqlite'; 
$conn  = new PDO($db) or die("cannot open the database");

but it didn't work

Cassie
  • 123
  • 2
  • 9
  • 1
    Just take a look into the documentation of phps sqlite3 driver: http://php.net/manual/en/book.sqlite3.php There is an introduction, function explanations and lots of good examples. I really would expect that answers your question... – arkascha Aug 09 '15 at 19:34
  • This seems like a good time to port it over to use PDO as well. – ArtisticPhoenix Aug 09 '15 at 19:36

3 Answers3

2

Like Python, PHP has a built in SQLite library. Current versions support SQLite3. First, uncomment out the php_sqlite extension in the .ini file. Then, simply, call a new object:

<php

$conn = new SQLite3($db);

$results = $conn->query('SELECT bar FROM foo'); 

while ($row = $results->fetchArray()) {
        var_dump($row);
} 

?>

Of course as suggested you can use PDO or mysqli database connections.

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I'm not sure what you mean by "the .ini file". Do you mean /Library/Server/Web/Config/php/php.ini ? if so it does not have any line php_sqlite extension. Can you tell me exactly what the line should say? – Cassie Aug 10 '15 at 00:08
  • See this [SO post](http://stackoverflow.com/questions/9343151/where-is-php-ini-in-mac-os-x-lion-thought-it-was-in-usr-local-php5-lib) of exactly your question. – Parfait Aug 10 '15 at 00:11
  • Search for `;extension=php_sqlite3`, and its PDO counterpart: `;extension=php_pdo_sqlite`. – Parfait Aug 10 '15 at 00:13
  • I looked at that post and found the /private/etc/php.ini file. In it there are lines `;extension=php_sqlite3.dll` and `;extension=php_pdo_sqlite.dll`, but those are for windows. There aren't lines that have just `;extension=php_sqlite3` or `;extension=php_pdo_sqlite` – Cassie Aug 10 '15 at 00:34
  • @Cassie - Correct .dll are Windows files. PHP is cross-platform, so ships with all required. Follow manual install instructions [here](http://stackoverflow.com/questions/6523504/php-warning-unable-to-load-dynamic-library) to add `extension=php_sqlite3.so` for mac. – Parfait Aug 10 '15 at 02:02
1

Since you have to work on it anyway, I suggest to use PDO. This is a standard PHP library with drivers for several database types.

For a quick start see http://www.phptherightway.com/#databases, there's a short example about SQLite as well.

Francesco Abeni
  • 4,190
  • 1
  • 19
  • 30
1

after much searching I found the answer:

include '/usr/share/php/adodb/adodb.inc.php';
$path = urlencode(__DIR__.'/mydata');
$dsn = "sqlite://$path/?persist";  # persist is optional
$conn = ADONewConnection($dsn);
Cassie
  • 123
  • 2
  • 9