PHP Coding Solutions

Thursday, August 25, 2016

SQL queries to manage hierarchical or parent-child relational

/*create the tbl*/
CREATE TABLE UserType(
Id BIGINT NOT NULL,
Name VARCHAR(100) NOT NULL,
ParentId BIGINT NULL
)

/*insert data into table*/
INSERT INTO `usertype` (`Id`, `Name`, `ParentId`) VALUES
(NULL, 'user_1', NULL),
(NULL, 'user_2', '1'),
(NULL, 'user_3', '2'),
(NULL, 'user_4', '3'),
(NULL, 'user_5', '4'),
(NULL, 'user_6', '5'),
(NULL, 'user_7', '6'),
(NULL, 'user_8', '7'),
(NULL, 'user_9', '8'),
(NULL, 'user_10', '9');

/*regular join to get detail*/
SELECT ChildUserType.Id, ChildUserType.Name, ParentUserType.Id, ParentUserType.Name
FROM UserType AS ChildUserType
LEFT JOIN UserType AS ParentUserType ON ChildUserType.ParentId = ParentUserType.Id;

/* all posible childs of @userTypeId */
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS
(
SELECT *
FROM UserType WHERE ParentId = @userTypeId
UNION ALL
SELECT UserType.* FROM UserType JOIN tblChild ON UserType.ParentId = tblChild.Id
)
SELECT *
FROM tblChild
OPTION(MAXRECURSION 32767)

/*row posible parents in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
FROM UserType AS FirtGeneration
WHERE ParentId IS NULL
UNION ALL
SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
CAST(CASE WHEN Parent.Parents = ''
THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM UserType AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId
)
SELECT *
FROM Hierarchy
OPTION(MAXRECURSION 32767)

/*row posible childs in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Childs)
AS
(
SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
FROM UserType AS LastGeneration
WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)
UNION ALL
SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
CAST(CASE WHEN Child.Childs = ''
THEN(CAST(Child.ChildId AS VARCHAR(MAX)))
ELSE(Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM UserType AS PrevGeneration
INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId
)
SELECT *
FROM Hierarchy
OPTION(MAXRECURSION 32767)

Tuesday, August 2, 2016

Delete Directory with file in php

Tuesday, June 14, 2016

export bata base with condition Mysql

SELECT *
FROM `sma_payments_process`
WHERE `date` LIKE '%2016-04%'
AND `sellerId` LIKE 'A1G8X3F1VR5IF'
into outfile '/home/evanik/erp/download/data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Wednesday, March 30, 2016

HTML / CSS Popup div on text click

<html>
<head>
<title>VIJAY EXAMPLE</title>
<style>
.black_overlay{
display: none;
position: absolute;
top: 0%;
left: 0%;
width: 100%;
height: 100%;
background-color: black;
z-index:1001;
-moz-opacity: 0.8;
opacity:.80;
filter: alpha(opacity=80);
}
.white_content {
display: none;
position: absolute;
top: 25%;
left: 25%;
width: 50%;
height: 50%;
padding: 16px;
border: 16px solid orange;
background-color: white;
z-index:1002;
overflow: auto;
}
</style>
</head>
<body>
<p>This is the main content. To display a lightbox click <a href = "javascript:void(0)" onclick = "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block'">here</a></p>
<div id="light" class="white_content">This is the lightbox content. <a href = "javascript:void(0)" onclick = "document.getElementById('light').style.display='none';document.getElementById('fade').style.display='none'">Close</a></div>
<div id="fade" class="black_overlay"></div>
</body>

Friday, March 18, 2016

convert txt to csv in php

http://php.net/manual/en/function.fgetcsv.php

Sample usage for small files:-

-------------------------------------

<?php

$importer = new CsvImporter("small.txt",true);
$data = $importer->get();
print_r($data);
?>




Sample usage for large files:-

-------------------------------------

<?php

$importer = new CsvImporter("large.txt",true);

while($data = $importer->get(2000))

{
print_r($data);

}
?>




And heres the class:-

-------------------------------------

<?php
class CsvImporter
{

    private $fp;

    private $parse_header;

    private $header;

    private $delimiter;

    private $length;

    //--------------------------------------------------------------------

    function __construct($file_name, $parse_header=false, $delimiter="\t", $length=8000)

    {

        $this->fp = fopen($file_name, "r");

        $this->parse_header = $parse_header;

        $this->delimiter = $delimiter;

        $this->length = $length;

        $this->lines = $lines;



        if ($this->parse_header)

        {

           $this->header = fgetcsv($this->fp, $this->length, $this->delimiter);

        }



    }

    //--------------------------------------------------------------------

    function __destruct()

    {

        if ($this->fp)

        {

            fclose($this->fp);

        }

    }

    //--------------------------------------------------------------------

    function get($max_lines=0)

    {

        //if $max_lines is set to 0, then get all the data



        $data = array();



        if ($max_lines > 0)

            $line_count = 0;

        else

            $line_count = -1; // so loop limit is ignored



        while ($line_count < $max_lines && ($row = fgetcsv($this->fp, $this->length, $this->delimiter)) !== FALSE)

        {

            if ($this->parse_header)

            {

                foreach ($this->header as $i => $heading_i)

                {

                    $row_new[$heading_i] = $row[$i];

                }

                $data[] = $row_new;

            }

            else

            {

                $data[] = $row;

            }



            if ($max_lines > 0)

                $line_count++;

        }

        return $data;

    }

    //--------------------------------------------------------------------


}
?>

Monday, December 28, 2015

Check string is an email or not in PHP

<?php
$email = "amke01@example.com";
if(eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)) {
echo "Valid email address.";
}
else {
echo "Invalid email address.";
}
?>

Wednesday, November 25, 2015

How to send FormData objects with Ajax-requests in jQuery?