mysql search select case insensitive

From my development experience, I always think that mysql SELECT query with LIKE is not case sensitive, but anyhow I did experience such a situation that mysql is case sensitive. After few hours research on this area finally I got the clue to solve.

Some people suggest that, when you insert to database, you can lower / upper the character so that it will not have case sensitive issue, but sometimes you just change the coding of people framework. So how to solve it?

The clue is from the table structure. When you create a field, there is an option for the Encoding and Collation. If you need something case sensitive make sure you set it as utf8_bin. On the other hand, if you want something NOT case sensitive the set it as utf_general_ci. the CI at the base stand for CASE INSENSITIVE.

Below is the screen shot for my sql client.

Screen Shot 2015-04-15 at 5.23.01 PM

*Tips: If you have problem to click select fromt he Collation, please make sure the Binary column is not checked.

 

Thank you for reading, I hope this trick can help you solve your SELECT statement.

Opencart with Malaysia GST Setting

opencart_gst_malaysia

Happy April Fool…, is your e-commerce ready for GST setting? Opencart is very simple to do that.

Prerequisites: 

1. Geo Zones already setup Malaysia Address.

Geo Zones
Geo Zones

1. Login to Your Admin Account.

2. System -> Localisation ->Taxes -> Tax Rates

Tax Rates Setting
Tax Rates Setting

3. System -> Localisation ->Taxes -> Tax Class

The Tax Class is base on the Shipping Address. So it will only calculate if the Geo Zones requirement had matched.

Tax Class for 6% GST
Tax Class for 6% GST

4. Add GST ID No. In your Store Address field.

This is easy way to add your GST ID No. in your e-commerce, which is under your address. So each of your invoice will be added the GST ID No.

Add GST ID No in Your Address Field
Add GST ID No in Your Address Field

5. Display Price with Tax (Optional) System -> Settings-> Option (Tab), Scroll until TAXES Section

Display Price with Tax
Display Price with Tax

6. Add Tax Class to the Product that affected GST

Product Page Add Tax Rates
Product Page Add Tax Rates

7. Shipping Cost with Tax Rate (In this case I am using Flat Rate)

Shipping Rate with GST
Shipping Rate with GST

8. Confirm Order Summary:

Order Summary (Price with Tax), MacBook is the product added with 6% GST Tax Class.  I feel this kind of display a bit weird.
(Option 1:) Order Summary (Price with Tax), MacBook is the product added with 6% GST Tax Class. I feel this kind of display a bit weird.
(Option 2:) Order Summary (Price with Tax), MacBook is the product added with 6% GST Tax Class. Personally I more prefer this method.
(Option 2:) Order Summary (Price with Tax), MacBook is the product added with 6% GST Tax Class. Personally I more prefer this method.

9. Change the INVOICE to TAX INVOICE

When you access to admin, you can view the order and generate an invoice for the particular order.

Original Invoice format, there is not TAX INVOICE
Original Invoice format, there is not TAX INVOICE

How you fix to TAX INVOICE? This one required a bit technical knowledge.

Go to opencart_folderadminlanguageenglishsaleorder.php

Look for

$_[‘text_invoice’]                            = ‘Invoice’;

Replace to

$_[‘text_invoice’]                            = ‘Tax Invoice’;

This is after edit the word to Tax Invoice.

GST Requirement - Tax Invoice
GST Requirement – Tax Invoice

That’s all, just that simple in order for your eCommerce to meet GST requirement. Thank you for reading.

Do leave a comment below if you need any help.

 Update: How to do in Opencart Malaysia GST Exclude Tax Free Zone