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!



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

7z: Zip MySQL Database Dump File via Batch File


First, get 7zip Command Line here.

Next, unzip to your desired directory – mine is in C:\7za920.

Based on my previous post, MySQL: Backup Database using Batch File. Add the following lines after echo Database Backups Completed and before pause.

cd C:\7za920
7za a D:\BACKUPS\myapp_%datestr%.sql >Log.txt 2>&1
echo Zipping Backups Completed

Now you are done! Execute the batch file to see the result!

p/s: >Log.txt 2>&1 used for creating a log file for all the process to zip the file.

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