php - 带有 WHERE 子句过滤的 codeigniter 模型函数结果数组

Question

我的模型中已经有一个工作功能

//GETTING TOTAL PURCHASE
function total_portal_user_sale($user_id)
{
    $return = 0;
    $transection  = $this->db->get('transection')->result_array();
    foreach ($transection as $row) {
        if ($row['buyer'] == $user_id) {
            $return += $row['credit'];
        }
    }
    return $return;
}

我正确地从我的数据库中获取数据，但我需要通过 where 子句过滤结果，例如这个查询

$this->db->query("SELECT sum(credit) as total FROM transection 
where buyer = $user_id and status like '%paid%'");

这是相同的场景，但使用数据过滤，我不知道如何将我的查询与活动记录语句一起使用

如果有人重写我的

函数 total_portal_user_sale($user_id) 与我的 WHERE 子句查询我会很感激...

这就是我将数据呈现到视图文件中的表中的方式

<div class="panel-body" id="demo_s">
<table id="demo-table" class="table table-striped" data-pagination="true" data-show-refresh="false" data-ignorecol="0,6" data-show-toggle="false" data-show-columns="false" data-search="true" data-striped="true" data-filter-control="true" data-show-export="true" >
    <thead>
        <tr>
            <th style="width:4ex"><input type="checkbox" id="users_idtog"/></th>
            <th><?php echo translate('no');?></th>
            <th><?php echo translate('image');?></th>
            <th><?php echo translate('name');?></th>
            <th><?php echo translate('email');?></th>
            <th><?php echo translate('phone');?></th>
            <th>Total<br>Sale</th>
            <th>This<br>Month<br>Sale</th>
            <th>Total Debit</th>
            <th>Total<br>Profit</th>
            <th><?php echo translate('creation');?></th>
            <th>Monthly<br>Sale<br>Target</th>
            <th>Coupon<br>Code</th>
            <th>Coupon<br>Expiry<br>Date</th>
            <th class="text-right"><?php echo translate('options');?></th>
        </tr>
    </thead>                
    <tbody >
    <?php
        $i = 0;
        foreach($all_users as $row){
            $i++;
    ?>                
    <tr>
                <td><input type="checkbox" class="users_id" name="users_id[]" value="<?php echo $row['user_id']; ?>" /></td>

        <td><?php echo $i; ?></td>
        <td>
            <img class="img-sm img-circle img-border"
                <?php if(file_exists('uploads/user_image/user_'.$row['user_id'].'.jpg')){ ?>
                    src="<?php echo base_url(); ?>uploads/user_image/user_<?php echo $row['user_id']; ?>.jpg"
                <?php } else if($row['fb_id'] !== ''){ ?>
                    src="https://graph.facebook.com/<?php echo $row['fb_id']; ?>/picture?type=large" data-im='fb'
                <?php } else if($row['g_id'] !== ''){ ?>
                    src="<?php echo $row['g_photo']; ?>"
                <?php } else { ?>
                    src="<?php echo base_url(); ?>uploads/user_image/default.png"
                <?php } ?>  />
        </td>
        <td><?php echo $row['username']; ?></td>
                                <td><a href="mailto:<?php echo $row['email']; ?>" target="_self" ><?php echo $row['email']; ?></td>
        <td><?php echo $row['phone']; ?></td>

       <td class="text-right"><?php echo $this->crud_model->total_purchase($row['user_id']); ?></td>
        <td class="text-right">
        <?php 
                $Days = explode("-",date("d-m-Y"));
                echo round($this->crud_model->sale_target($Days[0],$row['user_id']),2);
        ?>          
        </td>
        <td class="text-right"><?php echo $this->crud_model->total_profit($row['user_id']); ?></td>
        <?php 
        $ts = $this->crud_model->total_portal_user_sale($row['user_id']);
        $tc = $this->crud_model->total_portal_user_profit($row['user_id']);
        $tp = $ts - $tc
        ?>
        <td class="text-right">
            <?php echo $ts; ?>
        </td>

        <td class="text-right"><?php echo date('d M,Y',$row['creation_date']);?></td>
        <td><?php echo $row['monthly_sale_target']; ?></td>
        <td><?php echo $row['coupon_code']; ?></td>
        <td><?php echo $row['expiry_date']; ?></td>
        <td class="text-right">

            <a class="btn btn-purple btn-xs btn-labeled fa fa-tag" data-toggle="tooltip"
                onclick="ajax_modal('add_discount','<?php echo translate('give_target_discount'); ?>','<?php echo translate('adding_discount!'); ?>','add_discount','<?php echo $row['user_id']; ?>')" data-original-title="Edit" data-container="body">
                    <?php echo translate('give_discount');?>
            </a>

            <a class="btn btn-success btn-xs btn-labeled fa fa-wrench" data-toggle="tooltip" 

                    onclick="ajax_modal('edit','<?php echo translate('edit_user'); ?>','<?php echo translate('successfully_edited!'); ?>','user_edit','<?php echo $row['user_id']; ?>')" 

                        data-original-title="Edit" data-container="body">

                            <?php echo translate('edit');?>

                </a>

            <a onclick="delete_confirm('<?php echo $row['user_id']; ?>','<?php echo translate('really_want_to_delete_this?'); ?>')" class="btn btn-xs btn-danger btn-labeled fa fa-trash" data-toggle="tooltip" 
                data-original-title="Delete" data-container="body">
                    <?php echo translate('delete');?>
            </a>
        </td>
    </tr>
    <?php
        }
    ?>
    </tbody>
</table>
</div>

标签： phpcodeigniteractiverecordmysqli

Answer 1

希望对你有帮助 ：

function total_portal_user_sale($user_id) 
{
   $return = 0;
   $this->db->select('*');
   //$this->db->select('sum(credit) as total,buyer');
   $this->db->from('transection');
   $this->db->where('buyer',$user_id);
   $this->db->like('status','paid');

   $query = $this->db->get();
   if ($query->num_rows() > 0 ) 
   {
      foreach ($query->result_array() as $row) 
      {
        if ($row['buyer'] == $user_id) 
        {
          $return += $row['credit'];
        }
      }
     return $return;
   }
}

您也可以使用$this->db->select_max('credit')相同的

更多信息：https ://www.codeigniter.com/user_guide/database/query_builder.html