CODEWITHSUNDEEP
phpmysqlcodeigniter
Anasbzr
Anasbzr

Reputation: 59

convert sql query to php codeigniter

I have been trying to get sum from my table. However i seem to get results with sql but not with CodeIgniter although i make sure the query is similar. i get no results and i don't know what exactly i'm doing wrong.

MySql query

SELECT SUM( amount_paid ) AS theSum
FROM invoice
WHERE MONTH( FROM_UNIXTIME( creation_timestamp ) ) =10
AND YEAR( FROM_UNIXTIME( creation_timestamp ) ) =2015

My CodeIgniter query 

<?php 
  echo $this->db->select('(SELECT SUM(amount_paid) FROM invoice WHERE MONTH (creation_timestamp` ) = 10) AS invoice');

  $query = $this->db->get('invoice'); 
?>

and I also tried

<div class="num" data-start="0" data-end="
 <?php 
  $this->db->select('SUM(amount_paid) as total');
  $this->db->where('creation_timestamp', 10);
  $q=$this->db->get('invoice');
  $row=$q->row();
  $total=$row->total;
 ?>"

  data-postfix="" data-duration="1500" data-delay="0">0</div>

  <h3><?php echo $total ;?></h3>
  <p>Total Payments</p>
</div>

MY schema

CREATE TABLE `invoice`(
 `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
 `student_id` int(11) NOT NULL,
 `title` longtext COLLATE utf8_unicode_ci NOT NULL,
 `description` longtext COLLATE utf8_unicode_ci NOT NULL,
 `amount` int(11) NOT NULL,
 `amount_paid` longtext COLLATE utf8_unicode_ci NOT NULL,
 `due` longtext COLLATE utf8_unicode_ci NOT NULL,
 `creation_timestamp` int(11) NOT NULL,
 `payment_timestamp` longtext COLLATE utf8_unicode_ci NOT NULL,
 `payment_method` longtext COLLATE utf8_unicode_ci NOT NULL,
 `payment_details` longtext COLLATE utf8_unicode_ci NOT NULL,
 `status` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'paid or unpaid',
 PRIMARY KEY (`invoice_id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Upvotes: 0

Views: 892

Answers (2)

Bilal
Bilal

Reputation: 2673

$this->db->select_sum("amount_paid", "theSum")
  ->from("invoice")
  ->where("MONTH( FROM_UNIXTIME( creation_timestamp ) )", 10)
  ->where("YEAR( FROM_UNIXTIME( creation_timestamp ) )", 2015);

$rec = $this->db->get();

Upvotes: 1

Nimatullah Razmjo
Nimatullah Razmjo

Reputation: 1961

According to your MySQL query,codeIgniter active record will work,

$this->db->select("SUM( amount_paid ) AS theSum");
$this->db->from("invoice");
$this->db->where("MONTH( FROM_UNIXTIME( creation_timestamp ) ) =10",null, true);
$this->db->where("YEAR( FROM_UNIXTIME( creation_timestamp ) ) =2015",null,true);
$rec = $this->db->get();

MySQL output will be :

SELECT SUM( amount_paid ) AS theSum 
FROM `invoice` 
WHERE MONTH( FROM_UNIXTIME( creation_timestamp ) ) = 10 
AND YEAR( FROM_UNIXTIME( creation_timestamp ) ) = 2015

Or, you can directy put your query inside active records like:

$rec = "SELECT SUM( amount_paid ) AS theSum
FROM invoice
WHERE MONTH( FROM_UNIXTIME( creation_timestamp ) ) =10
AND YEAR( FROM_UNIXTIME( creation_timestamp ) ) =2015";

$this->db->query($rec);

Upvotes: 1

Related Questions