Nasrul Hazim Bin Mohamad

Change MySQL Data Directory


The reason I wrote this post is: don’t let you database eat up your main partition! Ever!

Training: Mobile Apps Development with Intel XDK


Location: Medina Arabic Training & Solutions, Bangi Sentral (Above Melaka Kitchen Restauurant)


Date: 23th & 24th January 2016

Available Seats: 20 seats


Package Intel XDK API
2 Days 150
1 Day 50 120


Package Intel XDK API
2 Days 250
1 Day 100 175

* Fee payment must be made first in order to reserved your seats.




Register here if you unable to see the Google Form embed in this post.



Year 2016, InsyaAllah, next year to be a year of sharing knowledge of what I’ve learnt so far. Following are the trainings I would like to conduct:

1. Mobile Apps Development with jQuery Mobile
2. Mobile Apps Development with Intel XDK (incoming)
3. API Development with Slim Framework (incoming)
4. PHP Basic / Advanced
5. Web Development with CakePHP 3 (incoming)
6. Web Development with Laravel 5 (incoming)
7. Web Development with Yii Framework 2 (incoming)
8. WordPress for Developers – Beginner Level
9. WordPress Theme Development
10. WordPress Plugin Development

Those status incoming, it’s the one I’m preparing the syllabus & materials, summarizing the process, and so on. Hope more things can be share other than mentioned above such as the tools I’ve used along the process of the development – Sublime Text 3, SQLYog, Putty, Git and so on

MySQL: Backup Database using Batch File


Backup MySQL Database in Windows

The Backup User

CREATE USER 'backup'@'backup' IDENTIFIED BY 'backup'; 
GRANT FILE, LOCK TABLES, SELECT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'backup';

The Script

echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
	set dow=%%i
	set month=%%j
	set day=%%k
	set year=%%l
set datestr=%year%_%month%_%day%
cd C:wampbinmysqlmysql5.6.12bin
mysqldump --user=backup --password=backup myapp > D:BACKUPSmyapp_%datestr%.sql
echo Database Backups Completed

You may run this script using Scheduler to make your backup by weekly, monthly, etc.

jQuery Plugin: MySQL DateTime to Input Date Format


Simple solutions to convert MySQL DateTime to Input Date Format. You may change the format as your requirement.

  getInputDateFromMySQLDate: function(value) {
  	var t = value.split(/[- :]/);

	var d = new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]);
	var day = ("0" + d.getDate()).slice(-2);
	var month = ("0" + (d.getMonth() + 1)).slice(-2);

	return d.getFullYear()+"-"+(month)+"-"+(day);
  getDateFromMySQLDate: function(value){
  	var t = value.split(/[- :]/);

	return new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]);



Web Services: Slim Framework + Eloquent(Laravel)


Setting up the Restful API is easy with Slim Framework & Eloquent(Laravel). Here are the steps:

  1. Install required components via composer – Slim Framework + Eloquent(Laravel)
  2. Create a database called web_services and create users table.
  3. Setting up Eloquent
  4. Setting up Routes
  5. Final Touch
Web Service Restful API

Web Service Restful API

Install the necessary components using composer – create a composer.json and save it in a working directory and run composer install.

    "name": "Web Services",    
    "require": {
        "php": ">=5.3.0",
        "slim/slim": "2.*",
        "slim/views": "0.1.*",
        "illuminate/database": "5.0.*",
        "illuminate/events": "5.0.*"
    "autoload": {
        "classmap": [

We’re creating a simple web services for Users – Simple CRUD. Create a database called web_services and run the following SQL statement to create user table.

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)

Next, setting up the Eloquent. Create a database.php in config folder(Please create this folder in the working directory) folder.

$database = [
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'web_services',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',

use IlluminateDatabaseCapsuleManager as Capsule;

$capsule = new Capsule;


// Set the event dispatcher used by Eloquent models... (optional)
use IlluminateEventsDispatcher;
use IlluminateContainerContainer;
$capsule->setEventDispatcher(new Dispatcher(new Container));

// Make this Capsule instance available globally via static methods... (optional)

// Setup the Eloquent ORM... (optional; unless you've used setEventDispatcher())

Once we’re done with Eloquent, next we going to set up the routes for Users CRUD operations. Save your time, copy & paste the following codes and save it in config/routes.php.


$app->get('/api/users', function () {
    echo User::all()->toJson();

$app->get('/api/users/:id',  function($id) {
	try {
		echo User::find($id)->toJson();
	} catch (Exception $e) {
		echo '{"error":{"text":'. 'Unable to get the web service. ' . $e->getMessage() .'}}';

$app->get('/api/users/search/:query', function($query) {
	echo User::where('name', '=', $query)->get()->toJson();

$app->post('/api/users/add', function() use ($app) {
    try {
        $user = new User;

        $user->name = $app->request()->post('name');
        $user->phone = $app->request()->post('phone');
        $user->email = $app->request()->post('email');

        if($user->save()) {
            echo '{"message":"Successfully add new user"}';
        } else {
             echo '{"message":"Failed to add new user"}';
    } catch (Exception $e) {
        echo '{"error":{"text":'. 'Unable to get the web service. ' . $e->getMessage() .'}}';


$app->put('/api/users/update/:id', function($id) use ($app) {
    try {
        $user = User::find($id);

        $user->name = $app->request()->post('name');
        $user->phone = $app->request()->post('phone');
        $user->email = $app->request()->post('email');

        if($user->save()) {
            echo '{"message":"Successfully update user info"}';
        } else {
             echo '{"message":"Failed update user info"}';
    } catch (Exception $e) {
        echo '{"error":{"text":'. 'Unable to get the web service. ' . $e->getMessage() .'}}';

$app->delete('/api/users/:id', function($id) {
	$user = User::find($id);
    if($user->delete()) {
        echo '{"message":"Successfully delete user"}';
    } else {
         echo '{"message":"Failed to delete user"}';

OK, here the final touch! 2 things to do, one the index.php and the other one is .htaccess. Here the index.php. Please take note, we require the header("Access-Control-Allow-Origin: *");, to ensured that we can access this Restful API from other domains. There might be security issues. Comments are welcomed.

try {
	header("Access-Control-Allow-Origin: *");
	require 'vendor/autoload.php';
	require 'config/database.php';
	require 'config/initialize.php';
} catch (Exception $e) {
	echo '{"error":{"text":'. 'Unable to start up the web service. ' . $e->getMessage() .'}}';

The .htaccess.

RewriteEngine On

# Some hosts may require you to use the `RewriteBase` directive.
# If you need to use the `RewriteBase` directive, it should be the
# absolute physical path to the directory that contains this htaccess file.
# RewriteBase /

RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^ index.php [QSA,L]

Source code available at: Web Service(Restful API)

I Have 3 demo available:

  1. Web Service App
  2. Web Service Demo
  3. Web Service Mobile Demo


Micro-framework: Slim


Setting up Slim Application

  • Create a folder for your project, mine called slim
  • create a composer.json in the directory and open up your Command Prompt / Terminal and run composer install. Later you should have something similar to Screenshot #2.

    Screenshot #1


    Screenshot #2

Setting up RedBeanPHP4

  • Download RedbeanPHP4
  • Create a folder named RedBeanPHP4 in vendor directory and extract download zip file above and copy the rb.php into slim/vendor/RedBeanPHP4 – see Screenshot #2.
  • Include the rb.php in slim/vendor/autoloader.php
  • Then, add MySQL connection settings.

Let’s start it!

  • Create a index.php in slim directory and add the following:
    mf-slimframeworkBasically what’s in this index.php is to add new user and view a user details.
  • You may need to create new database, then new table named users. I used the following fields – id, name, email, created and modified
  • Next, when the database and tables created, you may run http://localhost/slim/users/add/Nasrul Hazim/ and you may want to add more records. Suppose each time new user created, you will be redirected to the view user info page – http://localhost/slim/users/view/1 and see the next screenshot – I only var_dump the user details and you want to add some nice view or probably pass a JSON encoding.

Training – PHP & MySQL: Basic to Advanced


MySQL: Save Arabic Characters / Words


Database / Table for Arabic Usage

  1. Charset – UTF-8
  2. Collation – utf8_unicode_ci


# Database
CREATE DATABASE `db_arabic`CHARACTER SET utf8 COLLATE utf8_unicode_ci;

# Table
CREATE TABLE `db_arabic`.`users`( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `created` DATETIME, `modified` DATETIME, PRIMARY KEY (`id`) ) CHARSET=utf8 COLLATE=utf8_unicode_ci; 

In CakePHP, make sure to set 'encoding' => 'utf8' in database.php.

You can have Arabic Lorem Ipsum at Multilanguage Lorem Ipsum Generator

For web page, please ensure to add meta tag, charset `utf-8′, as following:

&lt;meta charset=&quot;utf-8&quot; /&gt;

Reference: How to save Arabic Words Into MySQL Table

RDBMS Data Limit


== Microsoft SQL Server ==
Max DB Size: 524,272 TB (32 767 files * 16 TB max file size)
Max Table Size: 524,272 TB
Max Row Size: 8,060 bytes (Unlimited)
Max Columns Per Row: 30,000
Max Blob / Clob Size: 2 GB
Max Char Size: 2 GB
Max Number Size: 126 bits
Min Date Value: 0001
Max Date Value: 9999
Max Column Name Size: 128

== MySQL ==
Max DB Size: Unlimited
Max Table Size: MyISAM storage limits: 256 TB; Innodb storage limits: 64 TB
Max Row Size: 64 KB
Max Columns Per Row: 4,096
Max Blob / Clob Size: 4 GB (longtext, longblob)
Max Char Size: 64 KB (text)
Max Number Size: 64 bits
Min Date Value: 1000
Max Date Value: 9999
Max Column Name Size: 64

== PostgreSQL ==
Max DB Size: Unlimited
Max Table Size: 32TB
Max Row Size: 1.6TB
Max Columns Per Row: 250 – 1600 ( depending on type)
Max Blob / Clob Size:
– 1 GB (text, bytea)[] – stored inline or
– 4 TB (stored in pg_largeobject)[]
Max Char Size: 1GB
Max Number Size: Unlimited
Min Date Value: −4,713
Max Date Value: 5,874,897
Max Column Name Size: 63

== Oracle ==
Max DB Size: Unlimited (4 GB * block size per tablespace)
Max Table Size: 4 GB * block size (with BIGFILE tablespace)
Max Row Size: 8 KB
Max Columns Per Row: 1,000
Max Blob / Clob Size: 128 TB
Max Char Size: 32,767 B
Max Number Size: 126 bits
Min Date Value: −4712
Max Date Value: 9999
Max Column Name Size: 30

== SQLite ==
Max DB Size: 128 TB (231 pages * 64 KB max page size)
Max Table Size: Limited by file size
Max Row Size: Limited by file size
Max Columns Per Row: 32,767
Max Blob / Clob Size: 2 GB
Max Char Size: 2 GB
Max Number Size: 64 bits
Min Date Value: No DATE type
Max Date Value: No DATE type
Max Column Name Size: Unlimited