Access PHP PDO SQL Query Result From Another Class File

Question

Am trying to access the result of SQL query in SQLite using PHP from another class. I have read similar questions here and here. But still have not found a solution to my problem. This is what I have done.

DBMan.php

<?php
Class DBMan
    {
        private $dsn = 'sqlite:leDB.db';
        private $db;

        public function __construct()
            {
                $this->db = new PDO($this->dsn);
                $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
        public function showData()
            {
                try{
                    $sql = 'SELECT * FROM fruits';
                    $stmt= $this->db->query($sql);   //UPDATED

                    if(!$stmt){
                        throw new PDOException('Error displaying fruits');
                    }

                    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);    //UPDATED
                    return $data;
                }
                catch (PDOException $e){
                    echo 'Error\n';
                    echo $e->getMessage();  //UPDATED    
                }
            }
    }
?>

MaView.php

<?php
class MaView
    {
        include_once("DBMan.php");   //UPDATED
        $db = new DBMan();

        $val = $db->showData();
        foreach ($val as $row) {
            echo "<H1>" . $row['fruit_name'] . "</H1>"; //UPDATED
        }                             
    }
?>

Can someone please show me where I have blundered?

You have not wrapped your script (after the __autolad() method) in the MaView class. — Rasclatt, Jan 03 '16 at 03:07
Thanks. I removed the autoload function. The table is still not showing. — Adwin, Jan 03 '16 at 09:59

score 1 · Answer 1 · answered Jan 03 '16 at 03:08

1

There are couple issue with your php code. I updated your code as follows DbMan.php

public function showData()
{
    try {
        $sql = 'SELECT * FROM fruits';
        $stmt= $this->db->query($sql); // updated

        if(!$stmt){
            throw new PDOException('Error displaying fruits');
        }
        $data = $stmt->fetchAll(); // updated
        return $data;
    }
    catch (PDOException $e){
        echo 'Error\n';
        echo $e->getMessage(); // updated
    }
}

MaView.php

include_once("DbMan.php");

$db = new DbMan();
$val = $db->showData();
foreach ($val as $row) {
    echo "<H1>" . $row['fruit_name'] . "</H1>";
}

answered Jan 03 '16 at 03:08

mhrilwan

96
5

Thank you. I have done the necessary changes. However, it is still not displaying. – Adwin Jan 03 '16 at 09:51
Your MaView.php class is wrong. Please update the content of that file with following code: `include_once("DbMan.php"); $db = new DbMan(); $val = $db->showData(); foreach ($val as $row) { echo "
" . $row['fruit_name'] . "
"; }` – mhrilwan Jan 03 '16 at 12:37

Rasclatt · Answer 2 · 2016-01-04T05:44:14.483

I think you need to separate these elements out and organize them into single files each doing their own task or group of similar tasks:

/classes/class.DatabaseConfig.php

<?php
// This is your database class, just deals with connection
// and automated array returns
class DatabaseConfig
    {
        private static $singleton;
        private static $con;
        private $query;

        public function __construct()
            {
                if(empty(self::$singleton))
                    self::$singleton    =   $this;

                return self::$singleton;
            }

        public  function connect($db = 'sqlite:leDB.db')
            {
                if(!empty(self::$con))
                    return self::$con;

                try {
                    self::$con  =   new PDO($db);
                    self::$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }
                catch (PDOException $e) {
                    die("Connection failed");
                }

                return self::$con;
            }

        public  function query($sql,$bind = false)
            {
                $con    =   $this->connect();

                if(!empty($bind)) {
                    $this->query    =   $con->prepare($sql);
                    $this->query->execute($bind);                   
                }
                else {
                    $this->query    =   $con->query($sql);
                }

                return $this;
            }

        public  function getResults()
            {
                if(empty($this->query))
                    return 0;

                while($row = $this->query->fetch(PDO::FETCH_ASSOC)) {
                    $result[]   =   $row;
                }

                return (!empty($result))? $result : 0;
            }
    }

/classes/class.DBMan.php

<?php
// This is just your DBMan Class, but it's not doing much
// except returning an array for this table
class DBMan
    {
        public static function showData()
            {
                return qEngine()->query('SELECT * FROM `fruits`')->getResults();
            }
    }

/classes/class.MaView.php

<?php
// This is just the view class. It outputs to buffer so it
// can be echoed to the page
class MaView
    {
        public  static  function displayFruits()
            {
                $val    =   DBMan::showData();
                if(!is_array($val))
                    return false;

                ob_start();
                foreach($val as $row)
                    echo "<H1>".$row['fruit_name']."</H1>";

                $data   =   ob_get_contents();
                ob_end_clean();

                return $data;
            }
    }

/functions/function.qEngine.php

<?php
// This simply returns the database connection
function qEngine()
    {
        return new DatabaseConfig();
    }

/autoloader.php

<?php
// This autoloads classes based on a specific directory structure
spl_autoload_register(function($class) {
    if(!class_exists($class)) {
        $incFile = ROOT_DIR.'/classes/class.'.$class.'.php';
        if(is_file($incFile))
            include_once($incFile);
        }
    });

/config.php

<?php
// Make some defines
define('ROOT_DIR',__DIR__);
// Include the class autoloader
include_once(ROOT_DIR.'/autoloader.php');
// Include the query function
include_once(ROOT_DIR.'/functions/function.qEngine.php');

/index.php

<?php
// Add the config to every page
require(__DIR__.'/config.php');
// You don't need to include anything for classes
// the spl_autoload_register() does it automatically
// provided your directories are set as indicated
echo MaView::displayFruits();

score 0 · Accepted Answer · answered Jan 03 '16 at 15:20

Well, I finally figured it out. Thanks for all your replies as well as your patience. Being a novice is not easy, so much to learn. I separated the connection settings in a separate class to modularize it further.

//Keys.php returns a database connection

<?php    
    class Keys
    {
        private $dsn = 'sqlite:leDB.db';
        private $db;

        public function __construct(){

            $this->db = new PDO($this->dsn);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }

        function getDB(){
            return $this->db;
        }
    }
?>

The class DBMan. Does all database manipulation. The mistake I made was to use fetchAll() to display all my results. FetchAll() returns an array of results however looping through each one displaying them is not straight forward. To remedy this, I used fetch(), then looped through all the results and stored them in an array. This is the code.

//Class DBMan handles database queries and returns results in an array

    <?php
    Class DBMan
        {

            private $con;
            public function __construct(Keys $key)
                {
                    $this->$con = $key;
                }
            public function showData()
                {
                    try{
                        $sql = 'SELECT * FROM fruits';
                        $stmt=$this->con->getDB()->query($sql);

                        if(!$stmt){
                            throw new PDOException('Error displaying fruits');
                        }


                        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                            $data [] = $row;
                        }

                        return $data;
                    }
                    catch (PDOException $e){
                        echo 'Error\n';
                        echo $e->getMessage();    
                    }
                }
        }
    ?>

I use the method fetchAssoc() to get an associative array and stored the result in array. This gives me a multidimensional array. To access my values the for loop had to loop through each row and pick out the key. This is what I have done.

//MaView.php displays information from the database

    <?php
    class MaView
        {
    include_once('Keys.php');
    include_once ('DBman.php');
    $key = new Keys();
    $db = new Datman($key);    

     $val = $db->showData();
            foreach ($val as $row => $key) {?>

                <h1><?php echo $key['fruit_name']?></h1>

            <?php } ?>

        }
    ?>

I have mixed the php code with html. It's a bit hard to read at first but it is correct. Once again thanks for all your help.

Access PHP PDO SQL Query Result From Another Class File

3 Answers3

" . $row['fruit_name'] . "