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:

copyraw
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'");
$mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'+1000000 WHERE 'user' = 'Fred'");
  1.  $mysqli->query ("UPDATE 'accounts' SET 'balance' = 'balance'-1000000 WHERE 'user' = 'Bob'")
  2.  $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

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

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:

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` 

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?

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:
copyraw
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
  1.  ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek 
  2.  ----------- ----------- ---------- -------- ------- -------- -------------- ------------- 
  3.  2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00 
  4.  2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30 
  5.  2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30 
  6.  2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15 
  7.  2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45 
  8.  2013-12-07  Saturday    -          -        -       -        0:00           37:45 
  9.  2013-12-08  Sunday      03:00      04:00    -       -        4:00           41:45 
Category: MySQL :: Article: 541

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?
copyraw
-- 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\\_]+$';
  1.  -- return all records that contain non-alphanumeric characters 
  2.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'
  3.   
  4.  -- return all records that are non-alphanumeric but ignore underscores 
  5.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$'
Category: MySQL :: Article: 521

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

report   list   would   program   parameter   name   data   license   file   source   windows   solution   using   added   database   mysql   error   server   where   google   site   zoho   used   website   system   following   select   uploaded   script   code   search   note   need   joomla   date   version   first   time   display   could   files   work   user   table   find   function   form   value   page   same   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.