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.

category
category_id UNIQUE, primary key url UNIQUE vendor_category_id vendor_id user_id stores type name heading menu_title VARCHAR (100) DEFAULT NULL parent_id place products fid description subs layout state nl2br TINYINT (1) status_id ordering discount discount_code ext_url meta_keywords meta_description created updated
content (info pages)
content_id INT (11) NOT NULL PRIMARY KEY parent_id vendor_content_id vendor_id stores place data time url VARCHAR (30) title VARCHAR (100) heading menu_title section enabled nl2br TINYINT (1) ext_url meta_keywords meta_description
grouping
grouping_option
product
product_id INTEGER PRIMARY KEY parent_product_id vendor_id vendor_product_id stores price FLOAT sale_price FLOAT low_price FLOAT high_price FLOAT wholesale_price FLOAT wholesale_sale_price FLOAT wholesale_price2 FLOAT wholesale_price3 FLOAT wholesale_price4 FLOAT wholesale_price5 FLOAT wholesale_price6 FLOAT wholesale_price7 FLOAT wholesale_price8 FLOAT wholesale_price9 FLOAT value_price FLOAT cost_price FLOAT stock sub_products fid VARCHAR (100) NOT NULL url VARCHAR (255) NOT NULL name VARCHAR (255) NOT NULL summary TEXT details TEXT deleted category_id category_id2 category_id3 category_id4 category_id5 category_id6 category_id7 category_id8 category_id9 created updated sku brand colour size colour_label size_label attr1 attr2 attr3 listing_id gateway_id user_id trademe_listing_id trademe_enddate trademe_thumb trademe_relist ext_url img_id img_url_sm img_url_md img_url_lg reviews rating ranking orders sold views similar additional also_bought supplier_id voucher min_qty max_qty unit_qty ship_free ship_free_local ship_only_local ship_extra ship_extra_type ship_apply_to dangerous_goods file avail weight height width length depth volume status_id ordering nl2br meta_keywords meta_description variations variants children
product_attribute_label
product_attribute_value
product_basic_option
parent_product_id vid vidx pvid pvidx cvid cvidx selection stock sku status_id
product_basic_variation
parent_product_id vid pvid pvidx cvid cvidx selection ordering
product_booking
product_comments
comment_id parent_comment_id product_id category_id content_id user_id user_name user_firstname user_lastname user_email user_url user_town status comment_title comment date_created date_updated customer_rating auth_user_id auth_response viewer_rating viewer_response
product_grouping
product_img
product_id vendor_id med_w med_h small_w small_h large_w large_h img_id title active
product_multi
product_price_matrix
product_price_variation
product_price_variation_data
variation
vid name display_name select_type required variation_type status legend
variation_option
vid vidx pvid pvidx option VARCHAR (200) stock sku status_id price sale_price wholesale_price wholesale_price2 wholesale_price3 wholesale_price4 wholesale_price5 wholesale_price6 wholesale_price7 wholesale_price8 wholesale_price9

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.

Shop for:

Customer Comments

“Hi there, they loved it! I am now giving them the biscuits for their daytime feeds and only using wet food in the morning as they are still so young (7weeks today). I am thrilled with the price. I've heard about your cat line as well and am spreading the word.
Thanks for the samples, very welcome!
thanks again,”
Jo, Stanmore Bay

View all customer comments...