I’d been meaning to get my mits on SQLite for some time and finally found a good project to do so.
Installing SQLite
I’m on ubuntu so the following got things sorted for me:
1 |
sudo apt-get install sqlite3 libsqlite3-dev |
I then noticed I was getting the following
1 |
PHP Fatal error: Class 'SQLite3' not found in /var/www/html/wordpress/testdb.php on line 2 |
1 |
sudo apt-get install php5-sqlite |
1 2 3 4 5 6 7 |
php -i|grep sqlite /etc/php5/cli/conf.d/20-pdo_sqlite.ini, /etc/php5/cli/conf.d/20-sqlite3.ini, PDO drivers => mysql, sqlite pdo_sqlite sqlite3 sqlite3.extension_dir => no value => no value |
Finally, some working php code
While I won’t share my production code with you, here’s what I can share
References: https://www.tutorialspoint.com/sqlite/sqlite_php.htm https://www.digitalocean.com/community/tutorials/how-and-when-to-use-sqlite As per the tutorial above, I created the WINES table and populated it:
1 2 3 4 |
CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20)); INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa"); INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa"); INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France"); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
<?php class MyDB extends SQLite3{ function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo '<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Opened database successfully</span><br>'; } $sql =<<<EOF SELECT * from WINES; EOF; $query = $db->query($sql); // output to table // css $css=<<<EOF <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"/> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> </head> <style> table.redTable { border: 2px solid #A40808; background-color: #EEE7DB; width: 100%; text-align: center; border-collapse: collapse; } table.redTable td, table.redTable th { border: 1px solid #AAAAAA; padding: 3px 2px; } table.redTable tbody td { font-size: 13px; } table.redTable tr:nth-child(even) { background: #F5C8BF; } table.redTable thead { background: #A40808; } table.redTable thead th { font-size: 19px; font-weight: bold; color: #FFFFFF; text-align: center; border-left: 2px solid #A40808; } table.redTable thead th:first-child { border-left: none; } table.redTable tfoot { font-size: 13px; font-weight: bold; color: #FFFFFF; background: #A40808; } table.redTable tfoot td { font-size: 13px; } table.redTable tfoot .links { text-align: right; } table.redTable tfoot .links a{ display: inline-block; background: #FFFFFF; color: #A40808; padding: 2px 8px; border-radius: 5px; } </style> EOF; echo $css; $firstRow = true; echo '<div class="table-responsive"><table id="wines_tbl" class="redtable">'; while ($row = $query->fetchArray(SQLITE3_ASSOC)) { if ($firstRow) { echo '<thead><tr>'; foreach ($row as $key => $value) { echo '<th>'.$key.'</th>'; } echo '</tr></thead>'; echo '<tbody>'; $firstRow = false; } echo '<tr>'; foreach ($row as $value) { echo '<td>'.$value.'</td>'; } echo '</tr>'; } echo '</tbody>'; echo '</table></div>'; echo '<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Operation completed successfully</span><br>'; $db->close(); $js=<<<EOF <script type="text/javascript"> // jQuery - load jsontable jQuery.getScript("/wordpress/js/jquery.jsontable.js", function() { // script is now loaded and executed ; put your dependent JS here. var jsonData = jQuery("#wines_tbl").tableToJSON({ ignoreColumns: [0] }); console.log(jsonData); }); </script> EOF; echo $js; $html.='<hr style="width:100%;">'; $date = time(); $html.='<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:600;">Query execution time:</span><br>'; $html.='<span class="sdata" style="font-family:Verdana;font-size:12px;">'.date("Y-m-d",$date).' '.date( "H:i:s e",$date).'</span><br>'; $date = new DateTime('now'); $tz = new DateTimeZone('Asia/Bangkok'); $date->setTimezone($tz); $html.= '<span class="sdata" style="font-family:Verdana;font-size:12px;">Bangkok: '.$date->format('l F j Y g:i:s A')."</span><br>"; $tz = new DateTimeZone("America/Vancouver"); $date->setTimezone($tz); $html.= '<span class="sdata" style="font-family:Verdana;font-size:12px;">Vancouver: '.$date->format('l F j Y g:i:s A')."</span><br>"; $html .='</body></html>'; echo $html; ?> |

Backing up your SQLite database
1 |
sqlite3 test.db ".backup mysq3db.bak" |
JSON REST API Issues with WordPress Gutenberg
While writing up this post I ran into JSON REST API issues. This post from StackOverflow eventually saved me 🙂
here’s my .htaccess file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# BEGIN WordPress <IfModule mod_rewrite.c> RewriteEngine On RewriteBase / RewriteRule ^index\.php$ - [L] RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule . /index.php [L] SetEnvIf Authorization "(.*)" HTTP_AUTHORIZATION=$1 </IfModule> # END WordPress <Directory "/home/*/Sites"> AllowOverride All FileInfo AuthConfig Limit Indexes Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec Require method GET POST PUT DELETE OPTIONS </Directory> |
I also had to follow the instructions from this post to install the Basic Auth plugin
Leave a Reply
Be the First to Comment!