+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Help with Codeigniter v4 pagination Library (/showthread.php?tid=78884)
Help with Codeigniter v4 pagination Library - venance - 03-22-2021
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',
]);
?>RE: Help with Codeigniter v4 pagination Library - craig - 03-22-2021
Why not use the one that comes with CI4? https://codeigniter.com/user_guide/libraries/pagination.html
RE: Help with Codeigniter v4 pagination Library - php_rocs - 03-22-2021
@venance,
...or use something like DataTables (https://datatables.net/)
RE: Help with Codeigniter v4 pagination Library - venance - 03-22-2021
(03-22-2021, 08:59 AM)php_rocs Wrote: @venance,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.
...or use something like DataTables (https://datatables.net/)
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/libraries/pagination.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.
RE: Help with Codeigniter v4 pagination Library - InsiteFX - 03-22-2021
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.
RE: Help with Codeigniter v4 pagination Library - venance - 03-23-2021
(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?
RE: Help with Codeigniter v4 pagination Library - venance - 03-23-2021
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!