-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
Hello, I found pagination library for Codeigniter v4 on github with this link https://github.com/KejawenLab/CI4Pager . I followed every step, But I get issue on displaying data, I get all the data even I provide a number of limit to be shown per page. But for pagination links it work correct. I'm now using codeigniter 4.0.4. Please help, how can I limit number of result.
Here is my Code
//controller
PHP Code: public function index() { helper('paginator'); $db = \Config\Database::connect(); $result = $db->query('SELECT * FROM users'); $paginator = \App\Libraries\Paginator::createFromResult($result, 1, 1); return view('page', ['paginator'=> $paginator]); }
//view
PHP Code: <?php foreach($paginator->getResults() as $row):?> <div><?= $row['username'] ?></div> <?php endforeach;?>
<?= ci_pager($paginator, [ 'base_url' => '/vyombo', 'current_text' => 'Current Page', 'total_text' => 'Total Records', ]); ?>
-
craig
CI Veteran
-
Posts: 60
Threads: 0
Joined: Nov 2020
Reputation:
2
-
php_rocs
Administrator
-
Posts: 1,415
Threads: 103
Joined: Jun 2016
Reputation:
73
-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
03-22-2021, 12:12 PM
(This post was last modified: 03-22-2021, 12:20 PM by venance.)
(03-22-2021, 08:59 AM)php_rocs Wrote: @venance,
...or use something like DataTables (https://datatables.net/) I know the datatable is great, But I really don't like. I like codeigniter 4 pager, but is not working with the query builder.
I wan't to use that external library. I hope it works fine even if I don't get it right to work. I will appreciate if will get help with it. Thanks!
(03-22-2021, 08:44 AM)craig Wrote: Why not use the one that comes with CI4? https://codeigniter.com/user_guide/libra...ation.html
Yes. And I always used a pager library. But I want to perform my own sql with query() method. Thus why I found that library will be helpeful to me.
But if there's a way I can user $db->query("SELECT subQuery... FROM table"); with paginate() please help.
-
InsiteFX
Super Moderator
-
Posts: 6,575
Threads: 331
Joined: Oct 2014
Reputation:
240
Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this
Then it's chainable.
What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
03-23-2021, 12:49 AM
(This post was last modified: 03-23-2021, 01:25 AM by venance.)
(03-22-2021, 08:47 PM)InsiteFX Wrote: Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this
Then it's chainable.
Do you mean I can do this way:
$this->db->query("SELECT * FROM table");
return $this;
(03-23-2021, 12:49 AM)venance Wrote: (03-22-2021, 08:47 PM)InsiteFX Wrote: Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this
Then it's chainable.
Do you mean I can do this way:
$this->db->query("SELECT * FROM table");
return $this;
I tried to perform my query like this to
//my Model
PHP Code: public function getData(){ $this->table = 'province_income'; $sql = " SELECT Date, SUM(Income) as Income, SUM(Expense) as Expense, SUM(Income) - SUM(Expense) as Profit FROM ( SELECT province_income.date as Date, province_income.amount as Income, 0 as Expense FROM province_income UNION ALL SELECT province_expenses.date as Date, 0 as Income, province_expenses.amount as Expense FROM province_expenses ) as t GROUP BY Date ";
$this->db->query($sql); return $this; }
// my controller
PHP Code: public function index(){ $data['results'] =$this->model->getData()->paginate(1) $data['pager'] = $this->model->pager->links(); return view('Test/index', $data); }
//View
PHP Code: <?php foreach ($results as $result): ?> <div> <p> <?= $result['Date'] ?></p> <p> <?= $result['Income'] ?></p> <p> <?= $result['Expense'] ?></p> <p> <?= $result['Profit'] ?></p> </div> <?php endforeach;?>
But I get error:
PHP Code: Undefined index: Date Undefined index: Income Undefined index:Expense Undefined index: Profit
How can I make it work?
-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
Thank you all for the support and help, I carefully followed your device and I get a solution to my issue. I'd like to hear if this solution has no down effect to the app performance.
Here is what I have done to model class;
PHP Code: class IncomesModel extends Model { protected $table = "incomes";
public function __construct(){ parent::__construct(); }
public function getData() { //reset table to be used as default $this->table = 'view_province_incomes';
//creating view table to your database to hold the data $sql = "CREATE OR REPLACE VIEW view_province_incomes AS "; $sql .= " SELECT Date, SUM(Income) as Income, SUM(Expense) as Expense, SUM(Income) - SUM(Expense) as Profit FROM ( SELECT province_income.date as Date, province_income.amount as Income, 0 as Expense FROM province_income LEFT JOIN province ON province.provId = province_income.provId WHERE province_income.provId = 5 UNION ALL SELECT province_expenses.date as Date, 0 as Income, province_expenses.amount as Expense FROM province_expenses LEFT JOIN province ON province.provId = province_expenses.provId WHERE province_expenses.provId = 5 ) as t GROUP BY Date "; $this->db->query($sql); return $this; //to make easy chaining with the instance } }
//Controller
PHP Code: public function index() { $data['results'] = $this->model->getData()->asObject()->paginate(4); $data['pager'] = $this->model->pager->links(); return view('Income/index', $data); }
//View
PHP Code: <?php foreach ($results as $result): ?> <div style="display: flex; justify-content: space-between;"> <p> <?= $result->Date ?></p> <p> <?= $result->Income ?></p> <p> <?= $result->Expense ?></p> <p> <?= $result->Profit ?></p> </div> <?php endforeach;?>
<!-- Pagination --> <?= $pager ?>
My Output
PHP Code: +------------+-----------------------------+ | Date | Incomes | Expense | Profit | +------------+----------+---------+--------+ | 2018-06-12 | 10000 | 5000 | 5000 | +------------+----------+---------+--------+ | 2018-06-14 | 17000 | 9000 | 8000 | +------------+----------+---------+--------+ | 2018-06-15 | 8000 | 6000 | 2000 | +------------------------------------------+
Any suggestion, advice, alternatives please.
Thank you very much!
|