Untitled

via Pixelpipe

Posted via web from Matt's thoughts

Untitled

via Pixelpipe

Posted via web from Matt's thoughts

Populating and serving a temporary SQLite database file and serving.

Recently I had the requirement in PHP to populate a SQLite file with data from a MySQL database and make it available for download. I was using PDO objects for MySQL, and found I could do the same for SQLite, so the simple answer became to create a temporary SQLite file with PDO, populate it from the existing MySQL PDO object (by querying the required data and inserting it into the SQLite file-no shortcut there unfortunately) allow the user to download it and then delete it. The sample code below excludes the selection from the existing database (which is comparitively trivial) but includes the rest.
One major thing worth noting (and not illustrated here) is that if you're going to be inserting more than a few dozen records, it's worth wrapping everything in a transaction. This may seem overkill for a temporary file that will fail anyway and hit the try-catch if there's a probelm, but it results in massive performance improvements due to PHP doing all the SQL work in memory and writing the transaction to file in one operation. Just don't go doing it with hundreds of megabytes of data ;)

<?php function

outputDownloadHeaders

($path, $filename, $binary = false) { header("Pragma: public"); header("Expires: 0"); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: pre-check=0, post-check=0, max-age=0', false); header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT'); // Browser specific headers $browser = $_SERVER['HTTP_USER_AGENT']; if(preg_match('/MSIE 5.5/', $browser) || preg_match('/MSIE 6.0/', $browser)) { header('Pragma: private'); // C in control must be lowercase header('Cache-control: private, must-revalidate'); // Must be a number for IE header("Content-Length: ".filesize($path)); } else { header("Content-Length: ".(string)(filesize($path))); } header('Content-Type: application/x-download'); header('Content-Disposition: attachment; filename="'.$filename.'"'); // Tell the client file is binary if ($binary) { header('Content-Transfer-Encoding: binary'); } } function populateSqlite($sqlitefilepath) { // Open the file-creates new file if necessary $pdo = new PDO("sqlite:".$sqlitefilepath); // Create table in the db if needed $pdo->query("CREATE TABLE IF NOT EXISTS test_table ". "(id INT NOT NULL, text VARCHAR(100) NOT NULL, PRIMARY KEY (id));"); // Populate with some data $pdo->query("INSERT INTO test_table (id, text) values (0, 'Hello');"); $pdo->query("INSERT INTO test_table (id, text) values (1, 'World');"); $pdo->query("INSERT INTO test_table (id, text) values (2, 'from');"); $pdo->query("INSERT INTO test_table (id, text) values (3, 'PHP');"); $pdo->query("INSERT INTO test_table (id, text) values (4, 'and');"); $pdo->query("INSERT INTO test_table (id, text) values (5, 'SQLite'); "); // Connection closed by scope return true; } // The filename to present to the client $dbfilename = 'sqlite.sq'; // Temporary file to hold the sqlite db on the host $dbfilepath = sys_get_temp_dir() . uniqid('sqlite') . '.sq'; try { // Populate new file with some data if (populateSqlite($dbfilepath)) { // Prompt the user to save the file locally outputDownloadHeaders($dbfilepath, $dbfilename, true); // Stream the file to the client $file = fopen($dbfilepath, 'rb'); if ($file) { while(!feof($file) and (connection_status()==0)) { print(fread($file, filesize($dbfilepath))); flush(); } fclose($file); } } } catch (Exception $ex) { echo "Oops, something went wrong: $ex->getMessage()"; } // Clean up the file unlink($dbfilepath); ?>

Posted via email from Matt's thoughts

Game- and mobile dev gun for hire

I took a big step today and handed in my resignation at I-Imagine to
begin doing freelance contract development, with a focus on mobile-
and game development and consulting.
I've signed for my first gig, which will have me working on a really
exciting project for Luma Arcade from June. Of course I'll constantly
be on the lookout for new opportunities, so if you need anything from
advergaming to corporate mobile clients (or know anyone who might) let
me know!
I've got a wide range of experience behind me, including development
on platforms ranging from PC, web and mobile (including iPhone) to PS3
and Xbox 360. Language/tech wise the list includes C++, C#, PHP, Java,
various middleware platforms and soon Flash and XNA.
In addition to development and consulting in these areas, I can offer
training as well.

That's my sales pitch for now, if you have any projects you could use
me on or just feel like donating to keep me in biscuits and tea, I'm
easily reachable on LinkedIn, Twitter, Facebook and all those great
places :)

---
Regards,
Matt

(sent from my mobile, please excuse any typos)

Posted via email from Matt's thoughts