MySQL - Joel Lipman .Com

MySQL: Count occurrences of words in a column

: Joel Lipman; MySQL; 12 March 2020; Hits: 5456

What?
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.

: Ike Francis; MySQL; 14 December 2016; Hits: 3440

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:

copy raw

$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'");
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'+1000000 WHERE 'user' = 'Fred'");

$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'");
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'+1000000 WHERE 'user' = 'Fred'");

What if one of the queries fails? To mitigate the chances of misplacing our millions due to some possible error we must ensure that both queries are executed with the expected result before committing any changes to the data. This where transactions are useful. We can run the queries, then check if some conditions are met before committing the changes in the data:

Category: MySQL :: Article: 644

: Joel Lipman; MySQL; 13 June 2016; Hits: 1947

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

Why?
I use MS Excel for organizing data and then converting to MySQL commands. Unfortunately, the MS Excel software adds tab characters to delimit the columns...

How?

UPDATE `mytable` SET `myColumn` = TRIM(CHAR(9) FROM TRIM(`myColumn`));

Source: Stack Overflow - How to Remove Tabs

Convert foreign characters to English alphabet

: Joel Lipman; MySQL; 13 June 2016; Hits: 2917

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

Why?
A content management system (CMS) that I'm working on has just gone international and started including the names of places in other countries. This is nice but its search engine doesn't work properly as it thinks "riviere" is different to "rivière". We need to ensure that a search for any of these kind of words will return results of similarly typed/sounding words.

How?
Here's just a splurge of SQL but I use this often enough:

: Joel Lipman; MySQL; 22 October 2014; Hits: 4181

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

Why?
I have several database tables which replicate country names and I would rather they all use the ccTLD two letter code. This article was written because it took me so long to work it out.

How?

UPDATE  `table_to_update` a
        INNER JOIN `table_to_read` b
            ON a.`CountryName` COLLATE utf8_general_ci LIKE b.`CountryName` COLLATE utf8_general_ci
SET     a.`ccTLD` = b.`ccTLD`

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

: Joel Lipman; MySQL; 28 July 2014; Hits: 159114

Applies to:

MySQL Database v5.0.45
MySQL Workbench v6.0.8.11354 build 833

What?
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 when I try to "data export" the database.

Why?
The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say that I haven't solved anything; but this is a production database I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven website.

How?

: Joel Lipman; MySQL; 05 December 2013; Hits: 2603

What?
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.

Why?
I'm being tasked to work with EPM (Microsoft Enterprise Project Management) more and more. Similar systems have popped out that support some form of time recording and activity logging. The example below however is within a LAMP/MySQL environment but the SQL basics are here to help me adapt it to whatever environment people keep throwing at me.

What I want:

copy raw

ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek
----------- ----------- ---------- -------- ------- -------- -------------- -------------
2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00
2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30
2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30
2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15
2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45
2013-12-07  Saturday    -          -        -       -        0:00           37:45
2013-12-08  Sunday      03:00      04:00    -       -        4:00           41:45

ThisDate ThisDay StartTime TimeOut TimeIn EndTime TotalTimeToday TotalTimeWeek
----------- ----------- ---------- -------- ------- -------- -------------- -------------
2013-12-02 Monday 09:00 12:00 13:00 17:00 7:00 7:00
2013-12-03 Tuesday 08:45 12:00 13:30 17:45 7:30 14:30
2013-12-04 Wednesday 09:00 12:30 13:30 17:00 7:00 21:30
2013-12-05 Thursday 10:00 12:15 12:45 17:15 7:45 29:15
2013-12-06 Friday 07:00 12:00 13:00 16:30 8:30 37:45
2013-12-07 Saturday - - - - 0:00 37:45
2013-12-08 Sunday 03:00 04:00 - - 4:00 41:45

Category: MySQL :: Article: 541

: Joel Lipman; MySQL; 09 September 2013; Hits: 11514

What?
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?

copy raw

-- return all records that contain non-alphanumeric characters
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$';

-- return all records that are non-alphanumeric but ignore underscores
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$';

-- return all records that contain non-alphanumeric characters
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$';
-- return all records that are non-alphanumeric but ignore underscores
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$';

Category: MySQL :: Article: 521

SQL: Use CASE for Relevance column

: Joel Lipman; MySQL; 03 August 2013; Hits: 3621

What?
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:

copy raw

-- where @ThisSearch is a posted (and sanitized) variable

SET @ThisSearch:="Brains";

SELECT
        columnID,
        columnFullName,
        CASE
                WHEN columnFirstName LIKE @ThisSearch THEN 20
                WHEN columnFullName LIKE @ThisSearch THEN 10
                WHEN columnLastName LIKE @ThisSearch THEN 10
                WHEN columnFullName LIKE @ThisSearch THEN 1
        END as relevance
FROM
        myTable
WHERE
        s.columnPublished

-- where @ThisSearch is a posted (and sanitized) variable
SET @ThisSearch:="Brains";
SELECT
columnID,
columnFullName,
CASE
WHEN columnFirstName LIKE @ThisSearch THEN 20
WHEN columnFullName LIKE @ThisSearch THEN 10
WHEN columnLastName LIKE @ThisSearch THEN 10
WHEN columnFullName LIKE @ThisSearch THEN 1
END as relevance
FROM
myTable
WHERE
s.columnPublished

Category: MySQL :: Article: 516

Country Lookup by IP address CSV

: Joel Lipman; MySQL; 17 March 2013; Hits: 6859

What?
So this is an article to serve as documentation for one of the data sources available to download for FREE from this site.

Why?
It's useful to have a CSV list of all the IP ranges and their allocated country. Our use here was to create a refreshable country by IP address list that we can use when parsing our website logs to check where visitors were from.

Note: we also use and love Advanced Web Statistics (awstats) but this was so that you could have your own country lookup script.

The Download
Should contain the following:

create_countriesipranges_table.sql: SQL to create a pre-populated table into a MySQL database.
iana_ipv4_address_space_registry.csv: comma separated values list of all countries, regions and their respective IP ranges.
ip_lookup.php: to use the database
License.txt: Released under GNU/GPL
ReadMe.txt

What is it?
I wanted my own IP lookup script to tell me the country that is allocated a given IP address, this is what I use.

The scripts in the folder you have downloaded are to create a lookup function using a MySQL database and a PHP script. As well as a CSV file equivalent.

How to use:

Download from my site: «Download»
Run the SQL script against a test database,
1. Modify the table name to use if necessary.
2. Uncomment the drop statement if running for the second time.
Modify the PHP file to use database login credentials,
1. $db_host = "localhost"; // Database host name
2. $db_user = "my_database_user"; // User for database authentication
3. $db_pass = "my_database_password"; // Password for database authentication
4. $db_name = "my_database"; // Database name
5. $db_table = "mytable_countryipranges";// Database table
Upload the PHP file to your website
Test by using a web browser to visit: http://www.mysite.com/ip_lookup.php?ipv4=123.123.123.123 where www.mysite.com is your website where you uploaded this script. // should return China

Any questions, suggestions?
We are looking to add the ability to generate this data using other sources and in time a region by IP for more accuracy.

Feel free to feedback. It's FREE to download unlike others which is probably why I made the generator in the first place but I do like to make people sign up so I can gauge the level of interest.

Export/Import Database using MySQL Workbench

: Joel Lipman; MySQL; 05 February 2013; Hits: 18946

What?
This article isn't unique as there are other tutorials out there. This is meant to detail a process to export a database using MySQL Workbench and then to import it into another database.

Why?
If you're confined to use this product then this is how to do it. Personally, even a web-based app such as PhpMyAdmin would fare better.

How?

: Joel Lipman; MySQL; 03 February 2013; Hits: 4885

What?
This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

Why?
I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

How?
For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change: