I want to verify whether the database query is executing correctly and returning the expected data.
My SpecializationModel.php
<?php
defined('BASEPATH') or exit('No direct script access allowed');
class SpecializationModel extends Hirer_Model
{
public function __construct()
{
parent::__construct();
}
public function getTop10Specializations($dateFrom, $dateTo)
{
$subQuery = "(
SELECT
applicant_id,
jobspec_id,
@rn := CASE WHEN @var_applicant_id = applicant_id THEN @rn + 1 ELSE 1 END AS rn,
@var_applicant_id := applicant_id
FROM (
SELECT @var_applicant_id := NULL, @rn := NULL
) vars,
applicant_jobpref
ORDER BY applicant_id, jobspec_id DESC
)";
$this->db
->select('spec.name, COUNT(sub_table.jobspec_id) AS total')
->from("$subQuery AS sub_table", false)
->join('applicant_personal personal', 'personal.id = sub_table.applicant_id', 'inner')
->join('job_specialization spec', 'sub_table.jobspec_id = spec.id', 'inner')
->where('sub_table.rn <=', 2)
->where('personal.registration_date >=', $dateFrom)
->where('personal.registration_date <=', $dateTo)
->group_by(['sub_table.jobspec_id', 'spec.name'])
->order_by('total', 'DESC')
->limit(10);
$query = $this->db->get();
log_message('debug', 'SQL Query: ' . $this->db->last_query());
print_r($query->result_array());
return $query->result_array();
}
}
I tried to use Specialization.php controller
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Specialization extends Hirer_Controller
{
public function __construct()
{
parent::__construct();
$this->lang->load(
dir_hirer($this->currentClass),
$this->currentLanguage
);
$this->load->model(model_hirer('Specialization'), 'specialization');
$this->addCustomCss(url_assets_css($this->currentClass));
}
public function index()
{
$dateFrom = '2019-07-01';
$dateTo = '2023-06-30';
$specializations = $this->specialization->getTop10Specializations($dateFrom, $dateTo);
$xValuesJobs = [];
$yValuesJobs = [];
foreach ($specializations as $specialization) {
$xValuesJobs[] = $specialization['name'];
$yValuesJobs[] = $specialization['total'];
}
// JSON encode the data *before* sending it to the view
$data = [
'xValuesJobs' => json_encode($xValuesJobs),
'yValuesJobs' => json_encode($yValuesJobs),
];
// Load page components
$this->loadHeader(); // Load the header
if (!empty($xValuesJobs) && !empty($yValuesJobs)) {
$this->loadContent('graph', $data); // Load graph view with data
} else {
// Optional: Load a fallback or message when there's no data
$this->loadContent('no_data_message', ['message' => 'No data available for the selected period.']);
}
$this->loadFooter(); // Load the footer
}
}
im trying to display the result of the query in this view
<script src=".js/2.9.4/Chart.js"></script>
<div class="col-md-12 card p-2 my-3">
<canvas id="myChart2" style="width:100%; height:350px;"></canvas>
</div>
<script>
document.addEventListener("DOMContentLoaded", function () {
const yLabels = ["Top 1", "Top 2", "Top 3", "Top 4", "Top 5", "Top 6", "Top 7", "Top 8", "Top 9", "Top 10"];
const xValuesJobs = [];
const yValuesJobs = [];
const barColorsJobs = xValuesJobs.map(() => "#FF8A00");
new Chart(document.getElementById("myChart2"), {
type: "bar",
data: {
labels: xValuesJobs,
datasets: [{
backgroundColor: barColorsJobs,
data: yValuesJobs
}]
},
options: {
legend: { display: false },
title: {
display: true,
text: "Top 10 Preferred Specializations"
},
scales: {
yAxes: [{
ticks: {
beginAtZero: true,
callback: function (value) {
return yLabels[10 - value] || "";
},
stepSize: 1,
max: 10
}
}]
}
}
});
});
</script>
there is no error, it just that my view or graph became white blank, only background is shown, the yLabels will not be rendered, I am expecting it is because of the query but how can i check if it really works?
The query is working in a raw sql, but in this case i transferred it in a code igniter query builder, I havent also created any routes for this, because this view is like a card type inside of our current dashboard, this is just a new modal with a new data needed to be displayed, do i still need to configure something in the route of the dashboard not just focus only in this MVC? this view is a modal and can be seen in hirer/home, i did not added any new route because its already rendering in the hirer/home.
I used print_r($query->result_array()) and log_message() for debugging, but where can i see this? i know only echo then it will be displayed in a view, in this case i still dont have view or controller, - I'm not seeing any output in the browser" or "The log file isn't showing the query.
The query is working in a raw sql, but in this case i transferred it in a code igniter query builder, I havent also created any routes for this, because this view is like a card type inside of our current dashboard, this is just a new modal with a new data needed to be displayed, do i still need to configure something in the route of the dashboard not just focus only in this MVC? this view is a modal and can be seen in hirer/home, i did not added any new route because its already rendering in the hirer/home.
I want to verify whether the database query is executing correctly and returning the expected data.
My SpecializationModel.php
<?php
defined('BASEPATH') or exit('No direct script access allowed');
class SpecializationModel extends Hirer_Model
{
public function __construct()
{
parent::__construct();
}
public function getTop10Specializations($dateFrom, $dateTo)
{
$subQuery = "(
SELECT
applicant_id,
jobspec_id,
@rn := CASE WHEN @var_applicant_id = applicant_id THEN @rn + 1 ELSE 1 END AS rn,
@var_applicant_id := applicant_id
FROM (
SELECT @var_applicant_id := NULL, @rn := NULL
) vars,
applicant_jobpref
ORDER BY applicant_id, jobspec_id DESC
)";
$this->db
->select('spec.name, COUNT(sub_table.jobspec_id) AS total')
->from("$subQuery AS sub_table", false)
->join('applicant_personal personal', 'personal.id = sub_table.applicant_id', 'inner')
->join('job_specialization spec', 'sub_table.jobspec_id = spec.id', 'inner')
->where('sub_table.rn <=', 2)
->where('personal.registration_date >=', $dateFrom)
->where('personal.registration_date <=', $dateTo)
->group_by(['sub_table.jobspec_id', 'spec.name'])
->order_by('total', 'DESC')
->limit(10);
$query = $this->db->get();
log_message('debug', 'SQL Query: ' . $this->db->last_query());
print_r($query->result_array());
return $query->result_array();
}
}
I tried to use Specialization.php controller
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Specialization extends Hirer_Controller
{
public function __construct()
{
parent::__construct();
$this->lang->load(
dir_hirer($this->currentClass),
$this->currentLanguage
);
$this->load->model(model_hirer('Specialization'), 'specialization');
$this->addCustomCss(url_assets_css($this->currentClass));
}
public function index()
{
$dateFrom = '2019-07-01';
$dateTo = '2023-06-30';
$specializations = $this->specialization->getTop10Specializations($dateFrom, $dateTo);
$xValuesJobs = [];
$yValuesJobs = [];
foreach ($specializations as $specialization) {
$xValuesJobs[] = $specialization['name'];
$yValuesJobs[] = $specialization['total'];
}
// JSON encode the data *before* sending it to the view
$data = [
'xValuesJobs' => json_encode($xValuesJobs),
'yValuesJobs' => json_encode($yValuesJobs),
];
// Load page components
$this->loadHeader(); // Load the header
if (!empty($xValuesJobs) && !empty($yValuesJobs)) {
$this->loadContent('graph', $data); // Load graph view with data
} else {
// Optional: Load a fallback or message when there's no data
$this->loadContent('no_data_message', ['message' => 'No data available for the selected period.']);
}
$this->loadFooter(); // Load the footer
}
}
im trying to display the result of the query in this view
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>
<div class="col-md-12 card p-2 my-3">
<canvas id="myChart2" style="width:100%; height:350px;"></canvas>
</div>
<script>
document.addEventListener("DOMContentLoaded", function () {
const yLabels = ["Top 1", "Top 2", "Top 3", "Top 4", "Top 5", "Top 6", "Top 7", "Top 8", "Top 9", "Top 10"];
const xValuesJobs = [];
const yValuesJobs = [];
const barColorsJobs = xValuesJobs.map(() => "#FF8A00");
new Chart(document.getElementById("myChart2"), {
type: "bar",
data: {
labels: xValuesJobs,
datasets: [{
backgroundColor: barColorsJobs,
data: yValuesJobs
}]
},
options: {
legend: { display: false },
title: {
display: true,
text: "Top 10 Preferred Specializations"
},
scales: {
yAxes: [{
ticks: {
beginAtZero: true,
callback: function (value) {
return yLabels[10 - value] || "";
},
stepSize: 1,
max: 10
}
}]
}
}
});
});
</script>
there is no error, it just that my view or graph became white blank, only background is shown, the yLabels will not be rendered, I am expecting it is because of the query but how can i check if it really works?
The query is working in a raw sql, but in this case i transferred it in a code igniter query builder, I havent also created any routes for this, because this view is like a card type inside of our current dashboard, this is just a new modal with a new data needed to be displayed, do i still need to configure something in the route of the dashboard not just focus only in this MVC? this view is a modal and can be seen in hirer/home, i did not added any new route because its already rendering in the hirer/home.
I used print_r($query->result_array()) and log_message() for debugging, but where can i see this? i know only echo then it will be displayed in a view, in this case i still dont have view or controller, - I'm not seeing any output in the browser" or "The log file isn't showing the query.
The query is working in a raw sql, but in this case i transferred it in a code igniter query builder, I havent also created any routes for this, because this view is like a card type inside of our current dashboard, this is just a new modal with a new data needed to be displayed, do i still need to configure something in the route of the dashboard not just focus only in this MVC? this view is a modal and can be seen in hirer/home, i did not added any new route because its already rendering in the hirer/home.
in your Specialization.php Controller add this line:
echo '<pre>';print_r($data);echo '</pre>';die;
right above this line:
// Load page components
Now, reload controller and you will see all of the data... Look on the page for your arrays: $xValuesJobs and $xValuesJobs
print_rshould show the data, but since you're including views after it, you might have to view the source of the page to see it. If you add adie();directly after theprint_ryou should see it in the browser without having to view source. As for the graph not displaying any data; you don't seem to be using the data you're passing to the view inside the view. Tryconst xValuesJobs = <?php echo $xValuesJobs; ?>;instead ofconst xValuesJobs = [];(and the same for the yValuesJobs) in the view. – Marleen Commented Jan 15 at 9:36