#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Storing PDO query results (data) into array and reusing it


    Hi Guys,

    I need your help with PDO, Arrays and variables.
    (I am not PHP guru, but trying hard and did research before)

    I have a class file cmsStats, this file contains function "getWebStats":

    ==== So my function looks like below (I believe I need fix it) ===>

    PHP Code:
      public static function getWebStats() {
        
    $conn = new PDODB_DSNDB_USERNAMEDB_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:

    PHP Code:
    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 Code:
    <?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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    This works fine for me as it returns data array like this:
    Sounds like you know exactly what to do. Set $this->yourPlaceholder equal to the query results.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by NotionCommotion
    Sounds like you know exactly what to do. Set $this->yourPlaceholder equal to the query results.
    Thanks for reply, not sure what do you mean.

    Would you be able to provide an example in this case? Many thanks

    Honestly - I stuck
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    your general setup doesn't make a lot of sense.

    First of all, opening a new database connection for every single method call is pretty insane as this comes with a huge overhead and massively bloats your code. Why would you do something like that?

    Most applications have exactly one connection for the whole request. Meaning they create one PDO object at the beginning and then use it for all queries.

    That's what you need to do, too. In my opinion, you should also scrap those static methods calling other static methods from out of the blue. Instead, use actual objects which receive their input through the constructor and their methods -- in other words, I'd write the code object-oriented.

    It's really simple: Create a PDO object on the top level (create some "database.php" or "init.php" script). Then create your statistics objects and pass the PDO instance to the constructor. The stats object then stores the connection in an attribute and can use it at any time.

    PHP Code:
    <?php

    // this would be in some shared script
    $databaseConnection = new PDO();

    $webStats = new getWebStats($databaseConnection);
    This is much less obscure, since you can actually tell where the connection comes from (it's not some hard-coded reference pointing to ... something). And this is also testable (yeah, I know, PHP people don't do unit tests).
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    Thanks for reply, not sure what do you mean.

    Would you be able to provide an example in this case? Many thanks
    Take a look into OOP. An object had methods and properties. The object also has one special object called "$this" which points to itself. So if you want to save something that can later be retrieved from any other method, you can save it in $this->someProperty.

    This same concept can be used with what Jacques1 explained (and PS. Definitely do not create separate connections). Instead of just setting $databaseConnection equal to new PDO(), you can set $this->databaseConnection equal to new PDO() (and do it and your very first parent class). Or you can make $databaseConnection global. Or you can make it a singleton object. Start simple like Jacques showed, and then when you eventually get to the point where you say "hey, I can no longer access $databaseConnection", then you will have a reason to do one of the other solutions I mentioned.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I think we disagree here, NotionCommotion.

    First of all, why should you store the query result in some attribute like you mentioned in #2? For caching?

    tomailas, what you wanna do is have the method return the result set. That's how functions and methods communicate with the outside world: They "calculate" something, and then they return the result:

    PHP Code:
    <?php  
      
    function add($x$y) {
        
    $sum $x $y;
        return 
    $sum;
    }

    $my_sum add(1020);
    echo 
    $my_sum;
    Secondly, all this singleton/global variable/parent class stuff just makes things unnecessarily complicated and creates ugly dependencies.

    I know that everybody now uses this Database::getConnection() pattern. But I don't think anybody has really thought this through (like so often in the PHP world). What this does is create a hard-coded reference to a particular class coming from, well, somewhere. The object no longer works on its own. You cannot simply instantiate it and use it. You first need this "Database" class or at least create a mock-up. This makes the object pretty obscure and hard to test -- not to mention the inheritent obscurity of the singleton pattern.

    And why should a database connection even be a singleton? Is PDO a singleton? Is mysqli a singleton? No. If you instantiate it once, you have one connection. If you instantiate it again, you have another connection. What's the problem with that? Sounds pretty logical to me. I don't see any reason for setting up some weird singleton to prevent this.

    I got the impression that all those fancy patterns just make things complicated and distract people from what OOP is actually about. They do what SOAP has done to HTTP: create an overly complex re-implementation of what the host system could already do with much simpler means.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Burb of Detroit, Michigan
    Posts
    91
    Rep Power
    78
    I've been following this thread and I wasn't going reply, but after seeing the latest couple of replies I'm a little perplexed (This probably isn't the right word to use, but it's the best I can come up with). I see nothing wrong in doing the following:

    PHP Code:
    $db Database::getInstance();
    $pdo $db->getConnection(); 
    Which I have at the top of my php pages in a utilities.inc.php page

    An have the following Database class in my class in my classes folder:

    PHP Code:
    ?php
     
    # PDO database: only one connection is allowed. 

    class Database {
     
        private 
    $_connection;
        
    // Store the single instance.
        
    private static $_instance;
     
        
    // Get an instance of the Database.
        // @return Database: 
        
    public static function getInstance() {
            if (!
    self::$_instance) {
                
    self::$_instance = new self();
            }
            return 
    self::$_instance;
        }
     
        
    // Constructor - Build the PDO Connection:
        
    public function __construct() {
            
    $db_options = array(
                
    PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements)
                
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent)
                
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays)
            
    );
            
    $this->_connection = new PDO('mysql:host=localhost;dbname=cms;charset=utf8''username''password'$db_options);
        }
     
        
    // Empty clone magic method to prevent duplication:
        
    private function __clone() {
     
        }
     
        
    // Get the PDO connection:    
        
    public function getConnection() {
            return 
    $this->_connection;
        }
     

    I see nothing wrong in implementing it this way and my own personal website is pretty responsive. Granted it might not be as fast as doing it the other way, but without doing a speed test I would just guess that it's not that much slower. I don't find it obscure, for I just put in in my classes folder like I already stated and my little class auto-loader code does the rest. I know it works and don't have to worry about connecting to my database when I write a new script and focus my time testing scripts for what I want it to do.
    Last edited by Strider64; June 28th, 2013 at 06:11 AM.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I'm talking mainly about excessive usage of static methods. You know, when every method is sprinkled with hardcoded class references to some UserModel and some StringUtils and some HTMLHelper and whatnot. This means that this particular object depends on all kinds of specific external classes. It doesn't work without them. you cannot just take the object and use it on its own. You need the complete infrastructure of all those classes.

    How do you do unit tests on this code? How do you reuse it? The answer is: You (almost) cannot. Your class only works inside a very specific context.

    If that doesn't apply to you, there's no problem. By the way, I didn't say anything about performance.

    My issue with the singleton pattern is that I simply find it unnecessary in this case. What do you gain from it? What's its advantage over simply instantiating the database class only once? I mean, sometimes you do have to prevent a class from being instantiated more once once, because multiple instances would break the whole concept. For example, you obviously can't have more than one session object. But I don't why you'd wanna prevent multiple database connections under any circumstances.

    I also don't really see the purpose of the whole class. It doesn't add any abstraction or functionality. All it does is create and provide a PDO object -- how is that better than a plain new PDO(...)?

    If you actually used the Database class to do queries, it would make perfect sense. But currently, it's more like a packing: You take out the content and then throw it away.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    Originally Posted by Jacques1
    I think we disagree here, NotionCommotion.

    First of all, why should you store the query result in some attribute like you mentioned in #2? For caching?
    Primarily, because that is the topic of this post.

    Secondarily, I do something similar, and if you feel I should do differently, I would like your opinion. Below is a design pattern I often use. I am very self taught, and maybe it isn't a good pattern, but it seems to work for me. In my display() method, I call getData() which queries the DB and stores the results in a $model property. Then the $model object is passed to the view and the data is presented. Is there anything wrong in doing so?

    PHP Code:
    class specific_controller extends generic_controller
    {
        public 
    $somedata;

        public function 
    display()
        {
            
    $model=$this->getModel();
            if(
    $model->someData=$model->getData(library::getVar('id','int')))
            {
                
    $model->someMoreData=$model->getMore();
                
    $view=new view($this->getViewFile());
                
    $view->addModel($model);
                
    $html=$view->render(FALSE);
                
    $template=new template($this->componentData->id);
                
    $template->addComponent($html);
                
    $template->render(TRUE);            
            }
            else
            {
                
    header('Location: index.php?cid='.$GLOBALS['vs_main']->componentData->id);die;
            }
        }

        public function 
    saveRecord()
        {
            
    header('Content-Type: application/json');
            
    $validate=new my_validate($this->getValidate());
            
    $data=$validate->sanitize($_POST);
            
    $errors=$validate->validate($data);
            if(empty(
    $errors))
            {
                
    $model=$this->getModel();
                if(!
    $id=($data['id'])?$model->updateRecord($data):$model->addRecord($data))
                    {
    $errors[]='Unexpected error saving data.';}
            }
            else {
    $id=0;}
            echo 
    json_encode(array ('errors'=>$errors,'id'=>$id));
        }

  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by NotionCommotion
    Primarily, because that is the topic of this post.
    No. If you look at his code, all he wants is simply have the method return the result set:

    PHP Code:
    $data cmsStats::getWebStats(); // Calling cmsStats class::getWebStats function 
      
    $results['ListWebStats'] = $data['results']; 
    He didn't say anything about pushing the whole object to the "view" and fetching the data from its attributes.

    This is classical, plain PHP, not some sophisticated MVC architecture.



    Originally Posted by NotionCommotion
    Is there anything wrong in doing so?
    No.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo