如何从Codeigniter中的2个独立表中循环和计数数据？

thank you for your help on my previous question about Codeigniter

感谢您对我之前关于Codeigniter的问题的帮助

UPDATE : Since everyone got a little confused by my question, I have edited my question.

更新:由于每个人都对我的问题感到困惑,我已经编辑了我的问题。

I'm working in a banking company, and my project is creating a real-time monitoring for my company's clients transactions.

我在一家银行公司工作,我的项目正在为我公司的客户交易创建一个实时监控。

I need help in creating a HTML table that shows the total transaction of each of the Client both SUCCESS and FAILURE it. The HTML table should look like below:

我需要帮助创建一个HTML表,显示每个客户端的总事务,包括SUCCESS和FAILURE。 HTML表格应如下所示:

Here's my database structure (I can't change the structure, coz I don't have the root access)

这是我的数据库结构(我无法更改结构,因为我没有root访问权限)

Database A (Contained bank name and their ID code)

数据库A(包含银行名称及其ID代码)

Table 1 = BankName, ATMNode, CardNode, BankGroup, CDC

表1 = BankName,ATMNode,CardNode,BankGroup,CDC

Database B (Contained all the transaction data like the date, the encryption and the encrypted card number)

数据库B(包含所有交易数据,如日期,加密和加密的卡号)

Table 2 = Postid, Transid, SettleID, ATMNode, Rsp_Code, CDC

表2 = Postid,Transid,SettleID,ATMNode,Rsp_Code,CDC

Now, I have successfully looped all of the bank name in my model. All I need to do is just COUNT the SUCCESS and FAILURE row from each of the bank name and then loop the count result.

现在,我已经成功地在我的模型中循环了所有银行名称。我需要做的只是从每个银行名称中COUNT SUCCESS和FAILURE行,然后循环计数结果。

At first I created 1 function for 1 bank name, but then I got a tip from someone that's not how it works. He said that I need to create a parameter or something with the looped bank name, and then I can loop the COUNT data for each of the bank name.

起初我为1个银行名称创建了1个函数,但后来我收到了某人的提示,而不是它的工作方式。他说我需要创建一个带有循环银行名称的参数或东西,然后我可以循环每个银行名称的COUNT数据。

So here's my model:

所以这是我的模型:

<?php class Dash_model extends CI_Model {

        public function __construct()
        {
                // Cal CI Constructor
                parent::__construct();
                // Load Database A.
                $CI = &get_instance();
                //setting the second parameter to TRUE (Boolean) the function will return the database object.
                $this->db2 = $CI->load->database('db2', TRUE);
        }

        // Get all the bank name
        public function getbanklist()
        {
            $query = $this->db2->query("SELECT BankName FROM Table1 ORDER BY BankName");
            return ($query->result());

        }

        function transapproved() // Counting Success
        {
            // Database B autoloaded in autoload.php
            $this->db->select('TransID');
            $this->db->from('Table2');
            $this->db->join('Table1', 'Table1.CDC = Table2.CDC', 'inner');
            $this->db->where('BankName', $getbanklist);
            $this->db->where('Rsp_code', '01');
            $data = $this->db->get();
            return $this->data->num_rows();

        }

        function transfailure() // Counting Failure
        {
            // Database B autoloaded in autoload.php
            $this->db->select('TransID');
            $this->db->from('Table2');
            $this->db->join('Table1', 'Table1.CDC = Table2.CDC', 'inner');
            $this->db->where('BankName', $getbanklist);
            $this->db->where('Rsp_code', '02');
            data = $this->db->get();
            return $this->data->num_rows();
        }

}

?>

Of course my model is not working. It didn't return anything. Even if it work, it only return one row. So I need your help guys.

当然我的模型不起作用。它没有返回任何东西。即使它工作,它只返回一行。所以我需要你的帮助。

And then here's my controller:

然后这是我的控制器:

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Dash_control extends CI_Controller {

    public function __construct()
       {
            parent::__construct();
            $this->load->model('dash_model');
            $this->load->library('table');
       }

    public function index()
    {
        $tmpl = array (
        'row_start'           => '<tr>',
        'row_end'             => '</tr>',
        'cell_start'          => '<td>',
        'cell_end'            => '</td>',
        );

        $this->table->set_template($tmpl); 

        $data['banknamehere'] = $this->dash_model->getbanklist();

        $this->load->view('dashboard',$data);
    }


}

And then I passed it to my VIEW:

然后我把它传递给了我的视图:

<table class="table table-bordered table-hover">
<thead>
<tr>
<th>Bank Name</th>
<th>Trans Success</th>
<th>Trans Failure</th>
</tr>
</thead>
<tbody>
<?php foreach ($banknamehere as $row)
{ ?>
<tr><td><?php echo $row->banknamehere; ?></td></tr>
<?php } ?>
</tbody>
</table>

Yes, everything didn't work. I think the key must be in the model. But I don't know how. Thank you for your help...

是的,一切都行不通。我认为关键必须在模型中。但我不知道怎么做。谢谢您的帮助...

2 个解决方案

#1

Change the Table structure as per the previous answer

按照上一个答案更改表结构

Table 1 = CustomerId, CustomerName, CustomerCity, CustomerCountry, CustomerGroup

Table 2 = TransactionID, CustomerCity, TransactionStatus, DateTime,CustomerId

Table 3 = TransactionID, CustomerCountry, CustomerGroup, EncryptKey,CustomerId

Then in your Model

然后在你的模型中

the functions will be as follows

功能如下

public function transactionsuccess()
{
    $query = $this->db->query("SELECT Table1.CustomerName as Name,Count(CustomerName) as SuccessfullTransaction  FROM Table2 join Table1 ON Table1.CustomerId=Table2.CustomerId WHERE Table2.Status='Success' GROUP BY Table1.CustomerName ORDER BY Table1.CustomerName");

    return $query->result(); 
}
public function transactionfailure()
{
    $query = $this->db->query("SELECT Table1.CustomerName as Name,Count(CustomerName) as FailureTransaction  FROM Table2 join Table1 ON Table1.CustomerId=Table2.CustomerId WHERE Table2.Status='Failure' GROUP BY Table1.CustomerName ORDER BY Table1.CustomerName");

    return $query->result(); 
}

Then in your Controller

然后在你的控制器中

public function index()
{
    $tmpl = array (
    'row_start'           => '<tr>',
    'row_end'             => '</tr>',
    'cell_start'          => '<td>',
    'cell_end'            => '</td>',
    );

    $this->table->set_template($tmpl); 


    $data['success'] = $this->dash_model->transactionsuccess();
    $data['failure'] = $this->dash_model->transactionfailure();

    $this->load->view('dashboard',$data);
}

Then in your View file

然后在您的View文件中

<tbody>
 <tr>
  <td>Customer Name</td>
  <td>Successfull Transaction</td>
  <td>Failed Transaction</td>
 </tr>
  <?php foreach (array_combine($success, $failure) as $success_row =>  $failure_row)
   { ?>
  <tr>
    <td><?php echo $success_row->Name; ?></td>
    <td><?php echo $success_row->SuccessfullTransaction; ?></td>
    <td><?php echo failure_row->FailureTransaction; ?></td>
  </tr>
   <?php } ?>
</tbody>

updated Answer: ..............................................

更新答案:..............................................

In Model

     <?php
    class Dash_model extends CI_Model 
    {
            public function __construct()
            {

                    parent::__construct();
                    $CI = &get_instance();
                    $this->db2 = $CI->load->database('db2', TRUE);
            }


            function transapproved() // Counting Success
            {
        $query = $this->db->query("SELECT Table1.BankName as Name,Count(BankName) as   SuccessfullTransaction  FROM Table2 join Table1 ON Table1.CDC = Table2.CDC WHERE Table2.Rsp_code='01' GROUP BY Table1.BankName ORDER BY Table1.BankName");

        return $query->result(); 

            }

            function transfailure() // Counting Failure
            {
        $query = $this->db->query("SELECT Table1.BankName as Name,Count(BankName) as FailureTransaction  FROM Table2 join Table1 ON Table1.CDC = Table2.CDC WHERE Table2.Rsp_code='02' GROUP BY Table1.BankName ORDER BY Table1.BankName");

        return $query->result(); 
            }

    }
?>

In Controller

public function index()
{
    $tmpl = array (
    'row_start'           => '<tr>',
    'row_end'             => '</tr>',
    'cell_start'          => '<td>',
    'cell_end'            => '</td>',
    );

    $this->table->set_template($tmpl); 


    $data['success'] = $this->dash_model->transapproved();
    $data['failure'] = $this->dash_model->transfailure();

    $this->load->view('dashboard',$data);
}

In view

<tbody>
 <tr>
  <td>Bank Name</td>
  <td>Successfull Transaction</td>
  <td>Failed Transaction</td>
 </tr>
  <?php foreach (array_combine($success, $failure) as $success_row =>  $failure_row)
   { ?>
  <tr>
    <td><?php echo $success_row->Name; ?></td>
    <td><?php echo $success_row->SuccessfullTransaction; ?></td>
    <td><?php echo $failure_row->FailureTransaction; ?></td>
  </tr>
   <?php } ?>
</tbody>

#2

In your model

在你的模型中

   public function customerlist()
            {
                $query = $this->db->query("SELECT CustomerName,count(CustomerName) FROM Table1 ORDER BY CustomerName");

                return $query->result(); 

            }

count(CustomerName) wil help you to find count of names

count(CustomerName)将帮助您查找名称的数量

Chnage the table structure

Chnage表结构

Table 1 = customer_id, CustomerName, CustomerCity, CustomerCountry, CustomerGroup

Table 2 = TransactionID, CustomerCity, TransactionStatus, DateTime,customer_id

Table 3 = TransactionID, CustomerCountry, CustomerGroup, EncryptKey,customer_id