Porting Amharic Translation System ( PHP + MySQL )

I have tried porting Amharic Translation System from Oracle Database to MySQL.

If you want to know what is Amharic, look at below:
https://en.wikipedia.org/wiki/Amharic

1. Server Module

table: DICTIONARY

CREATE script

DROP TABLE DICTIONARY;
CREATE TABLE DICTIONARY(
    NSEQ       INT(4)       NOT NULL,
    NCHAPTER   INT(2)       NOT NULL,
    SAMHARIC   VARCHAR(40)  NOT NULL,
    SBUNRUI    VARCHAR(10),
    NPAGE      INT(4),
    SSETTO1    VARCHAR(20),
    SJAPANESE1 VARCHAR(40)  NOT NULL,
    STAISHO1   VARCHAR(20),
    SSETTO2    VARCHAR(20),
    SJAPANESE2 VARCHAR(40),
    STAISHO2   VARCHAR(20)
);

execute

source TABLE_DICTIONARY.sql;

INERT script

INSERT INTO DICTIONARY (NSEQ, NCHAPTER, SAMHARIC, SBUNRUI, NPAGE, SSETTO1, SJAPANESE1, STAISHO1, SSETTO2, SJAPANESE2, STAISHO2)
VALUES(1,1,'ጤና ይስጥልኝ','ስም',NULL,NULL,'こんにちは',NULL,NULL,NULL,NULL);
...
...
...
COMMIT;

This file is very long.
If you want it, you can get from bellow:
https://github.com/yokotakaoru/php-renshuu/blob/master/translation/T_AMHARIC_DICTIONARY_INSERT.sql

execute

source INSERT_DICTIONARY.sql;

PROCEDURE PTRANSRATION

drop PROCEDURE PTRANSRATION;
DELIMITER //
CREATE PROCEDURE PTRANSRATION(
/*=====================================================
    Purpose:      Translation
    Created date: 2015/07/17
    Created by:   Yokota Kaoru
  =====================================================*/
/*-----------------------------------------------------
  Parameters ( arguments )
  -----------------------------------------------------*/
     IN  PnDetail   INT           -- Detail flag
    ,IN  PsAmharic  VARCHAR(2000) -- Ordinary sentence
    ,OUT PsJapanese VARCHAR(2000) -- Translated sentence
)
BEGIN
/*-----------------------------------------------------
  Variables
  -----------------------------------------------------*/
    DECLARE LnDone       INT;          -- CURSOR control
    DECLARE LsJapanese   VARCHAR(200); -- Japanese word
    DECLARE LsSAMHARIC   VARCHAR(40);  -- DB
    DECLARE LsSSETTO1    VARCHAR(20);  -- DB
    DECLARE LsSJAPANESE1 VARCHAR(40);  -- DB
    DECLARE LsSTAISHO1   VARCHAR(20);  -- DB
/*-----------------------------------------------------
  CURSOR
  -----------------------------------------------------*/
    DECLARE myCur CURSOR FOR
        select
             SAMHARIC
            ,SSETTO1
            ,SJAPANESE1
            ,STAISHO1
        from
            DICTIONARY
        where
               SBUNRUI IS NOT NULL
            OR NCHAPTER = 0
        order by
            length(SAMHARIC) desc;

/*-----------------------------------------------------
  exception
  -----------------------------------------------------*/
    DECLARE EXIT HANDLER FOR NOT FOUND SET LnDone = 0;

/*-----------------------------------------------------
    ▼▼ Main routine ▼▼
  -----------------------------------------------------*/
   -- format
    SET PsJapanese = PsAmharic;
    SET LnDone = 1;

    -- CURSOR open
    OPEN myCur;

    WHILE LnDone DO
        -- read 1 record
        FETCH
            myCur
        INTO
            LsSAMHARIC, LsSSETTO1, LsSJAPANESE1, LsSTAISHO1;

        -- translation
        SET LsJapanese = LsSJAPANESE1;
        -- Detail flag ON and prefix on
        IF( PnDetail = 1 AND LsSSETTO1 IS NOT NULL) THEN
            SET LsJapanese = CONCAT( '(', LsSSETTO1, ')', LsJapanese );
        END IF;
        -- Detail flag ON and suffix on
        IF( PnDetail = 1 AND LsSTAISHO1 IS NOT NULL) THEN
            SET LsJapanese = CONCAT( LsJapanese, '(', LsSTAISHO1, ')' );
        END IF;

        SET PsJapanese = replace( PsJapanese, LsSAMHARIC, LsJapanese );
    END WHILE;

    -- CURSOR close
    CLOSE myCur;
/*-----------------------------------------------------
    ▲▲ Main routine ▲▲
  -----------------------------------------------------*/
END
//
DELIMITER ;

execute

source PROC_PTRANSRATION.sql;

CALL

CALL PTRANSRATION( 1, 'ጤና ይስጥልኝ።', @GsJapanese );
select @GsJapanese;

2. Client Module

In this case, VC++ or Java are better.
As we know, PHP is weak for screen keeping.
But this post is practice of PHP.

If we use Ubuntu OS, we have to put PHP files under /opt/lampp/htdocs/xampp/.
In this case, we can create php directory like bellow:

/opt/lampp/htdocs/xampp/php

transration01.php

<?php
	// DB connection
	$host          = 'localhost';
	$user_name     = 'root';
	$user_password = ';
	$database_name = 'kaoru';

	// visiting check
	if( isset($_COOKIE['visitcount']) ){
		$visit = $_COOKIE['visitcount'];
	}
	// first visiting
	else{
		$visit = 0;
	}
	$visit++;
	setcookie( 'visitcount', $visit ); // unlimited COOKIE
?>
<!DOCTYPE HTML>
<html>
	<head>
		<meta charset="UTF-8">
		<link rel="stylesheet" href="style_PC.css" type="text/css">
		<title>Amharic translation</title>
	</head>
	<body>
		<form action="<?php
			echo $_SERVER['SCRIPT_NAME']
		?>" method="post">
			<textarea name="tAmharic" rows=6><?php
			// first visiting
			if( $visit <= 1 ) {
				echo "ጤና ይስጥልኝ።";
			}
			// more than 2 times
			else{
				echo $_POST['tAmharic'];
			}
			?></textarea><br>
			<input type="checkbox" name="cDetail" value="Detail" >詳細説明あり<br>
			<input type="submit" value="翻訳">
		</form>
	</body>
</html>
<?php
// translation
if( $_POST ){
	$LsAmharic = $_POST['tAmharic'];
	// Detail Mode
	if( isset( $_POST["cDetail"] ) ){
		$LnDetail = 1;
	}
	else{
		$LnDetail = 0;
	}

	// connecting MySQL
	$mysqli = new mysqli( $host, $user_name, $user_password, $database_name );
	$mysqli->set_charset( 'utf8' );

	// execute stored procedure
	if( $mysqli->query( "CALL PTRANSRATION( " . $LnDetail . " , '" . $LsAmharic. "', @LsJapanese )" ) ){
		$result = $mysqli->query( "SELECT @LsJapanese AS LsJapanese" );
		$obj = $result->fetch_object();
	}
	echo "<textarea rows=6>" . $obj->LsJapanese . "</textarea>";

	// disconnecting MySQL
	$mysqli->close();
}
?>

style_PC.css

@charset "UTF-8";

body{
	margin:  0 auto;
	padding: 10px;
	awidth:   600px;
}

textarea{
	awidth: 100%;
}

Open browser and access to "localhost/xampp/php/transration01.php"

Amharic sentence

input this example

ያንድ ሰው የልብ ትርታ በደቂቃ ከ60 እስከ 80 ይደርሳል። በዓመት 40 ሚሊዮን ያህል ጊዜ ይመታል ማለት ነው። በያንዳንዷ ትርታ ወቅት 1/4 ሊተር ደም ወደ ልብ ይገባል ማለት ነው።  ልብ በአንድ ቀን ውሎው 2200 ጋሎን ያህል ደም ይረጫል።

The translation has been done.

Thank you for reading!