Code Library: SQLite

store.db file queried in your PHP files; your sqlite connection is made at the beggininng of all pages: $sDB = new SQLite3("store.db");

SQLite  Description Result

Your SQLITE database file contains all your product, category and information pages content. You can query your database using the CREATE, UPDATE, SELECT, and DELETE statements in your PHP files.

<?php
$sDB->exec("create table my_data (
   product_id INTEGER NOT NULL DEFAULT 0,
   description TEXT,
   qty INTEGER,
   price REAL,
   date INTEGER,
   status_id INTEGER DEFAULT 1
   )");
?>

Creates a new table called my_data

This example is a simple DELETE to remove a product with the product_id of 1

<?php
$sDB->exec("delete from product where product_id=1");
?>

Deletes the product with the product_id of 1

This example shows you how to view all the fields for a table by using the SELECT statement in one of your PHP files.

<?php
$rs = $sDB->query("select * from category limit 1");
while($data = $rs->fetchArray(SQLITE3_ASSOC)){
  foreach($data as $field=>$value)   {
    print $field.'<br />';
  }
}
?>

category_id
url
vendor_category_id
vendor_id
user_id
stores
type
name
heading
menu_title
parent_id
place
products
fid
description
subs
layout
state
nl2br
status_id
ordering
discount
discount_code
ext_url
meta_keywords
meta_description
created
updated

Update a product value:

<?php
$sDB->exec("update product set price='19.95' where sku='XYZ'");
?>

Updates the price for the product with the sku of XYZ to $20.00

Making changes to your SQLite database

Data for your website is stored in two databases: the central vendo MySQL database, which contains customer and order records, which you do not have access to, and your local SQLite database, which contains your product data, which you have full access to. (You can also add tables and fields to your SQLite database). Keep in mind you should not alter existing table structure unless you really know what you are doing.

The above SELECT example shows you how to query your sqlite database to show fields for the category table. Your database also contains the following tables. Note that table structure is subject to change.

Running queries using the SQLite Admin function

If you want to update your database manually you can also execute queries using the SQLite Admin function.

×