Woonalmildrom
New Member
I am working on a user interface, "dashboard" of sorts which has some div boxes on it, which contain information relevant to the current logged in user. Their calendar, a todo list, and some statistics dynamically pulled from a google spreadsheet.I found here:http://code.google.com/apis/spreadsheets/data/3.0/reference.html#CellFeedthat specific cells can be requested from the sheet with a url like this:
\[code\]spreadsheets.google.com/feeds/cells/0AnhvV5acDaAvdDRvVmk1bi02WmJBeUtBak5xMmFTNEE/1/public/basic/R3C2\[/code\]I briefly looked into Zend GData, but it seemed way more complex that what I was trying to do. So instead I wrote two php functions: (in hours.php)
1.) does a \[code\]file_get_contents()\[/code\] of the generated url, based on the parameters row, column, and sheet
2.) uses the first in a loop to find which column number is associated with the given name.So basically I do an ajax request using jQuery that looks like this: // begin js function \[code\]function ajaxStats(fullname){ $.ajax({ url: "lib/dashboard.stats.php?name="+fullname, cache: false, success: function(html){ document.getElementById("stats").innerHTML = html; } });}\[/code\]// end js function // begin file hours.php\[code\]<?php function getCol($name){ $r=1; $c=2; while(getCell($r,$c,1) != $name) { $c++; } return $c;}function getCell($r, $c, $sheet){ $baseurl = "http://spreadsheets.google.com/feeds/cells/"; $spreadsheet = "0AnhvV5acDaAvdDRvVmk1bi02WmJBeUtBak5xMmFTNEE/"; $sheetID = $sheet . "/"; $vis = "public/"; $proj = "basic/"; $cell = "R".$r."C".$c; $url = $baseurl . $spreadsheet . $sheetID . $vis . $proj . $cell . ""; $xml = file_get_contents($url); //Sometimes the data is not xml formatted, //so lets try to remove the url $urlLen = strlen($url); $xmlWOurl = substr($xml, $urlLen); //then find the Z (in the datestamp, assuming its always there) $posZ = strrpos($xmlWOurl, "Z"); //then substr from z2end $data = http://stackoverflow.com/questions/3556961/substr($xmlWOurl, $posZ + 1); //if the result has more than ten characters then something went wrong //And most likely it is xml formatted if(strlen($data) > 10) { //Asuming we have xml $datapos = strrpos($xml,"<content type='text'>"); $datapos += 21; $datawj = substr($xml, $datapos); $endcont = strpos($datawj,"</content>"); return substr($datawj, 0,$endcont); } else return $data;} ?>\[/code\]//End hours.php //Begin dashboard.stats.php\[code\]<?phpsession_start();// This file is requested using ajax from the main dashboard because it takes so long to load,// as to not slow down the usage of the rest of the page.if (!empty($_GET['name'])){ include "hours.php"; // GetCollumn of which C#R1 = users name $col = getCol($_GET['name']); // then get cell from each of the sheets for that user, // assuming they are in the same column of each sheet $s1 = getcell(3, $col, 1); $s2 = getcell(3, $col, 2); $s3 = getcell(3, $col, 3); $s4 = getcell(3, $col, 4); // Store my loot in the session varibles, // so next time I want this, I don't need to fetch it $_SESSION['fhrs'] = $s1; $_SESSION['fdol'] = $s2; $_SESSION['chrs'] = $s3; $_SESSION['bhrs'] = $s4;}//print_r($_SESSION);?><!-- and finally output the information formated for the widget--><strong>You have:</strong><br/><ul style="padding-left: 10px;"> <li> <strong><?php echo $_SESSION['fhrs']; ?></strong> fundraising hours<br/></li> <li>earned $<strong><?php echo $_SESSION['fdol']; ?></strong> fundraising<br/></li> <li> <strong><?php echo $_SESSION['chrs']; ?></strong> community service hours<br/></li> <li> <strong><?php echo $_SESSION['bhrs']; ?></strong> build hours <br/></li></ul>\[/code\]//end dashboard.stats.phpI think that where I am loosing my 4 secs is the while loop in \[code\]getCol()\[/code\] [hours.php]
How can I improve this, and reduce my loading time? Should I just scrap this, and go to Zend GData?
If it is that while loop, should i try to store each users column number from the spreadsheet in the user database that also authenticates login?
\[code\]spreadsheets.google.com/feeds/cells/0AnhvV5acDaAvdDRvVmk1bi02WmJBeUtBak5xMmFTNEE/1/public/basic/R3C2\[/code\]I briefly looked into Zend GData, but it seemed way more complex that what I was trying to do. So instead I wrote two php functions: (in hours.php)
1.) does a \[code\]file_get_contents()\[/code\] of the generated url, based on the parameters row, column, and sheet
2.) uses the first in a loop to find which column number is associated with the given name.So basically I do an ajax request using jQuery that looks like this: // begin js function \[code\]function ajaxStats(fullname){ $.ajax({ url: "lib/dashboard.stats.php?name="+fullname, cache: false, success: function(html){ document.getElementById("stats").innerHTML = html; } });}\[/code\]// end js function // begin file hours.php\[code\]<?php function getCol($name){ $r=1; $c=2; while(getCell($r,$c,1) != $name) { $c++; } return $c;}function getCell($r, $c, $sheet){ $baseurl = "http://spreadsheets.google.com/feeds/cells/"; $spreadsheet = "0AnhvV5acDaAvdDRvVmk1bi02WmJBeUtBak5xMmFTNEE/"; $sheetID = $sheet . "/"; $vis = "public/"; $proj = "basic/"; $cell = "R".$r."C".$c; $url = $baseurl . $spreadsheet . $sheetID . $vis . $proj . $cell . ""; $xml = file_get_contents($url); //Sometimes the data is not xml formatted, //so lets try to remove the url $urlLen = strlen($url); $xmlWOurl = substr($xml, $urlLen); //then find the Z (in the datestamp, assuming its always there) $posZ = strrpos($xmlWOurl, "Z"); //then substr from z2end $data = http://stackoverflow.com/questions/3556961/substr($xmlWOurl, $posZ + 1); //if the result has more than ten characters then something went wrong //And most likely it is xml formatted if(strlen($data) > 10) { //Asuming we have xml $datapos = strrpos($xml,"<content type='text'>"); $datapos += 21; $datawj = substr($xml, $datapos); $endcont = strpos($datawj,"</content>"); return substr($datawj, 0,$endcont); } else return $data;} ?>\[/code\]//End hours.php //Begin dashboard.stats.php\[code\]<?phpsession_start();// This file is requested using ajax from the main dashboard because it takes so long to load,// as to not slow down the usage of the rest of the page.if (!empty($_GET['name'])){ include "hours.php"; // GetCollumn of which C#R1 = users name $col = getCol($_GET['name']); // then get cell from each of the sheets for that user, // assuming they are in the same column of each sheet $s1 = getcell(3, $col, 1); $s2 = getcell(3, $col, 2); $s3 = getcell(3, $col, 3); $s4 = getcell(3, $col, 4); // Store my loot in the session varibles, // so next time I want this, I don't need to fetch it $_SESSION['fhrs'] = $s1; $_SESSION['fdol'] = $s2; $_SESSION['chrs'] = $s3; $_SESSION['bhrs'] = $s4;}//print_r($_SESSION);?><!-- and finally output the information formated for the widget--><strong>You have:</strong><br/><ul style="padding-left: 10px;"> <li> <strong><?php echo $_SESSION['fhrs']; ?></strong> fundraising hours<br/></li> <li>earned $<strong><?php echo $_SESSION['fdol']; ?></strong> fundraising<br/></li> <li> <strong><?php echo $_SESSION['chrs']; ?></strong> community service hours<br/></li> <li> <strong><?php echo $_SESSION['bhrs']; ?></strong> build hours <br/></li></ul>\[/code\]//end dashboard.stats.phpI think that where I am loosing my 4 secs is the while loop in \[code\]getCol()\[/code\] [hours.php]
How can I improve this, and reduce my loading time? Should I just scrap this, and go to Zend GData?
If it is that while loop, should i try to store each users column number from the spreadsheet in the user database that also authenticates login?