Nasrul Hazim Bin Mohamad

Change MySQL Data Directory

Apr
08

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

Jan
18

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


 

Date: 23th & 24th January 2016

Available Seats: 20 seats

Fees:

Student
Package Intel XDK API
2 Days 150
1 Day 50 120

 

Public
Package Intel XDK API
2 Days 250
1 Day 100 175

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

Training-Intel-XDK-NHM-20160121

 

 

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

Y2016

Dec
18

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

Aug
29

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';
FLUSH PRIVILEGES;

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
cls
mysqldump --user=backup --password=backup myapp > D:BACKUPSmyapp_%datestr%.sql
echo Database Backups Completed
pause

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

jQuery Plugin: MySQL DateTime to Input Date Format

May
07

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

$.extend({
  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]);
  }
});

References:

  1. http://stackoverflow.com/questions/3075577/convert-mysql-datetime-stamp-into-javascripts-date-format
  2. http://stackoverflow.com/questions/12346381/set-date-in-input-type-date

Web Services: Slim Framework + Eloquent(Laravel)

May
02

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": [
            "models"
        ]
    }
}

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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;

$capsule->addConnection($database);

// 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)
$capsule->setAsGlobal();

// Setup the Eloquent ORM... (optional; unless you've used setEventDispatcher())
$capsule->bootEloquent();

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.

<?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.

<?php
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

Apr
22

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.
    mf-composer

    Screenshot #1

    mf-structure

    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
    mf-autoloader
  • 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/email@gmail.com 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.
    mf-db
    mf-readbean-orm


Training – PHP & MySQL: Basic to Advanced

Jan
02

MySQL: Save Arabic Characters / Words

Aug
13

Database / Table for Arabic Usage

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

Example:


# 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

Apr
01

== 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)[http://grokbase.com/t/postgresql/pgsql-general/12bsww982c/large-insert-leads-to-invalid-memory-alloc] – stored inline or
– 4 TB (stored in pg_largeobject)[http://www.postgresql.org/docs/9.3/static/lo-intro.html]
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

Reference: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems