Search
Search Code
Search Code by entering your search text above.
sqlite
mysql to sqlite notes

  ### MySQL to SQLite converterhttps://pypi.org/project/mysql-to-sqlite3/https://github.com/dumblob/mysql2sqlitehttps://stackoverflow.com/questions/3890518/convert-mysql-to-sqlite ```pip instal..

4:33 am, August 3, 2023
mysql
clean excess binary logs from mysql - free up space

Update: 30 July 2023 I actually prefer this command, i just have to figure out why im getting so many binlog files! MYSQL PURGE BINARY LOGS BEFORE '2023-07-30 00:00:00';PURGE BINARY LOGS BEFORE NOW..

1:16 am, July 4, 2023
SQLite
How To Install SQLite on Ubuntu 20.04

  Bash sudo apt install sqlite3 and to get it working with php, pick your version of php and change the following Bash sudo apt-get install php7.4-sqlite3

7:36 am, June 14, 2023
php
sqlite escape string

simple sqlite php escaped string https://www.php.net/manual/en/sqlite3.escapestring.php 

6:02 am, May 10, 2023
php function
function causing slow load time

i had this function matching a uid using a binary select query. The sites were loading in about 8 full seconds according to my load timer, sometimes up to 12 seconds! So this was running on old site ..

2:46 am, April 13, 2023
php functions
show views list function

list items by the view count

10:52 pm, April 12, 2023
php functions
list page links function list_page_links

list top number of page links from selected table test run time on db of 4k items.  select * from pages where publish = 'publish' order by insdate desc limit 10 10 rows (0.005 s)

10:48 pm, April 12, 2023
SQLite
MySQL to Sqlite converter

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the# CREATE block and create them in separate commands _after_ all the INSERTs. # Awk is choos..

1:32 am, June 25, 2022
php
turn on or off error reporting php

Allows you to turn on or off errors in php, like the following: Warning: SQLite3::query(): Unable to prepare statement: 5, database is locked Warning: SQLite3::query(): database is locked in 

5:29 am, June 2, 2022
linux
Install PHP without Apache on Ubuntu

I was actually about to do some coding and then i got distracted with this annoying error on vscode. I keep getting this error on vscode: I had been ignoring it, but i thought fine, ill fix it and..

12:30 am, June 1, 2022
Site Bugs
Search Duplicating Bugs

Still having issues with the word above duplicating, not sure why.  Search Page PHP - Get Search Value $search_value_encoded = htmlentities($search_value); Check for Search value, and then ..

6:39 am, May 20, 2022
docker
unable to write to a sqlite database with docker

turns out this was not a docker specific problem, i was getting the readonly database error.  even if the database has full write permissions, the directory also needs to have write permissions ..

1:46 am, May 8, 2022
core
Core Function : list_search_by_class_return_links - List Searches by Class Type and Return Links

Function that list's the latest and top searches by the class name and return a list of links, with the search totals on them.  I wanted to extract the top searches and the latest searches by da..

2:35 am, March 10, 2022
sqlite
function to delete older sqlite records when the limit is reached

I had an issue where a sqlite database was hitting over 100,000 records and this was causing quite a lot of lag and crashing on my server, the number of records was not really meant to be this high, s..

4:20 am, January 18, 2022
php functions
make a nice readable date using a timestamp string function make_nice_date

handy if you have a timestamp like this: 2022-01-10 00:12:42 and want it to look a bit nicer. like this... 12:19 am, January 10, 2022

12:12 am, January 10, 2022
php functions
Spam IP Database and delete button

The Background and Reason for the SPAM IP Database and Delete I actually wrote up half of this article and was still testing the function and lost the whole post, which will teach me for adding it st..

12:24 pm, November 22, 2021
sqlite
count total results from distinct sqlite

a query to count the total items found on a distinct query

2:02 am, November 11, 2021
php functions
list all array json function list_all_array_json

I wrote this a while ago, and i always forget the complex functions i write so ill add it here.  This loads all fields and then returns them as json. 

5:51 am, November 3, 2021
php
sqlite fetchArray into another array

was having an issue when trying to return a multi array using sqlite over multiple rows. Stack Overflow Inspiration  I tried a few of these but it was not incrementing key, and also it was only..

2:55 am, October 25, 2021
php
list_all_array an early stages of list all function that uses an array to pass in main variables

I think this is more neat than passing in individual variables into the list_all function, seems to work ok so far.  Still in early stages of this listing function, more needs to be built into i..

12:15 am, October 18, 2021
php
timeline class and function

this is a class extend that generates a timeline based on structured data and order's the items by year decending. this code will not work out of the box as it has other requirements, but it is a goo..

5:02 am, October 8, 2021
php
views list function for checking what views have been made on the current week

this is related to the views class that i added with monthly week number and yearly views, this is the function that i use to extract the top viewed pages for the week num, month num and year num. I ..

8:54 am, October 7, 2021
php
load array load all items from an array while in a sqlite load sql loop

i find this useful when loading a bunch of items in a class while looping through data, rather than assigning variables manually i just add the array of item names into the class to begin with and the..

8:36 am, October 7, 2021
php
How to record your own page views with PHP, and make them into weekly monthly and yearly charts

Building a better Simple Page View Counter Currently this site has a very basic view counter on it.  Here is how it currently works. Load the page, function checks if the page has a existing r..

2:01 am, September 23, 2021
php
load from fields array php class function

this class function loads from multiple fields from an array, much easier than passing in a bunch of variables into a function nice and clean, the only issue is you need to remember what fields to add..

12:28 pm, September 22, 2021
site documentation
function to load one latest item with a start val load_latest_single_num

This is the same as load oldest item based on insdate then update the insdate to now - load_latest_single_update but it allows you to add a start number to the range, so allows you to access the 2nd, ..

4:06 am, September 2, 2021
php
PHP/SQLite - Load Random Item

loads a random item from the current class / db using sqlite.

3:53 am, August 8, 2021
site bugs
still more comment spam

this is getting annoying now. so much spam. i already have the google recapture, but spam is still getting through. who runs this stuff, bots i guess. annoying.  update: 10 August 2021 Still..

6:16 am, August 6, 2021
linux
simple backup to google drive fron linux

i added these scripts a while ago to backup mysql databases and web files to google drive a while ago, i just run them on a daily cron to copy the backups every day. the files so the bain backup_a..

1:04 am, June 27, 2021
site documentation
use a list group for listing by category page [todo]

change this to a list group, should make it neater. Page to update: https://kruxor.com/list-category/code/  so for some reason this is listed using this function PHP $list_wiki_menu = $clas..

12:02 am, June 17, 2021
site documentation
Comments Listing should be a custom list rather than using search

For some reason when i listed the comments under posts on this site, i used the search function and then searched for the related uid, which maybe was easier at the time than adding an additional cate..

3:57 am, June 14, 2021
site documentation
load the latest single item only

this function loads the lastest item from the selected class

1:24 am, June 11, 2021
site documentation
load oldest item based on insdate then update the insdate to now - load_latest_single_update

i keep finding these functions that i wrote ages ago and forgot about so ill list them here so maybe i can remember them. I think this one was to loop through items, so it grabs the oldest item and t..

1:19 am, June 11, 2021
php
check column exists in table sqlite

this will return 1 if the column name exists in the table or 0 if it does not

12:21 pm, May 17, 2021
css
github logo background png css

just the github transparent logo as a css class, yes it should probably be a svg, but here it is in all its PNG glory.

5:10 am, February 10, 2021
site bugs
linking search results to view pages [finished] 🤣

I was thinking it might be useful to link the search term to the page, so that it can be found more easily using that search. For example, Link this search text (somehow) to the post page and display..

2:44 am, January 12, 2021
php
Fixing PHP SQLite database is locked warning - Unable to execute statement: database is locked

Apparently this error is caused by not closing the database after using it.  Warning: SQLite3::query(): Unable to execute statement: database is locked. You can close the database using the fol..

12:24 am, August 26, 2020
core
What is Core?

17 Aug 20 core is php content management system where the content is editable from the front end of the site, so there is no real back end to this system. i designed it to be easily extended so every..

6:52 am, August 17, 2020
sql
Create a UTF8 General MySQL Database with Command

This will create a UTF8 database with the character encoding utf8_general_ci replacing the with your database name. I was manually doing this in adminer before this and selecting the encoding, this i..

4:05 am, August 13, 2020
php
php mysql table exists function

this checks if a mysql table already exists in the selected database and returns true or false

4:25 am, December 2, 2019
php
php convert date now into a sql timestamp

an easy way to get the current date from php into a mysql or sql timestamp format, this uses the server time

12:43 am, November 25, 2019
php
create a mysql or sqlite current timestamp with php

just in case you need to create a CURRENT_TIMESTAMP using php, this formats the current date into the following.

5:45 am, May 27, 2019
php
PHP MySQL vs SQLite Count Items Function

I have been writing a few functions using sqlite and mysql. There are a couple of slight differences which can be seen in this count items_function.

3:40 am, February 25, 2019
core
Core Class - Basic Core Structure

A Core class that can be used as a base for all content types and uses the extend classes to define the database variables. So basically you can set up core class and then create sub extend classes to..

11:33 pm, December 13, 2018
php
sqlite check table name exists

check that the table name exists in a sqlite database, this function it taken from a class.

12:44 am, October 24, 2018
php
sqlite basic sql and loop results

basic loop through sql results using sqlite and php $sql = "select $field_name from $db_table_name"; $out = ""; $result = $db->query($sql); if($result) { while($row = $result->fetchA..

6:20 am, August 31, 2018
php
sqlite delete data older than timestamp days

good if you need to delete data older than a certain date, i tested this and it seems to delete data older than 2 days but might have had the time stamp incorrect or something. DELETE FROM $db_tabl..

6:20 am, August 31, 2018
Welcome

This is my test area for webdev. I keep a collection of code here, mostly for my reference. Also if i find a good link, i usually add it here and then forget about it. more...

Subscribe to weekly updates about things i have added to the site or thought interesting during the last week.

You could also follow me on twitter or not... does anyone even use twitter anymore?

If you found something useful or like my work, you can buy me a coffee here. Mmm Coffee. ☕

❤️👩‍💻🎮

🪦 2000 - 16 Oct 2022 - Boots
Random Quote


Me
Random CSS Property

font

The font CSS shorthand property sets all the different properties of an element's font. Alternatively, it sets an element's font to a system font.
font css reference