PDO storing into arrays and re-using data

[tomailas]

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>

[ginerjm]

Which section of code are you talking about? They don't seem to be related, so not quite sure what you are asking for help on.

 

Regarding your initial question about storing into an array, you are doing that with the fetchall statement, but I don't see how that carries into the next section. Also your echo of $rows is going to look pretty funny. You should do this:

 

foreach ($rows as $k=>$v)

echo "$k is $v<br>";

 

Also - what is the line: $line = array() doing for you?

 

As for the second example - don't know what that is doing, since it is incomplete, but the output looks pretty funky here as well. Looks like you are going to create an un-ordered list element with one item in it over and over again. Is that what you want to do?

[tomailas]

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.

 

[ginerjm]

I may be wrong here but I think you want this:

 

$data = cmsStats::getWebStats(); // Calling cmsStats class::getWebStats function  

echo "<ul class='chartlist'>";

foreach ( $data as $row)

{

    $url = $row['visitedURL'];

    $title = $row['visitedPageTitle'];

    $cnt = $row['clickCount'];

    echo "<li><a href='$url'>$title</a><span class='count'>$cnt</span><span class='index' style='width: 42%'>(42%)</span></li>";

}

echo "</ul>";

 

You already have an array of arrays returned in $data from your call to getwebstats.  Simply loop thru $data, getting one row and echo the values from that row into a list element.  Note I removed the ul tags to outside of the loop, otherwise you would be creating a whole bunch of unordered lists still.

Edited June 27, 2013 by ginerjm

[tomailas]

Interesting approach, just tested.

 

It returns:

 

Notice: Undefined index: visitedPageTitle in webStats\viewStats.php

 

Which is line:

    $title = $row['visitedPageTitle'];

[ginerjm]

That means you have the wrong column name from the query that produced it.  Could be an upper/lower case issue or just a misspelling.

[tomailas]

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".

[ginerjm]

I don't see why you are doing this:

 

$data = cmsStats::getWebStats();  

$results['ListWebStats'] = $data['results'];

 

You're taking the webstats data which is a 2-dimensional array and placing one piece of it in one element of an array.  In fact there is nothing named $data['results'] that I can see, so I don't know what you saving.  You should be getting an error on that line.

 

Maybe you have other code that is changing what you have shown me so far.  If that is so, then I can't be of much help here.  But from what I have seen here is what getwebstats returns to you in $data:

($data[0])     array with 2 elements named 'visitedPageTitle' and 'totalClicks'

($data[1])     array with 2 elements named 'visitedPageTitle' and totalClicks'

etc

etc.

etc.

 

Then you are trying to copy something called "$data['results']" which I don't see here.  You actually showed us a dump of the viewwebstats output - I didn't see anything named 'results' in there.

 

My code was handling the data as I have been shown it.  If there is something that has massaged it, obviously my code wouldn't work.

[ginerjm]

I see the problem with the index.  I'm guessing that you pointed me to the wrong error line and that the error is the line above

 

$row['visitedURL']

 

which is a value stored in $row['visitedPageTitle'];

 

From your intial dump of the array of results from getwebstats:

Array ( [visitedPageTitle] => about me | sampledomain.net [totalClicks] => 2 )

 

I see that the [visitedPageTitle] element has two things separated by |.  So instead of doing:  

 

$url = $row['visitedURL'];

$title = $row['visitedPageTitle'];

 

change it to this:

 

list ($title,$url) = explode("|",$row['visitedPageTitle'];

   

[ginerjm]

oops - left out a paren in above.  S/B:

 

list($title,$url) = explode("|",$row['visitedPageTitle']);

[tomailas]

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] ) );
  }

[ginerjm]

What I provided you will work to display the results from one call to getwebstats.

 

If you want to do multiple calls to getwebstats and save each of their results do:

 

 

$data = cmsStats::getWebStats();  

$results[] = $data;

 

This will give you an array ($results) containing each of the arrays that are returned by each call to getwebstats.

 

To process:

 

simply add a foreach to the one I already gave you:

 

foreach ($results as $data)

{

  echo "<ul class='chartlist'>";

  foreach ( $data as $row)

  {

    list($title,$url) = explode("|",$row['visitedPageTitle']);

    $cnt = $row['clickCount'];

    echo "<li><a href='$url'>$title</a><span class='count'>$cnt</span><span class='index' style='width: 42%'>(42%)</span></li>";

  }

  echo "</ul>";

}

 

Not sure where you want the <ul> tags.  As it is above you'll get a list for each call to getwebstats.

Edited June 27, 2013 by ginerjm

[tomailas]

Thank you very much, I will try this now.

[ginerjm]

Good luck.  Be sure to post the new code if you are still having a problem.

[tomailas]

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!

Edited June 27, 2013 by tomailas

[ginerjm]

Ok - I looked at the function getwebstats.

 

You are not returning anything from it. End of story.

[ginerjm]

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']).

[ginerjm]

I also don't see what these are:

 

// 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'].

Edited June 27, 2013 by ginerjm

[tomailas]

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?

[ginerjm]

How many times do you call getwebstats before calling viewstats? That's the big question.

[ginerjm]

ok - gotta go.

 

If you are calling getwebstats ONCE before calling viewstats then my previous example code would work with only the one foreach loop. Just process $data as I originally did since you don't need to put the array of getwebstats into another array just to view it. Period.

 

As for the stuff you said to ignore - how about temporarily creating a viewstats function that just outputs the $data returned from getwebstats and see if you're getting any closer.

 

You are doing some very serious array manipulation here and I'm not sure you understand arrays that well yet. Your references to $data['results'] bother me since they have never existed, yet you keep using that reference.

[tomailas]

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

[ginerjm]

So - did you try what I suggested in my last post? That should work if you do exactly what I showed you. IF not post the 'new' viewstats function so I can see it.

 

And - did you change getwebstats to return something?

[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