Scripts, systems & field notes.

MySQL 12 Mar 2020 24068

MySQL: Count occurrences of words in a column

This is an article to document how to output the most frequently used words in a MySQL database column. How? Note this only applies to MySQL.

Read note →

MySQL 04 Oct 2018 20376

MySQL Transactions in PHP

Sometimes it is critical to ensure a group of queries are all executed successfully to maintain the integrity of our data. Let's say we have a banking app where we need to subtract funds from one account and add funds to another: $mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'"...

Read note →

MySQL 04 Oct 2018 10708

Trim values along with the tab

A quick article on how to trim in MySQL along with getting rid of any leading or trailing tab characters.

Read note →

MySQL 04 Oct 2018 21965

Convert foreign characters to English alphabet

A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and return the English equivalent.

Read note →

MySQL 22 Oct 2014 15024

MySQL Updating a database table from another table

A quick article on how to populate a database column from another table using a string comparison.

Read note →

MySQL 28 Jul 2014 191377

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

This is a quick article on how to get around the problem of backing up your MySQL database when attempting to "Data Export" using MySQL Workbench. This is not regarding the connection issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears whe...

Read note →

MySQL 07 Dec 2013 34477

Generate a Timesheet in MySQL

This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a database and auto-completes your timesheet.

Read note →

MySQL 09 Sep 2013 21720

MySQL: Find all non-alphanumeric rows

Quick note on how to do this. I was tasked with cleaning up an english database by replacing all special alphabets (ë to e) and non-alphanumeric symbols with URL friendly characters. How? -- return all records that contain non-alphanumeric characters SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'...

Read note →

MySQL 03 Aug 2013 12728

SQL: Use CASE for Relevance column

So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a column of relevance and sorts the rows accordingly. How? This has to be a real quick one for a dropdown search field which has to find relevant terms to autofill/autocomplete a search form: -- where @ThisSea...

Read note →