Good thing MySQL has the IF() function that enables us to update a column by switching values to 0 if current value is 1 and vice versa in just 1 query.

This way, we do not have to waste resources by querying for the current value and then executing an update query.

Setting the sort order between 2 rows in MySQL can be done in 1 line by making use of the IF() function.

I had a scenario where I had move up and move down functionalities to set the order of my database entries.

Let us say, you have columns ID and SORT_ORDER.

This line of code should do the trick.

I came across a situation where I had to filter out a height column so that I will only get rows between, say 5’6 as the minimum. Now, if let us say you are required to filter out all rows with a height between 5’3 and 6’5, you cannot possibly enumerate each height in the IN keyword of SQL. Well, you can, but that is not maximizing the capabilities of programming then. What you can do is extract the values of the height column and get the feet and inches value respectively. With that, you can multiply the foot value by 12 since 1 foot is equal to 12 inches, then add the remaining inches to the resulting product.

Using the MySQL function substring_index(), you can retrieve a substring value at the specified index. So for example, if your height values are in this format 5’6, you can do this in MySQL …

The MySQL query above filters out rows that have a minimum height of 5’6.

Related Posts Plugin for WordPress, Blogger...