MySQL and PHP
Tips and Tricks
Rajib Biswas | 22 June 2020
Table of Contents:
The Necessity!
To be honest, I have been struggling with data management for a long time. For official purpose, I needed to perform a set of data analysis that is very difficult without have standardized data set. But the system we were given had very limited accessibility to the end users. So, for a long time, different datasets were kept in different places like Google Sheet, Office Excel and many other places. The problem with that was, it was very easy to break the formatting of a cell of a column and before you know it, your analysis data will be way off than the true picture. Also, because of multiple data sheets in multiple places, it was never synchronized and if anyone had made a correction to his ‘Backup Dataset”, he/she had to tell the others to update theirs as well. So, it was really painful and making an error in the analysis was really common.
For a long time, I had been thinking about building my own standardized dataset, not in a Google Sheet or Excel, in a proper database that I can personally ensure proper entry and retrieve true analysis from. MySQL had been in my mind for a long time, but I have been thinking about something ready-made. So I surfed for a long time, and found nothing that is customizable enough to fit my need. Then all on a sudden I got the job to log thousands of patient sheets in Google Sheet where many fields were just repeatable and with a proper entry form, the whole process could be made easier. So, I finally came back to MySQL. Here I’ll be sharing some tips and tricks for real-life productivity. The article will be updated regularly based on the new problems that I face and how I solved them.
Find and Replace Text in MySQL
If you want to just find a text and replace it with something else in a column in MySQL, use the following code. You may also use this code for correcting mistaken spellings also.
For example, if you want to correct the spelling mistake in the patients table, and the text to replace is in the disease column, you can use the REPLACE function to fins and replace text as:
UPDATE patients SET diseases = REPLACE(diseases, 'Neumonia', 'Pneumonia');
Caution: Don’t try to replace NULL fields with this formula. It’ll just replace everything with nothing.
Create Age Group in MySQL
If you have a list of age and you want to create an age group and count how many of them fall into different age group category, you can easily do that with a simple query.
For example, I want to see how many patients are from different age group. So, my table name is patients and the age of the patients are saved in age_years column. The query to group and count age in MySQL is:
SELECT CASE
WHEN age_years <= 5 THEN '0-5'
WHEN age_years <= 20 THEN '6-20'
WHEN age_years <= 40 THEN '21-40'
WHEN age_years > 40 THEN '>40'
WHEN age_years IS NULL THEN 'Not available'
END AS age,
COUNT(*) total
FROM patients
GROUP BY age
ORDER BY age_years