Skip to content
Permalink
be8e06e4c5
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Go to file
 
 
Cannot retrieve contributors at this time
874 lines (735 sloc) 29.8 KB
<?php
//set content type and xml tag
header("Content-type: text/xml");
/**************************/
// replace these information if needed
$db_name = "test";
$table_name = "";
$host_name = "localhost";
$user_name = "root";
$password = "";
$DBlink = mysqli_connect($host_name, $user_name, $password);
$currentRow = 0;
/***************************/
if (isset($_POST["upload"])){
$timestamp = (new DateTime())->getTimestamp();
$data = parseData($_POST["upload"]);
$result = insertToDb($timestamp, $_POST["header"],$data);
ajaxReturn($result);
}
else if (isset($_POST["processData"])){
$serviceUrl = $_POST["processData"];
$processFileHandle = $_POST["processFileHandle"];
$result = processData($serviceUrl,$processFileHandle);
ajaxReturn($result);
}
else if (isset($_POST["getData"])){
if (isset($_POST["getStart"])&&isset($_POST["getEnd"])){
$result = getData($_POST["getData"],$_POST["getStart"],$_POST["getEnd"]);
ajaxReturn($result);
}
else {
$result = getData($_POST["getData"],"","");
ajaxReturn($result);
}
}
else if (isset($_POST["getCurrentData"])){
$result = getData($_POST["getCurrentData"], $currentRow, 17);
ajaxReturn($result);
}
else if (isset($_POST["getTrackData"])){
$result = getTrackData($_POST["fileHandles"], $_POST["name"], $_POST["chrom"], $_POST["start"], $_POST["end"]);
ajaxReturn($result);
}
else if (isset($_POST["getStartEndTrackData"])){
$result = getStartEndTrackData($_POST["fileHandles"], $_POST["name"], $_POST["chrom"]);
ajaxReturn($result);
}
else if (isset($_POST["loadTooltip"])){
$result = loadTooltip($_POST["chrom"],$_POST["trackName"], $_POST["type"], $_POST["min"],$_POST["max"],$_POST["fileHandle"]);
ajaxReturn($result);
}
else {
loadTable();
}
function processData($serviceUrl,$processFileHandle){
$data = getData($processFileHandle,"","");
//print_r($data);
$result = httpCurlPost("http://localhost/loadingtable/data_process_service.php", $data);
$resultArray = json_decode(trim($result), TRUE);
//print_r($resultArray) ;
$timestamp = (new DateTime())->getTimestamp();
$header = $resultArray[0];
//print_r($header);
//array_pop($resultArray);
$tableName = insertToDb($timestamp,$header, $resultArray);
return $tableName;
}
function httpCurlPost($url, $params) {
//$postData = "data=";
//$postData = http_build_query($params);
// print_r($postData);
$postData = json_encode($params);
// print_r($postData);
//echo $postData;
//create name value pairs seperated by &
// foreach ($params as $k => $v) {
// $postData .= $k . '=' . $v . '&';
// }
// rtrim($postData, '&');
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HEADER, false);
// curl_setopt($ch, CURLOPT_POST, count($postData));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, "data=".$postData);
$output = curl_exec($ch);
//var_dump($output);
//echo $output;
curl_close($ch);
return $output;
}
function parseData($data){
$result = [];
$rows = explode("\n", $data);
foreach($rows as $row){
$rowArray = explode("\t", $row);
array_push($result, $rowArray);
}
return $result;
}
function getData($fileHandle,$start,$end){
$count = $end - $start;
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$resArray = [];
if ($start !== "" && $end !== ""){
$sql = "SELECT * FROM {$db_name}.{$fileHandle} LIMIT ".$start.",".$count;
}
else {
$sql = "SELECT * FROM {$db_name}.{$fileHandle}";
}
$res = mysqli_query ($link,$sql);
$header = [];
$firstRow = mysqli_fetch_assoc($res);
$firstRowArray = [];
foreach ($firstRow as $key => $value) {
array_push($header, $key);
array_push($firstRowArray, $value);
}
array_push($resArray, $firstRowArray);
while($row = mysqli_fetch_row($res)){
array_push($resArray, $row);
}
array_unshift($resArray, $header);
return $resArray;
//echo json_encode($resArray);
}
function mergeExonsArray($exons, $scale){
$resArray = [];
$mergedExon = $exons[0];
for ($i=1; $i < count($exons); $i++) {
if ($exons[$i][0] - $mergedExon[1] > 1/$scale){
array_push($resArray, $mergedExon);
$mergedExon[0] = $exons[$i][0];
$mergedExon[1] = $exons[$i][1];
}
else{
if ($exons[$i][1] > $mergedExon[1]){
$mergedExon[1] = $exons[$i][1];
}
}
}
$LastElement = end($resArray);
reset($resArray);
if ($mergedExon[1] != $LastElement[1] || count($resArray) == 0){
array_push($resArray, $mergedExon);
}
return $resArray;
}
function mergeExons($geneStart, $geneEnd, $Exstart, $Exend, $start,$end, $scale){
$ExStartArray = explode(",",substr($Exstart,0,-1));
$ExEndArray = explode(",",substr($Exend,0,-1));
if ($geneEnd-$geneStart < 1/$scale){
return [[$ExStartArray[0],max($ExEndArray)]];
}
else{
$resArray = [];
if(count($ExStartArray) > 1){
$mergedExon = [$ExStartArray[0],$ExEndArray[0]];
for ($i=1; $i < count($ExStartArray); $i++) {
if ($ExStartArray[$i] - $ExEndArray[$i-1] > 1/$scale){
array_push($resArray, $mergedExon);
$mergedExon[0] = $ExStartArray[$i];
$mergedExon[1] = $ExEndArray[$i];
}
else{
if ($ExEndArray[$i] > $ExEndArray[$i-1]){
$mergedExon[1] = $ExEndArray[$i];
}
}
}
$LastElement = end($resArray);
reset($resArray);
if ($mergedExon[1] != $LastElement[1]){
array_push($resArray, $mergedExon);
}
}
else{
array_push($resArray, [$ExStartArray[0],$ExEndArray[0]]);
}
return $resArray;
}
}
function getHG19TrackData($fileHandle,$chrom,$start,$end){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$resArray = [];
$exonArray = [];
$scale = 1000/($end - $start);
$sql = "SELECT `chrom`, `txStart`, `txEnd`, `name2`, `score`, `exonStarts`, `exonEnds` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `chrom` = '".$chrom."'";
$sql .= " AND (((`txStart` BETWEEN $start AND $end) OR (`txEnd` BETWEEN $start AND $end))";
$sql .= " OR (`txStart` <= $start AND `txEnd` >= $end))";
$sql .= " ORDER BY `txStart` * 1 ASC";
$res = mysqli_query ($link,$sql);
$mergedRect = [];
$firstRow = mysqli_fetch_row($res);
$exons = mergeExons($firstRow[1],$firstRow[2],$firstRow[5], $firstRow[6], $start,$end, $scale);
//print_r($exons);
foreach ($exons as $key => $value) {
array_push($exonArray, $value);
}
$mergedRect = [$chrom,$firstRow[1],$firstRow[2],"",""];
$noMergedRect = false;
while($row = mysqli_fetch_row($res)){
$tempStartPix = ($row[1] - $start) * $scale;
$tempEndtPix = ($row[2] -$start)* $scale;
$mergedRectStartPix = ($mergedRect[1] - $start) * $scale;
$mergedRectEndtPix = ($mergedRect[2] - $start) * $scale;
$exons = mergeExons($row[1],$row[2],$row[5], $row[6], $start,$end, $scale);
foreach ($exons as $key => $value) {
array_push($exonArray, $value);
}
if ($noMergedRect){
$mergedRect = [$chrom,$row[1],$row[2],"",$row[4]];
$noMergedRect = false;
continue;
}
//print_r($row);
if (is_numeric($row[4])){
array_push($resArray, $mergedRect);
array_push($resArray, [$chrom,$row[1],$row[2],"",$row[4]]);
$noMergedRect = true;
continue;
}
if ($tempStartPix - $mergedRectEndtPix > 1){
//$mergedRect[5] = mergeExonsArray($mergedRect[5],$scale);
array_push($resArray, $mergedRect);
$mergedRect[1] = $row[1];
$mergedRect[2] = $row[2];
//$mergedRect[5] = [$exons];
}
else{
if ($mergedRect[2] < $row[2]){
$mergedRect[2] = $row[2];
}
// foreach ($exons as $key => $value) {
// array_push($mergedRect[5], $value);
// }
}
}
$LastElement = end($resArray);
reset($resArray);
if ($mergedRect[1] != $LastElement[1]){
array_push($resArray, $mergedRect);
}
$resExon = mergeExonsArray($exonArray,$scale);
return [$resArray,$resExon];
}
function getCpgTrackData($fileHandle,$chrom,$start,$end){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$resArray = [];
$scale = 1000/($end - $start);
$fileHangleArray = explode(",", $fileHandle);
$cpgFile = $fileHangleArray[0];
$shelveFile = $fileHangleArray[1];
$shoreFile = $fileHangleArray[2];
foreach ($fileHangleArray as $key => $handle) {
$type = "";
if ($key == 0){
$type = "cpg";
}
else if ($key == 1){
$type = "shelve";
}
else {
$type = "shore";
}
$sql = "SELECT `CHROM`, `START`, `END` FROM {$db_name}.{$handle}";
$sql .= " WHERE `CHROM` = '".$chrom."'";
$sql .= " AND (((`START` BETWEEN $start AND $end) OR (`END` BETWEEN $start AND $end))";
$sql .= " OR (`START` <= $start AND `END` >= $end))";
$sql .= " ORDER BY `START` * 1 ASC";
$res = mysqli_query ($link,$sql);
$mergedRect = [];
$firstRow = mysqli_fetch_row($res);
$mergedRect = [$chrom,$firstRow[1],$firstRow[2],$type];
while($row = mysqli_fetch_row($res)){
if ($row[1] - $mergedRect[2] > 1/$scale){
array_push($resArray, $mergedRect);
$mergedRect[1] = $row[1];
$mergedRect[2] = $row[2];
$mergedRect[3] = $type;
}
else{
if ($mergedRect[2] < $row[2]){
$mergedRect[2] = $row[2];
$mergedRect[3] = $type;
}
}
}
$LastElement = end($resArray);
reset($resArray);
if ($mergedRect[1] != $LastElement[1]){
array_push($resArray, $mergedRect);
}
}
return $resArray;
}
function getTrackData($fileHandles,$name,$chrom,$start,$end){
$returnArray = [];
foreach ($fileHandles as $key => $value) {
# code...
if ($name[$key] == "HG19"){
$resArray = getHG19TrackData($fileHandles[$key],$chrom,$start,$end);
$returnArray["HG19"] = $resArray;
continue;
}
if ($name[$key] == "cpg"){
$resArray = getCpgTrackData($fileHandles[$key],$chrom,$start,$end);
$returnArray["cpg"] = $resArray;
continue;
}
$fileHandle = $value;
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$resArray = [];
$scale = 1000/($end - $start);
$sql = "SELECT `CHROM`, `CHROM_START`, `CHROM_END`, `GENESYMBOL`, `SCORE` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `CHROM` = '".$chrom."'";
$sql .= " AND (((`CHROM_START` BETWEEN $start AND $end) OR (`CHROM_END` BETWEEN $start AND $end))";
$sql .= " OR (`CHROM_START` <= $start AND `CHROM_END` >= $end))";
$sql .= " ORDER BY `CHROM_START` * 1 ASC";
$res = mysqli_query ($link,$sql);
$mergedRect = [];
$firstRow = mysqli_fetch_row($res);
$mergedRect = [$chrom,$firstRow[1],$firstRow[2],"",""];
$noMergedRect = false;
while($row = mysqli_fetch_row($res)){
$tempStartPix = ($row[1] - $start) * $scale;
$tempEndtPix = ($row[2] -$start)* $scale;
$mergedRectStartPix = ($mergedRect[1] - $start) * $scale;
$mergedRectEndtPix = ($mergedRect[2] - $start) * $scale;
if ($noMergedRect){
$mergedRect = [$chrom,$row[1],$row[2],"",$row[4]];
$noMergedRect = false;
continue;
}
//print_r($row);
if (is_numeric($row[4])){
array_push($resArray, $mergedRect);
array_push($resArray, [$chrom,$row[1],$row[2],"",$row[4]]);
$noMergedRect = true;
continue;
}
if ($tempStartPix - $mergedRectEndtPix > 1){
//array_push($result, $mergedRect);
array_push($resArray, $mergedRect);
$mergedRect[1] = $row[1];
$mergedRect[2] = $row[2];
}
else{
if ($mergedRect[2] < $row[2]){
$mergedRect[2] = $row[2];
}
}
}
$LastElement = end($resArray);
reset($resArray);
if ($mergedRect[1] != $LastElement[1]){
array_push($resArray, $mergedRect);
}
$returnArray[$name[$key]] = $resArray;
}
return $returnArray;
}
function getStartEndTrackData($fileHandles,$name,$chrom){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$returnArray = [];
$sql = "SELECT `CHROM`, `CHROM_START`, `CHROM_END`, `GENESYMBOL`, `SCORE` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `CHROM` = '".$chrom."'";
$sql .= " ORDER BY `CHROM_START` * 1 ASC";
foreach ($fileHandles as $key => $value) {
# code...
$resArray = [];
$fileHandle = $value;
$minsql = "SELECT `CHROM`, `CHROM_START`, `CHROM_END`, `GENESYMBOL`, `SCORE` FROM {$db_name}.{$fileHandle} ORDER BY `CHROM_START` * 1 ASC LIMIT 1";
if ($name[$key] == "HG19"){
$minsql = "SELECT `chrom`, `txStart`, `txEnd`, `name2`, `score` FROM {$db_name}.{$fileHandle} ORDER BY `txStart` * 1 ASC LIMIT 1";
}
if ($name[$key] == "cpg"){
$temp = explode(",", $fileHandle);
$shelveFile = $temp[1];
$minsql = "SELECT `CHROM`, `START`, `END` FROM {$db_name}.{$shelveFile} ORDER BY `START` * 1 ASC LIMIT 1";
}
$minres = mysqli_query ($link,$minsql);
while($row = mysqli_fetch_row($minres))
{
$minRect = [$chrom,$row[1],$row[2],"",""];
if ($name[$key] == "cpg"){
$minRect = [$chrom,$row[1],$row[2],"shelve"];
}
array_push($resArray, $minRect);
}
$maxsql = "SELECT `CHROM`, `CHROM_START`, `CHROM_END`, `GENESYMBOL`, `SCORE` FROM {$db_name}.{$fileHandle} ORDER BY `CHROM_END` * 1 DESC LIMIT 1";
if ($name[$key] == "HG19"){
$maxsql = "SELECT `chrom`, `txStart`, `txEnd`, `name2`, `score` FROM {$db_name}.{$fileHandle} ORDER BY `txEnd` * 1 DESC LIMIT 1";
}
if ($name[$key] == "cpg"){
$temp = explode(",", $fileHandle);
$shelveFile = $temp[1];
$maxsql = "SELECT `CHROM`, `START`, `END` FROM {$db_name}.{$shelveFile} ORDER BY `END` * 1 DESC LIMIT 1";
}
$maxres = mysqli_query ($link,$maxsql);
while($row = mysqli_fetch_row($maxres))
{
$maxRect = [$chrom,$row[1],$row[2],"",""];
if ($name[$key] == "cpg"){
$maxRect = [$chrom,$row[1],$row[2],"shelve"];
}
array_push($resArray, $maxRect);
}
array_push($returnArray, $resArray);
}
return $returnArray;
}
function loadTooltip($chrom,$trackName, $type, $min, $max, $fileHandle){
//return $chrom.$trackName.$type.$xCoordinate.$scale.$fileHandle;
if($trackName == "cpg"){
return loadCPGTooltip($chrom,$type, $min,$max,$fileHandle);
}
else if($trackName == 'HG19'){
return loadHG19Tooltip($chrom,$type, $min,$max,$fileHandle);
}
else{
return loadBEDTooltip($chrom,$type, $min,$max,$fileHandle);
}
}
function loadCPGTooltip($chrom,$type, $min,$max,$fileHandle){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$sql = "SELECT `START`, `END` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `CHROM` = '".$chrom."'";
$sql .= " AND (((`START` BETWEEN $min AND $max) OR (`END` BETWEEN $min AND $max))";
$sql .= " OR (`START` <= $min AND `END` >= $max))";
$sql .= " ORDER BY `START` * 1 ASC";
$res = mysqli_query ($link,$sql);
$returnArray = [];
$compString = "";
$count = 0;
while($row = mysqli_fetch_row($res)){
$start = (string)$row[0];
$end = (string)$row[1];
//$compString.(string)$row[0]."-".(string)$row[1].", ";
$count++;
if($count > 5){
$compString = substr($compString,0,-2);
$compString .= " ... zoom in to see more ";
break;
}
else{
$compString .= $start." - ".$end.", ";
}
}
$compString = substr($compString,0,-2);
return $type.": ".$compString;
}
function loadHG19Tooltip($chrom,$type, $min,$max,$fileHandle){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$sql = "SELECT `txStart`, `txEnd`, `name2`, `exonStarts`, `exonEnds` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `chrom` = '".$chrom."'";
$sql .= " AND (((`txStart` BETWEEN $min AND $max) OR (`txEnd` BETWEEN $min AND $max))";
$sql .= " OR (`txStart` <= $min AND `txEnd` >= $max))";
$sql .= " ORDER BY `txStart` * 1 ASC";
$res = mysqli_query ($link,$sql);
$returnArray = [];
$compString = "";
$count = 0;
while($row = mysqli_fetch_row($res)){
$count++;
if($type == "gene"){
$start = (string)$row[0];
$end = (string)$row[1];
$name = (string)$row[2];
if($count > 5){
$compString = substr($compString,0,-2);
$compString .= " ... zoom in to see more ";
break;
}
else{
//$compString.(string)$row[0]."-".(string)$row[1].", ";
$compString .= $name." (".$start." - ".$end."), ";
}
}
else{
if($count > 1){
$compString = substr($compString,0,-2);
$compString .= " ... zoom in to see more ";
break;
}
else{
$name = (string)$row[2];
$start = substr((string)$row[3],0,-1);
$end = substr((string)$row[4],0,-1);
$start_array = explode(",",$start);
$end_array = explode(",",$end);
$exons = "";
foreach ($start_array as $key => $value) {
$exons .= "(".$value." - ".$end_array[$key]."), ";
}
$compString .= $name." ".$exons;
}
}
}
$compString = substr($compString,0,-2);
return $type.": ".$compString;
}
function loadBEDTooltip($chrom,$type, $min,$max,$fileHandle){
$db_name = $GLOBALS['db_name'];
$link = $GLOBALS['DBlink'];
$sql = "SELECT `CHROM_START`, `CHROM_END`, `GENESYMBOL` FROM {$db_name}.{$fileHandle}";
$sql .= " WHERE `CHROM` = '".$chrom."'";
$sql .= " AND (((`CHROM_START` BETWEEN $min AND $max) OR (`CHROM_END` BETWEEN $min AND $max))";
$sql .= " OR (`CHROM_START` <= $min AND `CHROM_END` >= $max))";
$sql .= " ORDER BY `CHROM_START` * 1 ASC";
$res = mysqli_query ($link,$sql);
$returnArray = [];
$compString = "";
while($row = mysqli_fetch_row($res)){
$start = (string)$row[0];
$end = (string)$row[1];
$name = (string)$row[2];
//$compString.(string)$row[0]."-".(string)$row[1].", ";
$compString .= $name. " (".$start."-".$end."), ";
}
$compString = substr($compString,0,-2);
return $type.": ".$compString;
}
function ajaxReturn($result){
echo json_encode($result);
}
function insertToDb($timestamp,$header, $data){
$db_name = $GLOBALS['db_name'];
$table_name = $GLOBALS['table_name'];
$host_name = $GLOBALS['host_name'];
$user_name = $GLOBALS['user_name'];
$password = $GLOBALS['password'];
$link = mysqli_connect($host_name, $user_name, $password, $db_name);
$tableName = "upload".$timestamp;
$sql = "CREATE TABLE IF NOT EXISTS `".$tableName."` (
ID VARCHAR(10)
)";
mysqli_query ($link,$sql);
foreach($header as $colName){
$sql = "ALTER TABLE `".$tableName."` ADD $colName VARCHAR( 255 )";
mysqli_query ($link,$sql);
}
//$rows = explode("\n", $data);
array_shift($data);
$length = sizeof($data);
//sql for insert columes, this line includes column names, values will be added in the loop
$sql = "INSERT INTO ".$tableName." (ID";
$headerColumnCount = 0;
$comma = ", ";
foreach ($header as $title){
$headerColumnCount++;
$sql.= $comma . $title;
}
$sql.= ") VALUES";
$sqlCopy = $sql;
//echo $sql;
$rowNo = 1;
$countDown = 1000;
//sql for values to be inserted
// $sqlForValue = " ('". $counter ."',";
// foreach ($rowArray as $key=>$title){
// $sqlForValue.= "'". $rowArray[$key] ."',";
// }
// $sqlForValue = rtrim($sqlForValue, ",");
// $sqlForValue.= "),";
// echo $sqlForValue;
// foreach($rows as $row){
// $counter++;
// $rowArray = explode("\t", $row);
// if ($countDown !== 0){
// $sqlForValue = " ('". $counter ."',";
// foreach ($rowArray as $key=>$title){
// $sqlForValue.= "'". $rowArray[$key] ."',";
// }
// $sqlForValue = rtrim($sqlForValue, ",");
// $sqlForValue.= "),";
// $sql.= $sqlForValue;
// $countDown--;
// }
// else {
// $result = rtrim($sql, ",");
// mysqli_query ($link,$result);
// $countDown = 1000;
// $sql = $sqlCopy;
// $sqlForValue = " ('". $counter ."',";
// foreach ($rowArray as $key=>$title){
// $sqlForValue.= "'". $rowArray[$key] ."',";
// }
// $sqlForValue = rtrim($sqlForValue, ",");
// $sqlForValue.= "),";
// $sql.= $sqlForValue;
// }
// if ($counter == $length){
// $result = rtrim($sql, ",");
// mysqli_query ($link,$result);
// }
// }
foreach($data as $rowArray){
//echo $rowArray;
//$rowArray = explode("\t", $row);
$sqlForValue = " ('". $rowNo ."'";
$dataColumnCount = 0;
$comma = ", ";
foreach ($rowArray as $key=>$title){
$dataColumnCount++;
$sqlForValue.= $comma . "'". $rowArray[$key]. "'";
}
if($dataColumnCount != $headerColumnCount){
continue;
}
// $sqlForValue = rtrim($sqlForValue, ",");
$sqlForValue.= "),";
$sql.= $sqlForValue;
$rowNo++;
$countDown--;
if ($countDown == 0){
$result = rtrim($sql, ",");
mysqli_query ($link,$result);
// echo $result . "<br>";
$countDown = 1000;
$sql = $sqlCopy;
}
}
if ($sql !== $sqlCopy){
$result = rtrim($sql, ",");
// echo $result;
mysqli_query ($link,$result);
}
return $tableName;
//echo json_encode($tableName);
}
function loadTable(){
global $currentRow;
$db_name = $GLOBALS['db_name'];
$table_name = $GLOBALS['table_name'];
$host_name = $GLOBALS['host_name'];
$user_name = $GLOBALS['user_name'];
$password = $GLOBALS['password'];
//define variables from incoming values
if (isset($_GET["tableRef"])){
if ($_GET["tableRef"] !== ""){
$table_name = $_GET["tableRef"];
}
}
if(isset($_GET["posStart"]))
$posStart = $_GET['posStart'];
else
$posStart = 0;
if(isset($_GET["count"]))
$count = $_GET['count'];
else
$count = 100;
$currentRow = $posStart;
$totalCount = 0;
//connect to database
$link = mysqli_connect($host_name, $user_name, $password);
//$db = mysqli_select_db ("test");
//create query to products table
$sql = "SELECT * FROM {$db_name}.{$table_name}";
//if this is the first query - get total number of records in the query result
if($posStart==0){
$sqlCount = "Select count(*) as cnt from ($sql) as tbl";
$resCount = mysqli_query ($link,$sqlCount);
$rowCount=mysqli_fetch_assoc($resCount);
$totalCount = $rowCount["cnt"];
}
if(isset($_GET["name_mask"]) && isset($_GET["filter_attribute"])){
$p = $_GET['filter_attribute'];
$sql.=" Where {$p} = '".$_GET["name_mask"]."'";
$sqlCount = "Select count(*) as cnt from ($sql) as tbl";
$resCount = mysqli_query ($link,$sqlCount);
$rowCount=mysqli_fetch_assoc($resCount);
$totalCount = $rowCount["cnt"];
}
if (isset($_GET["order"]) && isset($_GET["sort_attribute"])){
$order = $_GET['order'];
$sort_attribute = $_GET['sort_attribute'];
$ifNumeric = "SELECT `".$sort_attribute."` FROM {$db_name}.{$table_name} LIMIT 1";
$res = mysqli_query ($link, $ifNumeric);
$resArray = mysqli_fetch_row($res);
if( is_numeric($resArray[0])){
$sql.=" ORDER BY $sort_attribute * 1 $order";
}
else {
$sql.=" ORDER BY $sort_attribute $order";
}
}
//add limits to query to get only rows necessary for the output
$sql.= " LIMIT ".$posStart.",".$count;
//query database to retrieve necessary block of data
$res = mysqli_query ($link,$sql);
//output data in XML format
print("<rows pos='".$posStart."' total_count='".$totalCount."'>");
while($row=mysqli_fetch_row($res)){
print("<row ID='".$row[0]."'>");
// print("<cell>");
// print($row[0]);
// print("</cell>");
// print("<cell>");
// print($row[1]);
// print("</cell>");
// print("<cell>");
// print($row[2]);
// print("</cell>");
// print("<cell>");
// print($row[3]);
// print("</cell>");
// print("<cell>");
// print($row[4]);
// print("</cell>");
// print("<cell>");
// print($row[5]);
// print("</cell>");
// print("<cell>");
// print($row[6]);
// print("</cell>");
foreach ($row as $key=>$title){
print("<cell>");
print($row[$key]);
print("</cell>");
}
print("</row>");
}
print("</rows>");
}
?>