I have a very little knowledge about SQL.
I would like to show the numbers of package sold from my SQL database for specific table value for different ID (package ID) on CodeIgniter.
When I try to put some coding, it shows the same numbers of package sold for all packages.
Table: payments
Here is my code:
$this->db->where('payment_status', 'succeeded');
$this->db->where('item_price', '50000.00');
$succeededpayment = $this->db->get('payments');
I want to show only payment_status is succeeded, item_price = 50000 from table (payments) for each different ID (package) but it shows the SAME numbers of package sold for all packages.
The code to show the numbers of package sold:
<?php echo $succeededpayment->num_rows(); ?>
And here is the result;
result of the numbers of item sold
The image above displays 22 package sold (at the top right corner).
Fact: Package 1 has 21 sales and Package 2 has 1 sale.
Quiz controller:
<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Quiz_Controller extends Public_Controller {
/**
* Constructor
*/
function __construct()
{
parent::__construct();
$this->load->library('form_validation');
$this->load->model('HomeModel');
$this->load->model('Payment_model');
$this->load->model('PackageModel');
$this->load->model('TestModel');
$this->add_css_theme('quiz_box.css');
$this->add_css_theme('set2.css');
}
public function set_leader_bord_user_name()
{
$response['status'] = 'error';
$response['msg'] = 'Invalid Response';
if($this->input->post('inputValue'))
{
$response['status'] = 'success';
$response['name'] = $this->input->post('inputValue');
$response['msg'] = 'Thanks ! '.$this->input->post('inputValue');
$this->session->set_userdata('leader_bord_user_name', $this->input->post('inputValue'));
}
echo json_encode($response);
}
function instruction($quiz_id)
{
$quiz_data = $this->TestModel->get_quiz_by_id($quiz_id);
$test_taken = $this->TestModel->get_test_taken($quiz_id,$this->user['id']);
if($quiz_data->is_registered == 1 && empty($this->user['id']))
{
$this->session->set_flashdata('error', 'Login atau Daftar dulu ya!');
return redirect(base_url("login"));
}
if($quiz_data->is_registered == 1 && ($quiz_data->attempt == $test_taken['count']))
{
$this->session->set_flashdata('error', lang('test_already_given'));
return redirect(base_url());
}
$leader_bord_user_name = $this->session->leader_bord_user_name;
if(empty($leader_bord_user_name) && empty($this->user['id']))
{
$this->session->set_flashdata('error', lang('user_required'));
redirect(base_url(''));
}
if(empty($quiz_data))
{
$this->session->set_flashdata('error', lang('invalid_id'));
redirect(base_url('404_override'));
}
$checkUser = $this->db
->where('user_id', $this->user['id'])
->where('payment_status', 'succeeded')
->where('type', 'PACKAGES')
->where('due_date >=', date('Y-m-d'))
->get('payments')
->row();
if(!$checkUser){
if($quiz_data->price > 0)
{
$quiz_last_paymetn_status = $this->Payment_model->get_quiz_last_paymetn_status($quiz_id);
if(empty($quiz_last_paymetn_status) && empty($quiz_last_paypal_status))
{
return redirect(base_url("quiz-pay/payment-mode/$quiz_id"));
}
}
}
if($this->session->quiz_session)
{
$session_quiz_id = $this->session->quiz_session['quiz_data']['id'];
return redirect(base_url("test/$session_quiz_id/1"));
}
$this->set_title(lang('front_quiz_instruction'), $this->settings->site_name);
$content_data = array(
'Page_message' => lang('front_quiz_instruction'),
'page_title' => lang('front_quiz_instruction'),
'quiz_id' => $quiz_id,
'quiz_data' => $quiz_data,
'total_participants' => $this->TestModel->get_total_participants($quiz_id, $this->user['id']),
);
$data = $this->includes;
$data['content'] = $this->load->view('instruction', $content_data, TRUE);
$this->load->view($this->template, $data);
}
function category($category_slug=NULL, $page_no=NULL)
{
$quiz_filter = $this->input->get('most') ? $this->input->get('most') : '';
$session_quiz_data = array();
$session_quiz_question_data = array();
if($this->session->quiz_session)
{
$get_quiz_session = $this->session->quiz_session;
$session_quiz_data = $get_quiz_session['quiz_data'];
$session_quiz_question_data = $get_quiz_session['quiz_question_data'];
}and the view:
<?php
$quiz_running = 'no_quiz_start';
$session_quiz_id = NULL;
$this->db->where('payment_status', 'succeeded');
$this->db->where('item_price', '50000.00');
$this->db->where('quiz_id', 1);
//$this->db->where('quiz_id', 116);
$succeededpayment = $this->db->get('payments');
$gradients = array();
$gradients[] = 'mask gradient-vue';
$gradients[] = 'mask gradient-angular';
$gradients[] = 'mask gradient-react';
$gradients[] = 'mask gradient-material';
$gradients[] = 'mask gradient-html';
$gradients[] = 'mask gradient-laravel';
$gradients[] = 'mask gradient-react-native';
$gradients[] = 'mask gradient-nuxtjs';
if($session_quiz_data && $session_quiz_question_data)
{
$quiz_running = 'quiz_running';
$session_quiz_id = $session_quiz_data['id'];
echo "<input type='hidden' value='".$session_quiz_id."' class='session_quiz_id'>";
}
if($quiz_list_data)
{
foreach ($quiz_list_data as $quiz_array)
{
$price = $quiz_array->price > 1 ? '₹ '.$quiz_array->price : ' '.lang('free');
$start_quiz_link = $quiz_array->price > 1 ? '₹ '.$quiz_array->price : ' '.lang('free');
$quiz_id = $quiz_array->id;
$quiz_url = $session_quiz_id == $quiz_id ? base_url("test/$session_quiz_id/1") : base_url("instruction/$quiz_id");
$quiz_btn_name = $session_quiz_id == $quiz_id ? lang('resume_test') : lang("start_quiz");
if($quiz_url != base_url("test/$session_quiz_id/1") && $quiz_array->price > 1)
{
$quiz_url = base_url("quiz-pay/payment-mode/$quiz_id");
$quiz_btn_name = $quiz_btn_name != lang('resume_test') ? lang('pay_now') : $quiz_btn_name;
}
if($session_quiz_id == $quiz_id)
{
$quiz_running_btn = "";
}
else if($quiz_array->price > 1)
{
$quiz_running_btn = "";
}
else if($quiz_array->is_registered == 1)
{
$quiz_running_btn = "";
}
else
{
$quiz_running_btn = $quiz_running;
}
$lang_id = get_language_data_by_language($this->session->userdata('language'));
$translate_quiz_title = get_translated_column_value($lang_id,'quizes',$quiz_id,'title');
$quiz_title = $translate_quiz_title ? $translate_quiz_title : $quiz_array->title;
$quiz_title = strlen($quiz_title) > 40 ? substr($quiz_title,0,40)."..." : $quiz_title;
$quiz_user_name = $quiz_array->first_name.' '.$quiz_array->last_name;
$quiz_user_name = strlen($quiz_user_name) > 20 ? substr($quiz_user_name,0,20)."..." : $quiz_user_name;
?>
<div class="col-xs-6 col-sm-6 col-md-4 col-lg-3">
<div class="card card-bundle" data-turbolinks="false">
<div class="thumbnail">
<span class="maskk gradient-defaultt <?php echo xss_clean($gradients[mt_rand(0,7)]); ?>"> </span>
<a href="<?php echo xss_clean($quiz_url); ?>" data-url="<?php echo xss_clean($quiz_url); ?>" id="quiz_<?php echo xss_clean($quiz_array->id);?>" class="thumb-cover <?php echo xss_clean($quiz_running_btn); ?> statrt_quiz_btn" data-quiz_id="<?php echo xss_clean($quiz_array->id);?>" data-toggle="tooltip" title="<?php echo lang('start_quiz');?>"> </a>
<div class="details card-dark-shadow">
<div class="quiz_icons">
<div class="icon float-left">
<?php
if($quiz_array->price > 0)
{
echo '<span class=\'badge badge-warning text-premium\'><i class="far fa-money-bill-alt"></i> ' . $quiz_array->price . '</span>';
// $start_or_pay = lang('quiz_pay_now');
}
else
{
echo '<span class=\'badge badge-info\'><i class="far fa-bookmark"></i> Gratis</span>';
// $start_or_pay = lang('start_quiz');
}
?>
</div>
<!--
<a href="/javascript:void(0)" class="icon float-left text-white-im"><i class="fe fe-eye mr-1"></i>
<span class="value"><?php echo xss_clean($quiz_array->total_view);?></span></a>
<a href="/javascript:void(0)" class="icon inline-block ml-3 float-right like-quiz text-white-im">
<?php $like_or_not = (isset($quiz_array->like_id) && !empty($quiz_array->like_id) ? 'text-success' : 'text-muted');?>
<i class="fav_icon fas fa-heart <?php echo xss_clean($like_or_not);?> text-white-im" data-quiz_id="<?php echo xss_clean($quiz_array->id);?>"></i>
<?php $total_like = (!empty($quiz_array->total_like) && $quiz_array->total_like > 0 ? $quiz_array->total_like : "");?>
<span class="value like-quiz-count-<?php echo xss_clean($quiz_array->id);?>"><?php echo xss_clean($total_like);?></span>
</a> -->
<a href="/" class="icon float-right text-white-im"><i class="fas fa-cart-arrow-down mr-1"></i>
<span class="value"><?php echo $succeededpayment->num_rows(); ?></span></a>
</div>
<div class="framework-logo">
<p class="quiz_title"> <?php echo xss_clean($quiz_title); ?> </p>
<div class="row quiz_middle_icon">
<div class="col-6 text-center">
<i class="fas fa-book-open"> </i> <br>
<span class="value"><?php echo xss_clean($quiz_array->number_questions); ?> </span> <br>
Files
</div>
<div class="col-6 text-center">
<i class="fas fa-thumbs-up"></i> <br>
<span class="value"><?php echo xss_clean($total_like);?></span><br>
Like
</div>
</div>
</div> Quizmodel:
<?php defined('BASEPATH') OR exit('No direct script access allowed');
class QuizModel extends CI_Model
{
var $table = 'quizes';
var $column_order = array(null, 'title', 'category_title', 'number_questions', 'duration_min' , NULL);
var $column_search = array('title', 'category_title','number_questions','duration_min');
var $order = array('quizes.id' => 'DESC');
public function __construct() {
parent::__construct();
$this->load->database();
}
private function _get_datatables_query() {
$this->db->from($this->table);
$this->db->join('category', 'quizes.category_id = category.id', 'left');
$this->db->join('users', 'users.id = quizes.user_id', 'left');
$i = 0;
foreach ($this->column_search as $item) {
// if datatable send POST for search
if ($_POST['search']['value']) {
// first loop
if ($i === 0) {
// open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->group_start();
$this->db->like($item, $_POST['search']['value']);
} else {
$this->db->or_like($item, $_POST['search']['value']);
}
// last loop
if (count($this->column_search) - 1 == $i) {
// close bracket
$this->db->group_end();
}
}
$i++;
}
// here order processing
if (isset($_POST['order'])) {
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
} else if (isset($this->order)) {
$order = $this->order;
$this->db->order_by(key($order), $order[key($order) ]);
}
}
function get_quiz() {
$this->_get_datatables_query();
if ($_POST['length'] != - 1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->select('quizes.*,category_title')
->order_by('quizes.id', 'desc')
->get();
return $query->result();
}
function count_filtered() {
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all() {
$this->db->from($this->table);
return $this->db->count_all_results();
}Thank you good people for your help.
Right now, you're only getting the result once, for a single quiz with a hardcoded id, and then displaying that same result for all quizzes. You want to execute the query for each quiz separately using a different quiz_id.
For that, you need to move this query block ...
$this->db->where('payment_status', 'succeeded');
$this->db->where('item_price', '50000.00');
$this->db->where('quiz_id', 1);
//$this->db->where('quiz_id', 116);
$succeededpayment = $this->db->get('payments');
... to inside of the foreach loop, so that it is executed for each quiz. And then change $this->db->where('quiz_id', 1); to $this->db->where('quiz_id', $quiz_id); to get the result for the "current" quiz in the loop.
The view then looks like this:
<?php
$quiz_running = 'no_quiz_start';
$session_quiz_id = NULL;
$gradients = array();
$gradients[] = 'mask gradient-vue';
$gradients[] = 'mask gradient-angular';
$gradients[] = 'mask gradient-react';
$gradients[] = 'mask gradient-material';
$gradients[] = 'mask gradient-html';
$gradients[] = 'mask gradient-laravel';
$gradients[] = 'mask gradient-react-native';
$gradients[] = 'mask gradient-nuxtjs';
if($session_quiz_data && $session_quiz_question_data)
{
$quiz_running = 'quiz_running';
$session_quiz_id = $session_quiz_data['id'];
echo "<input type='hidden' value='".$session_quiz_id."' class='session_quiz_id'>";
}
if($quiz_list_data)
{
foreach ($quiz_list_data as $quiz_array)
{
$price = $quiz_array->price > 1 ? '₹ '.$quiz_array->price : ' '.lang('free');
$start_quiz_link = $quiz_array->price > 1 ? '₹ '.$quiz_array->price : ' '.lang('free');
$quiz_id = $quiz_array->id;
$quiz_url = $session_quiz_id == $quiz_id ? base_url("test/$session_quiz_id/1") : base_url("instruction/$quiz_id");
$quiz_btn_name = $session_quiz_id == $quiz_id ? lang('resume_test') : lang("start_quiz");
if($quiz_url != base_url("test/$session_quiz_id/1") && $quiz_array->price > 1)
{
$quiz_url = base_url("quiz-pay/payment-mode/$quiz_id");
$quiz_btn_name = $quiz_btn_name != lang('resume_test') ? lang('pay_now') : $quiz_btn_name;
}
if($session_quiz_id == $quiz_id)
{
$quiz_running_btn = "";
}
else if($quiz_array->price > 1)
{
$quiz_running_btn = "";
}
else if($quiz_array->is_registered == 1)
{
$quiz_running_btn = "";
}
else
{
$quiz_running_btn = $quiz_running;
}
$lang_id = get_language_data_by_language($this->session->userdata('language'));
$translate_quiz_title = get_translated_column_value($lang_id,'quizes',$quiz_id,'title');
$quiz_title = $translate_quiz_title ? $translate_quiz_title : $quiz_array->title;
$quiz_title = strlen($quiz_title) > 40 ? substr($quiz_title,0,40)."..." : $quiz_title;
$quiz_user_name = $quiz_array->first_name.' '.$quiz_array->last_name;
$quiz_user_name = strlen($quiz_user_name) > 20 ? substr($quiz_user_name,0,20)."..." : $quiz_user_name;
$this->db->where('payment_status', 'succeeded');
$this->db->where('item_price', '50000.00');
$this->db->where('quiz_id', $quiz_id);
//$this->db->where('quiz_id', 116);
$succeededpayment = $this->db->get('payments');
?>
<div class="col-xs-6 col-sm-6 col-md-4 col-lg-3">
<div class="card card-bundle" data-turbolinks="false">
<div class="thumbnail">
<span class="maskk gradient-defaultt <?php echo xss_clean($gradients[mt_rand(0,7)]); ?>"> </span>
<a href="<?php echo xss_clean($quiz_url); ?>" data-url="<?php echo xss_clean($quiz_url); ?>" id="quiz_<?php echo xss_clean($quiz_array->id);?>" class="thumb-cover <?php echo xss_clean($quiz_running_btn); ?> statrt_quiz_btn" data-quiz_id="<?php echo xss_clean($quiz_array->id);?>" data-toggle="tooltip" title="<?php echo lang('start_quiz');?>"> </a>
<div class="details card-dark-shadow">
<div class="quiz_icons">
<div class="icon float-left">
<?php
if($quiz_array->price > 0)
{
echo '<span class=\'badge badge-warning text-premium\'><i class="far fa-money-bill-alt"></i> ' . $quiz_array->price . '</span>';
// $start_or_pay = lang('quiz_pay_now');
}
else
{
echo '<span class=\'badge badge-info\'><i class="far fa-bookmark"></i> Gratis</span>';
// $start_or_pay = lang('start_quiz');
}
?>
</div>
<!--
<a href="/javascript:void(0)" class="icon float-left text-white-im"><i class="fe fe-eye mr-1"></i>
<span class="value"><?php echo xss_clean($quiz_array->total_view);?></span></a>
<a href="/javascript:void(0)" class="icon inline-block ml-3 float-right like-quiz text-white-im">
<?php $like_or_not = (isset($quiz_array->like_id) && !empty($quiz_array->like_id) ? 'text-success' : 'text-muted');?>
<i class="fav_icon fas fa-heart <?php echo xss_clean($like_or_not);?> text-white-im" data-quiz_id="<?php echo xss_clean($quiz_array->id);?>"></i>
<?php $total_like = (!empty($quiz_array->total_like) && $quiz_array->total_like > 0 ? $quiz_array->total_like : "");?>
<span class="value like-quiz-count-<?php echo xss_clean($quiz_array->id);?>"><?php echo xss_clean($total_like);?></span>
</a> -->
<a href="/" class="icon float-right text-white-im"><i class="fas fa-cart-arrow-down mr-1"></i>
<span class="value"><?php echo $succeededpayment->num_rows(); ?></span></a>
</div>
<div class="framework-logo">
<p class="quiz_title"> <?php echo xss_clean($quiz_title); ?> </p>
<div class="row quiz_middle_icon">
<div class="col-6 text-center">
<i class="fas fa-book-open"> </i> <br>
<span class="value"><?php echo xss_clean($quiz_array->number_questions); ?> </span> <br>
Files
</div>
<div class="col-6 text-center">
<i class="fas fa-thumbs-up"></i> <br>
<span class="value"><?php echo xss_clean($total_like);?></span><br>
Like
</div>
</div>
</div>