#SignMyTL – A Complete Microblog using PHP & MySQL (Facebook Like Timeline)

#Website:

A website is a location to the internet that maintains one or more webpages.

#Microblog: 

Microblog is a social media site to which a user makes short, frequent posts.

#SignMyTL

#SignMyTL is a digital autobiograph for one who wants to create log of the key activities of their daily life on the Web. The key feature to this application is that user can share blog post, upload images, youtube videos, facebook timline layout, share immediate comments, smiley support, infinite scroll of website.

Why #SignMyTL?
1. I want my private TimLine public.
2. I want to make a history of my life.
3. I want to connect with my friends differently.
4. I am what my friends talk about me.
5. I want to say something silently.
6. Social Networks are getting fake day by day.
7. I want to be true to myself.

How to get #SignMyTL?

Click here to download #SignMyTL
Click here to view Live Demo

How to install #SignMyTL?

1. Extract and upload SignMyTL directory using ftp client software.

Directory Structure :

SignMyTL 1.0

  • assets
    images
    javascripts
    smileys
    stylesheets
  • imagecache
  • includes
  • uploads

2. Use signmytl.sql to create database & tables.
3. Modify the database credentials in the SignMyTL/config.php.
4. Access your SignMyTL using http://your.domain.name/SignMyTL.

Install SSL Certificate – godaddy

[ Login to Godaddy Sales Account ]
1. Login to your godaddy sales account -> Go to SSL Certificates -> Click on Manage

2. Click on View Status

3. Click on download from Certificate Management Options -> Extract from the zip file (You will get two .crt files)

[ Login to Hosting cPanel ]

4. Login to your hosting cPanel -> Go to Home -> Go to SSL / TLS -> Click on Certificates (crt)

5. Browse and upload crt file (That you downloaded from the Sales, follow point 3)

6. Next you will be followed by the system.

 

[dfads params=’groups=-1′]

How to implement Heatmap on Websites using PHP, MySQL, jQuery and Ajax?

I was assigned a task when an Interviewer interviewed me and that was a Technical question. I was asked to design a system that can gather click data from a webpage and show it later in form of a Heatmap.

[dfads params=’groups=-1′]

This system can be integrated into any website which records visitor clicks on the page. The goal of of this will be to track visitor’s mouse clicks on various elements of the page and later show them accurately in form a Heatmap. The owner should be able to see aggregated click data in form of an overlay on top of his webpage. The areas in the heatmap that are red more clicks compared to the areas that are white.

After googling through the internet I found some resources which were very interesting. I devoted some time and tried to implement a demo. Please follow the following Steps:

1. Client Code : Create a file index.php and include the following code below. This is the page where users make clicks.

[code]
<script type=’text/javascript’ src="js/jquery-lib.js"></script> <!– jQuery library –>
<script type=’text/javascript’ src="js/hm-dev.js"></script> <!– Log JS –>

<title>99-Websites.com Labs</title>
<body>
<div style="text-align:center">
<img src="99-ws-labs.jpg" />
</div>
</body>

[/code]

2. The Ajax Call : Create a JS file named as hm-dev.js. This file is included in the above index.php page. Add the snippet below:

[code]
/*
Author : Dev
Date : 12/06/2015
*/

jQuery(document).ready(function() {
jQuery(document).click(function(e){
//alert(window.location.href.toString().split(window.location.host)[1]+" — "+e.pageX+" — "+e.pageY);
log_click(window.location.href.toString(), e.pageX, e.pageY);
});
var canvas = document.getElementsByTagName(‘canvas’)[0];
canvas.style.display = "none";
});

function log_click(page, x, y){ // log clicks for heatmap
jQuery.ajax({
type: ‘POST’,
url: ‘log_click.php’,
crossDomain: true,
data: "x_coord="+x+"&y_coord="+y+"&page="+page,
dataType: ‘json’,
success: function(responseData, textStatus, jqXHR)
{
if (responseData== 1){
console.log("Click logged: " + x + ", " + y);

}
else{
console.log("Error – click not logged " + x + ", " + y);
}
},
error: function (responseData, textStatus, errorThrown)
{
console.warn(responseData, textStatus, errorThrown);
alert(‘CORS failed – ‘ + textStatus);
}
});
}
[/code]

3. Log the Clicks : Create a PHP file named with log_click.php and add the following snippet.

[code]

<?php
header(‘Access-Control-Allow-Origin: *’);
header(‘Access-Control-Allow-Methods: POST, GET, OPTIONS’);
header(‘Access-Control-Max-Age: 1000’);
header(‘Access-Control-Allow-Headers: Content-Type’);

//echo json_encode(array("your_request_was" => $_POST[‘page’].$_POST[‘x_coord’].$_POST[‘y_coord’]));

include(‘config.php’); //Create config.php file and define $dbuser, $dbpass & $dbname

if(isset($_POST[‘x_coord’])){
$page = htmlentities($_POST[‘page’]);
if($page == "/"){ $page = "/index.php"; }
$xcoord = htmlentities($_POST[‘x_coord’]);
$ycoord = htmlentities($_POST[‘y_coord’]);
$time = date( ‘Y-m-d H:i:s’);

$conn = mysql_connect(‘localhost’, $dbuser, $dbpass);
mysql_select_db($dbname, $conn);
$page = mysql_real_escape_string($page);
$xcoord = mysql_real_escape_string($xcoord);
$ycoord = mysql_real_escape_string($ycoord);

mysql_query("INSERT INTO clicks (timestamp, page, x, y) VALUES (‘$time’, ‘$page’, $xcoord, $ycoord)");
mysql_close($conn);
echo "1";
}
else
{
echo ‘0’;
}

?>

[/code]

4. Create MySQL Table : Create table with following fields:

[code]

CREATE TABLE IF NOT EXISTS `clicks` (
`timestamp` datetime NOT NULL,
`page` varchar(200) NOT NULL,
`x` int(255) NOT NULL,
`y` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[/code]

That’s it, the first part is over. After implementing the above steps you will be able to log clicks on the MySQL Table. The next and the main part is viewing the heat maps.

5. Create the Admin Page : Create PHP page named with admin.php. This page will lists out all the domains on which the client side code is added. When the links are clicked, their respective heatmaps will be displayed below the same page.  Admin Code is listed below:

[code]

<?php
/*
Author : Dev
Date : 12/06/2015
*/
include("config.php");
$con2 = mysql_connect(‘localhost’, $dbuser, $dbpass);
mysql_select_db($dbname, $con2);
$query = "SELECT distinct page FROM `clicks` WHERE 1";
$result = mysql_query($query);
?>
<html>
<head>
<title>Heatmap Admin</title>
<script type=’text/javascript’ src="js/jquery-lib.js"></script>

<script type=’text/javascript’ src="js/heatmap.js"></script>
<script>
jQuery(document).ready(function(){
var heatmapInstance = h337.create({
container: document.querySelector(‘.display’),
radius: 25
});

jQuery(".showcanvas").click(function(){
//location.reload();
//alert(jQuery(this).attr(‘value’));
var timescale = "day";
var page = jQuery(this).attr(‘value’);
//alert(page);
var postData = "timescale="+timescale+"&page="+page;
jQuery.ajax({
type:"POST",
dataType: "json",
data: postData,
beforeSend: function(x) {
if(x && x.overrideMimeType) {
x.overrideMimeType("application/json;charset=UTF-8");
}
}, url: ‘heatmap.php’,
success: function(data) {

if (data.amount > 0){
for (i=0; i<data.amount; i++){
heatmapInstance.addData({
x: data[i].x,
y: data[i].y,
value: 2
});
}
}
}
});

});
});
</script>
</head>
<body >
<div style="width:100%; border:1px solid; black;">
[Click on the Websites below to View their respective HeatMaps]<br>
<ul style="font-size:12px;">
<?php
while($row = mysql_fetch_assoc($result)){
?>
<li><a href="#" class="showcanvas" value="<?=$row["page"]?>"> <?=$row["page"]?></a></li> <br/>

<?php

}
?>
</ul>
</div>
<div class="display" style="width:100%;height:100%;border:1px solid; black;float:right;">
</div>

</body>
</html>

[/code]

The main logic behind the code is the heatmap.js library. In order to understand in detail you can explore the following references:

1. http://www.patrick-wied.at/static/heatmapjs/example-click-heatmap.html

2. http://rossmarks.co.uk/blog/?p=683

3. http://www.d-mueller.de/blog/cross-domain-ajax-guide/

You can view the Demo here: 

1. Client / Users Page : http://99labs.net where you can make some clicks on the webpage.

2. Admin Page : http://dewendra.com.np/labs/hm/admin.php where you can view the Heatmap.

Also, You can download the whole code from here : Download

[dfads params=’groups=-1′]

PS : The client code can be installed into any website and their logs can be maintained on the remote server. If you go through the client side code you can see the ajax code implemented with CORS (Cross Origing Resource Sharing) using jQuery.

Import large database tables from CSV files using oracle_loader driver in Oracle External Table

After trying various techniques to import csv data to oracle tables, I found this as the simplest and the fastest way to import large databases into oracle tables. This technique uses oracle_loader driver in Oracle External Table. The basic is that external table displays data by reading from a physical file.

[dfads params=’groups=-1′]

Steps:
1. Create a physical directory in one of your drive. Paste your csv data into the same directory.

2. Execute the script below on sqldeveloper.

3. Congratulations, play with your data tables.

[code]
create or replace directory my_data_app as ‘D:my_data_app’;

CREATE TABLE BULK_KYC_UPLOAD_BY_EMPLOYER_AI
( office_id number(11) ,
uan varchar2(12) ,
cur_mid varchar2(25) ,
document_type varchar2(1) ,
document_name varchar2(85) ,
document_no varchar2(25) ,
ifsc_code varchar2(11) ,
employee_name varchar2(100) ,
expiry_date varchar2(50) ,
edu_quali_flag varchar2(1) ,
phy_hand_flag varchar2(1) ,
phy_hand_cat_flag varchar2(1) ,
gender_flag varchar2(1) ,
number_worker_flag varchar2(1) ,
martial_status_flag varchar2(1) ,
establishment_id varchar2(15) ,
scan_document_flag number(11) ,
alredy_veryfied_flag number(11) ,
document_scan_image varchar2(100) ,
dob varchar2(50) ,
doj varchar2(50) ,
doe varchar2(50) ,
father_husband_name varchar2(85) ,
fs_flag varchar2(1) ,
bulk_kyc_tracking_id varchar2(15) ,
text_file_upload_date varchar2(50) ,
online_verification number(1),
ts_online_verification varchar2(50) ,
employer_verification number(1) ,
ts_employer_verification varchar2(50) ,
field_office_verification number(1),
ts_field_office_verification varchar2(50) ,
added_by varchar2(1) ,
missing_detail_source_flag varchar2(1) ,
ts varchar2(50),
duplicate_flag varchar2(2),
duplicate_ts varchar2(50)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_data_app
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
SKIP 1
LOGFILE my_data_app:’data.log’
BADFILE my_data_app:’data.bad’
DISCARDFILE my_data_app:’data.disc’
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘"’
MISSING FIELD VALUES ARE NULL
(
office_id,
uan ,
cur_mid ,
document_type ,
document_name,
document_no,
ifsc_code,
employee_name,
expiry_date,
edu_quali_flag,
phy_hand_flag,
phy_hand_cat_flag,
gender_flag,
number_worker_flag,
martial_status_flag,
establishment_id,
scan_document_flag,
alredy_veryfied_flag,
document_scan_image,
dob,
doj,
doe,
father_husband_name,
fs_flag,
bulk_kyc_tracking_id,
text_file_upload_date,
online_verification,
ts_online_verification,
employer_verification,
ts_employer_verification,
field_office_verification,
ts_field_office_verification,
added_by,
missing_detail_source_flag,
ts,
duplicate_flag,
duplicate_ts
)
)
LOCATION (my_data_app:’kyc_16012015.csv’)
)reject limit unlimited;

select * from BULK_KYC_UPLOAD_BY_EMPLOYER_AI;

select * from BULK_KYC_UPLOAD_BY_EMPLOYER_AF rownum < 200;

drop table BULK_KYC_UPLOAD_BY_EMPLOYER_AI;

insert into bulk_kyc_upload_by_employer_af select * FROM bulk_kyc_upload_by_employer_ai;
commit;
[/code]

In order to Export large database table into a dump file using oracle_datapump driver in Oracle External Table, Click Here
[dfads params=’groups=-1′]

Export large database table into a dump file using oracle_datapump driver in Oracle External Table

[dfads params=’groups=-1′]

There are various ways to export oracle database tables. Here, I was dealing with large database tables when I was working at one of the india’s biggest government company. I had to import and export crores of tuples from and to csv files.
Following is the simplest example to unload data from huge oracle database to a dump file using oracle_datapump driver in Oracle External Table.

[code]create or replace directory my_data_app as ‘D:my_data_app’;

create table bulk_kyc_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_data_app
LOCATION (‘bulk_kyc_xt.dmp’)
)
AS SELECT * FROM bulk_kyc_upload_by_employer_af;

select * from bulk_kyc_xt;

drop table bulk_kyc_xt;

[/code]

In order to import oracle table from a csv file using oracle external table, click here.

[dfads params=’groups=-1′]