tomailas

Right, can you just advise me how I can convert totalClicks into variable, I believe I am on the right way - I just need to know how to store totalClick into array below. Thanks // Function getSiteSettings public static function getWebStats() { $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $sql = "SELECT visitedPageTitle, visitedURL, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; $st = $conn->prepare( $sql ); $st->execute(); $list = array(); while ( $row = $st->fetch() ) { $stats = new cmsStats( $row ); $list[] = $stats; } // below line for testing purpse - I can see that it returns what I need. print_r($list); $conn = null; return ( array ( "results" => $list, ? ) ); } // Function ends

Only 1, in: $data = cmsStats::getWebStats();

Please ignore these: // Populates fields from array (from "www_globalconfig" table) $results['globalconfig'] = cmsSettings::getSiteSettingsByID( ADMIN_PANEL_SETTINGS ); $results['pageTitle'] = "view website stats | " . $results['globalconfig']->wwwTitle; $results['pageMetaTags'] = $results['globalconfig']->wwwMetaTags; $results['pageFaviconURL'] = $results['globalconfig']->wwwFaviconURL; $results['pageCSSURL'] = $results['globalconfig']->wwwCSSURL; $results['pageJSURL'] = $results['globalconfig']->wwwJSURL; $results['bannerTitle'] = $results['globalconfig']->wwwBanner; This looks like you are referencing objects. Is 'getSiteSettingsByID' returning an object to you? Your comment says you are getting fields from an array. Perhaps you want an element of the 'globalconfig' array? That would be $results['globalconfig']['wwwMetaTags']. These working as expected. "I looked at view stats. Don't understand you. First we're storing $data into $results. Then you start adding elements to $results with names. Why are we storing the stats as an array ($data) in $results if you are using $results for these other things? That must be why you were referencing $results['listwebstats']. That now makes sense. Of course you were storing the wrong thing in that element before ($data['results'])." I agree with you 100%, but I am not sure how to resolve this, would you be able to provide an example?

My apologies, but this still doesn't work (maybe I wasn't clear enough - my bad). Will try explain in more details what I am trying to achieve: I got: class [cmsStats] and function [getWebStats] file: This I believe should be reviewed (especially function getWebStats. <?php class cmsStats { // defining variables public $visitedOn = null; public $visitorIP = null; public $visitorBrowserType = null; public $visitedURL = null; public $visitedPageTitle = null; public $visitorPreviousURL = null; public $visitedPageLoadTime = null; public $clickCount = null; public function __construct( $data=array() ) { // web_stats if ( isset( $data['visitedOn'] ) ) $this->visitedOn = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['visitedOn'] ); if ( isset( $data['visitorIP'] ) ) $this->visitorIP = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['visitorIP'] ); if ( isset( $data['visitorBrowserType'] ) ) $this->visitorBrowserType = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['visitorBrowserType'] ); if ( isset( $data['visitedURL'] ) ) $this->visitedURL = $data['visitedURL']; if ( isset( $data['visitedPageTitle'] ) ) $this->visitedPageTitle = $data['visitedPageTitle']; if ( isset( $data['visitorPreviousURL'] ) ) $this->visitorPreviousURL = $data['visitorPreviousURL']; if ( isset( $data['visitedPageLoadTime'] ) ) $this->visitedPageLoadTime = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['visitedPageLoadTime'] ); if ( isset( $data['clickCount'] ) ) $this->clickCount = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['clickCount'] ); } public static function getWebStats() { $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; $st = $conn->prepare( $sql ); $st->execute(); $row = $st->fetchAll(PDO::FETCH_ASSOC); print_r($row); $conn = null; } } ?> My second file is kind of index page which works (in my scenario kind of "gateway" (sorry for my expression ): index file calls class file/ function: <?php function viewStats() { $results = array(); // getList $data = cmsStats::getWebStats(); //$results['ListWebStats'] = $data['results']; $results[] = $data; // below can be ignored as it is calling other classes / functions // Populates fields from array (from "www_globalconfig" table) $results['globalconfig'] = cmsSettings::getSiteSettingsByID( ADMIN_PANEL_SETTINGS ); $results['pageTitle'] = "view website stats | " . $results['globalconfig']->wwwTitle; $results['pageMetaTags'] = $results['globalconfig']->wwwMetaTags; $results['pageFaviconURL'] = $results['globalconfig']->wwwFaviconURL; $results['pageCSSURL'] = $results['globalconfig']->wwwCSSURL; $results['pageJSURL'] = $results['globalconfig']->wwwJSURL; $results['bannerTitle'] = $results['globalconfig']->wwwBanner; // Populates links (from "web_links" table) $data = cmsEngine::getLinksListActive( TOTAL_MENU_LINKS_TO_DISPLAY ); $results['ListLinksActive'] = $data['results']; require( TEMP_PATH . "/viewStats.php" ); } ?> and third file viewStats - I am trying to create a graph based on results: (I am using similar code to my previous parts - so I know it is partially working) ?php foreach ( $results['ListWebStats'] as $ListWebStats ) { ?> <ul class="chartlist"> <li> <a href="<?php echo $ListWebStats->visitedURL?>"><?php echo $ListWebStats->visitedPageTitle?></a> <span class="count"><?php $ListWebStats->clickCount ?></span> <span class="index" style="width: 42%">(42%)</span> </li> </ul> <?php } ?> Well, that is all. Basically I am trying to keep same layout, I may need very little lines of code to get this working. (not much examples online to be honest for such a scenario) Many thanks again! Much appreciated!

Thank you very much, I will try this now.

Thanks for reply and your time matey. No - results doesn't exist - you are correct. I have provided this as example. To be honest I am not sure how to move my array data into "results" What do I need to amend, add in code below? public static function getWebStats() { $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; $st = $conn->prepare( $sql ); $st->execute(); $row = $st->fetchAll(PDO::FETCH_ASSOC); $conn = null; Previously I was using kind of [copied one of my working functions] different approach, but currently cannot sort out "getWebStats" public static function getList( $numRows=1000000, $order="pageID ASC" ) { $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(pagePublicationDate) AS pagePublicationDate FROM web_pages ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows"; $st = $conn->prepare( $sql ); $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT ); $st->execute(); $list = array(); while ( $row = $st->fetch() ) { $article = new cmsEngine( $row ); $list[] = $article; } // Now get the total number of website pages that matched the criteria $sql = "SELECT FOUND_ROWS() AS totalRows"; $totalRows = $conn->query( $sql )->fetch(); $conn = null; return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) ); }

No column name is correct. Your example - kind of different approach, as it is overlapping now with other functions and class calls. I need somehow populate data: In way: function viewStats() { $results = array(); // getList $data = cmsStats::getWebStats(); $results['ListWebStats'] = $data['results']; and fetch it via: <?php foreach ( $results['ListWebStats'] as $ListWebStats ) { ?> <ul class="chartlist"> <li> <a href="<?php echo $ListWebStats->visitedURL?>"><?php echo $ListWebStats->visitedPageTitle?></a> <span class="count"><?php $ListWebStats->clickCount ?></span> <span class="index" style="width: 42%">(42%)</span> </li> </ul> basically I just would like to know how to store my PDO query into array so later I could re-use it in "function viewStats".

Interesting approach, just tested. It returns: Notice: Undefined index: visitedPageTitle in webStats\viewStats.php Which is line: $title = $row['visitedPageTitle'];

Hi, Thanks for reply. I will try to explain: I have a class file cmsStats, this file contains function "getWebStats": public static function getWebStats() { $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; $st = $conn->prepare( $sql ); $st->execute(); $row = $st->fetchAll(PDO::FETCH_ASSOC); $conn = null; } This works fine for me as it returns data array like this: Array ( [0] => Array ( [visitedPageTitle] => about me | sampledomain.net [totalClicks] => 2 ) [1] => Array ( [visitedPageTitle] => admin menu | sampledomain.net [totalClicks] => 44 ) [2] => Array ( [visitedPageTitle] => all links | sampledomain.net [totalClicks] => 3 ) [3] => Array ( [visitedPageTitle] => all pages | sampledomain.net [totalClicks] => 2 ) [4] => Array ( [visitedPageTitle] => articles | sampledomain.net [totalClicks] => 4 ) [5] => Array ( [visitedPageTitle] => Edga | sampledomain.net [totalClicks] => 3 ) [6] => Array ( [visitedPageTitle] => home | sampledomain.net [totalClicks] => 34 ) [7] => Array ( [visitedPageTitle] => online access | sampledomain.net [totalClicks] => 2 ) [8] => Array ( [visitedPageTitle] => services | sampledomain.net [totalClicks] => 1 ) [9] => Array ( [visitedPageTitle] => tools | sampledomain.net [totalClicks] => 1 ) [10] => Array ( [visitedPageTitle] => view website stats | sampledomain.net [totalClicks] => 302 ) ) But I am struggling (cos don't know how) to store this into kind a "placeholders" (not sure what is right term for this). I am trying to call function: function viewStats() { $results = array(); // getList $data = cmsStats::getWebStats(); // Calling cmsStats class::getWebStats function $results['ListWebStats'] = $data['results']; And my goal is to re-use this data like below: <?php foreach ( $results['ListWebStats'] as $ListWebStats ) { ?> <ul class="chartlist"> <li> <a href="<?php echo $ListWebStats->visitedURL?>"><?php echo $ListWebStats->visitedPageTitle?></a> <span class="count"><?php $ListWebStats->clickCount ?></span> <span class="index" style="width: 42%">(42%)</span> </li> </ul> I believe is only few small bits missing in this puzzle, I would appreciate if somebody could help me with that.

Can somebody please advise how to store into array and fetch data from array. I got (not sure if I am doing this correctly) <?php $conn = new PDO("mysql:dbname=DB;host=localhost", "root", ""); $query = $conn->query("SELECT Title, SUM(Count) AS Clicks FROM web GROUP BY Title"); $rows = $query->fetchAll(PDO::FETCH_ASSOC); $list = array(); echo '</ br>', print_r($rows), '</ br>'; $conn = null; ?> So I want to get my results: <?php foreach ( $results['Stats'] as $Stats ) { ?> <ul class="chartlist"> <li> <span class="title"><?php echo $Stats->Title?></span> <span class="count"><?php $Stats->Clicks ?></span> <span class="index" style="width: 42%">(42%)</span> </li> </ul>

Thanks, but there is nothing wrong with: $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM web_stats ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows"; As it is returning what I need perfectly, however I am not sure if I need this statment at all, maybe I should replace it somehow with - only: $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; As I said - PDO is great, but is no unclear to me. Without messing with PDO I can always do something like this: $query = "SELECT ip, SUM(count) FROM table GROUP BY ip"; $result = mysql_query($query) or die(mysql_error()); echo "<table border='1' width='210' bordercolor='#232323' cellpadding='1' cellspacing='2'> <tr> <th>IP address</th> <th>Total clicks</th> </tr>"; while($row = mysql_fetch_array($result)){ echo "<tr>"; echo "<td align='center'>". $row['ip']. "</td>"; echo "<td align='center'>". $row['SUM(count)']. "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); But Anybody could tell me how to convert this query to PDO (as per my original post)?

Thanks for response boompa, I have checked this: function viewStats() { $results = array(); // getList $data = cmsStats::getWebStats(); $results['ListWebStats'] = $data['results']; $results['totalRows'] = $data['totalRows']; $results['totalClicks'] = $data['totalClicks']; and it looks correct. I am 100% that problem is with my PDO parts, as data being returned, but is not calculating properly clickCounts.

Hi Actually I managed to create full CMS based on online examples and etc. however stuck with PDO as was more familiar with SQL queries previously (but obviously – PDO is correct way to go). By profession - I am IT engineer (not developer). Yes I have switched logging on my server. I would appreciate if somebody could let me know what is wrong with my class/function and how I could correct this - it seems that many googler's looking for similar to my questions.

Hi Guys, (sorry for childish question - I am not a PHP developer - more likely hobbits, but trying hard and did research already - unfortunately without luck as I cannot see online example) I am having problems with PDO while creating web stats page (to be honest it is very difficult for me to explain what I am trying to achieve but here we go..). I have created a class which contains function below: // Function: getWebStats [Getting All ] public static function getWebStats( $numRows=1000000, $order="visitedOn DESC" ) { $conn = new PDO( DB_DSN, DB_UNAME DB_PASS ); $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM web_stats ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows"; $st = $conn->prepare( $sql ); $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT ); $st->execute(); $list = array(); while ( $row = $st->fetch() ) { $record = new cmsStats( $row ); $list[] = $record; } // Now get the total number of stats that matched the criteria $sql = "SELECT FOUND_ROWS() AS totalRows"; $totalRows = $conn->query( $sql )->fetch(); // Now get the total number of clicks that matched the criteria $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle"; $totalClicks = $conn->query( $sql )->fetch(); // Close connection $conn = null; // Return data array return ( array ( "results" => $list, "totalRows" => $totalRows[0], "totalClicks" => $totalClicks[0] ) ); I am calling this class: function viewStats() { $results = array(); // getList $data = cmsStats::getWebStats(); $results['ListWebStats'] = $data['results']; $results['totalRows'] = $data['totalRows']; $results['totalClicks'] = $data['totalClicks']; And trying to insert data below: <?php foreach ( $results['ListWebStats'] as $ListWebStats ) { ?> <ul class="chartlist"> <li> <a href="<?php echo $ListWebStats->visitedURL?>"><?php echo $ListWebStats->visitedPageTitle?></a> <span class="count">echo $results['totalClicks']</span> // This is there I am struggling, as it doesn’t returns data correctly <span class="index" style="width: 42%">(42%)</span> </li> </ul> Problem: I know that my code isn't correct (my function needs to be reviewed). I would be thankful if you could point me to right direction or tell me what I am doing wrong. Many thanks!