Hi

i am trying to display the username with their daily activity with count. i am getting the output for the last row reset is showing 0 in all the name. please help me to resolve.

connection.php

<?php
    class Database{
        //specify your own databse credentials

        private $host = "odbc:DRIVER={SQL Server};SERVER=servername;DATABASE=DB";
        private $username = "uname";
        private $password = "password";
        public $conn;

        //get the databse connection
        public function getConnection() {

            $this->conn = null;

            try{
                $this->conn = new PDO($this->host,$this->username,$this->password);
            }catch(PDOException $exception){
                echo "Connection error: " . $exception->getMessage();
            }
            return $this->conn;
        }



    }



?>

file.php

<?php

class Users {

        private $conn;
        private $db_name= "DB NAME";

        public function __construct($db)
            {
                $this->conn = $db;
            }

        public function STWCGY($tablen,$fieldname,$order,$by){
            $sql = "select $fieldname FROM $this->db_name.$tablen order by $order $by";
            $query = $this->conn->prepare($sql);
            try {
                    $query->execute();

                    return $query;


            }catch(PDOException $e) {
                    die($e->getMessage());
                }
        }

        /*user Activity*/

        public function uactivity($table,$fieldname){

            $sql = "select $fieldname from $this->db_name.$table";

            $query = $this->conn->prepare($sql);

            try{
                $query->execute();

                $rows = $query->fetchAll(); 

                $rowCount = count($rows);

                if($rowCount > 0){

                    return $rowCount;
                }else{
                    return 0;
                }

            }catch(PDOException $e) {
                die($e->getMessage());
            }
        }
}
?>

<table id="dt_tableTools" class="uk-table" cellspacing="0" width="100%">

                        <thead>
                            <tr>
                                <th>S.No</th>
                                <th>Username</th>
                                <th>Total Stream Created</th>
                                <th>Active Stream</th>
                                <th>InActive Stream</th>
                                <th>Stream Active - Not Scheduled</th>
                            </tr>
                        </thead>

                        <tbody>
                            <?php 
                                $user_act = $users->STWCGY('FQ64001','DISTINCT(SHUSER)','SHUSER','DESC');
                                $i=1;
                                while($row_stream = $user_act->fetch(PDO::FETCH_ASSOC)) 
                                {
                                    extract($row_stream);

                                           $tuser =  trim($SHUSER);                     

                            ?>
                            <tr>
                                <td><?php echo $i; ?></td>
                                <td><a href="user-profile.php?uprofile=<?php echo $SHUSER; ?>"><?php echo $SHUSER=trim($SHUSER); ?></a></td>
                                <td><?php echo $TSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'",'*');?></td>
                                <td><?php echo $ATSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'".' AND SHYQ64YN='."'Y'",'*');?></td>
                                <td><?php echo $INTSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'".' AND SHYQ64YN='."'N'",'*');?></td>
                                <td><?php // echo $scheduled_stream = $users->sinlineCount('FQ64001','COUNT(SHYQ64SID)','SHYQ64SID','Distinct SSYQ64SID','FQ64004','SHUSER',$SHUSER,'SHYQ64YN','Y') ?></td>
                            </tr>
                            <?php $i++; } ?>
                        </tbody>

                    </table>

Am guessing it's this:

$sql = "select $fieldname FROM {$this->db_name}.$tablen order by $order $by";

$sql = "select $fieldname from {$this->db_name}.$table";

You probably don't get an exception unless you explicitly turn it on in getConnection:

$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Thanks pritaeas. Updated one

file.php

<?php
include('connection.php');
class Users {

        private $conn;
        private $db_name= "DB NAME";

        public function __construct($db)
            {
                $this->conn = $db;
            }

        public function STWCGY($tablen,$fieldname,$order,$by){
            $sql = "select $fieldname FROM $this->db_name.$tablen order by $order $by";
            $query = $this->conn->prepare($sql);
            try {
                    $query->execute();

                    return $query;


            }catch(PDOException $e) {
                    die($e->getMessage());
                }
        }

        /*user Activity*/

        public function uactivity($table,$fieldname){

            $sql = "select $fieldname from $this->db_name.$table";

            $query = $this->conn->prepare($sql);

            try{
                $query->execute();

                $rows = $query->fetchAll(); 

                $rowCount = count($rows);

                if($rowCount > 0){

                    return $rowCount;
                }else{
                    return 0;
                }

            }catch(PDOException $e) {
                die($e->getMessage());
            }
        }
}
?>

<table id="dt_tableTools" class="uk-table" cellspacing="0" width="100%">

                        <thead>
                            <tr>
                                <th>S.No</th>
                                <th>Username</th>
                                <th>Total Stream Created</th>
                                <th>Active Stream</th>
                                <th>InActive Stream</th>
                                <th>Stream Active - Not Scheduled</th>
                            </tr>
                        </thead>

                        <tbody>
                            <?php 
                                $user_act = $users->STWCGY('FQ64001','DISTINCT(SHUSER)','SHUSER','DESC');
                                $i=1;
                                while($row_stream = $user_act->fetch(PDO::FETCH_ASSOC)) 
                                {
                                    extract($row_stream);

                                           $tuser =  trim($SHUSER);                     

                            ?>
                            <tr>
                                <td><?php echo $i; ?></td>
                                <td><a href="user-profile.php?uprofile=<?php echo $SHUSER; ?>"><?php echo $SHUSER=trim($SHUSER); ?></a></td>
                                <td><?php echo $TSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'",'*');?></td>
                                <td><?php echo $ATSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'".' AND SHYQ64YN='."'Y'",'*');?></td>
                                <td><?php echo $INTSC = $users->uactivity('FQ64001 where SHUSER='."'$tuser'".' AND SHYQ64YN='."'N'",'*');?></td>
                                <td><?php // echo $scheduled_stream = $users->sinlineCount('FQ64001','COUNT(SHYQ64SID)','SHYQ64SID','Distinct SSYQ64SID','FQ64004','SHUSER',$SHUSER,'SHYQ64YN','Y') ?></td>
                            </tr>
                            <?php $i++; } ?>
                        </tbody>

                    </table>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.