CodeIgniter Forums
How to convert from Mysql query to Codeigniter syntax? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: How to convert from Mysql query to Codeigniter syntax? (/thread-19724.html)



How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-16-2009

[eluser]sovandy[/eluser]
Thank you for your replying to me. I'm glad to get your help.
I don know how to convert from Mysql query to Codeigniter syntax. Here is my query:

SELECT lib_books.boo_id,
COUNT(lib_books.boo_id)AS number ,
lib_books.boo_title
FROM lib_borrowings bo
INNER JOIN lib_books
ON lib_books.boo_id=bo.boo_id
GROUP BY lib_books.boo_id;

I get the result that I want, but it difficult to implement in Codeigniter
And how to return from Model to Controller?
I think it return Array.


Please reply me soon if possible!
Thank you,


How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-16-2009

[eluser]Thorpe Obazee[/eluser]
Code:
SELECT lib_books.boo_id,
    COUNT(lib_books.boo_id)AS number ,
    lib_books.boo_title
FROM lib_borrowings bo
INNER JOIN lib_books
ON lib_books.boo_id=bo.boo_id
GROUP BY lib_books.boo_id;

assuming that your sql statement is correct, it should look like this.

Code:
$this->db->select('lib_books.boo_id,  lib_books.boo_title');
$this->db->select('COUNT(lib_books.boo_id)AS number', FALSE);
$this->db->join('lib_books', 'lib_books.boo_id = bo.boo_id', 'inner');
$this->db->group_by('lib_books.boo_id');
$this->db->get('bo');



How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-16-2009

[eluser]sovandy[/eluser]
I have tested it,but it doesn't work.
$this->db->select('COUNT(lib_books.boo_id)AS number', FALSE);
$this->db->join('lib_books', 'lib_books.boo_id = lib_borrowings.boo_id', 'inner');
$this->db->group_by('lib_books.boo_id');
$count=$this->db->get('lib_borrowings');
echo $count;
I echo $count is just want to know what it return by that function.
it shows me the error:

A PHP Error was encountered

Severity: 4096

Message: Object of class CI_DB_mysql_result could not be converted to string

Filename: models/book_model.php

Line Number: 142


How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-16-2009

[eluser]slowgary[/eluser]
You still need to call ->result() or ->result_array() on the query. Also, you'd need to determine which field you want to echo, as echoing $count will try to echo an object, which is what's causing the error. So:
Code:
$count = $this->db->select(‘COUNT(lib_books.boo_id)AS number’, FALSE)->join(‘lib_books’, ‘lib_books.boo_id = lib_borrowings.boo_id’, ‘inner’)->group_by(‘lib_books.boo_id’)->get(‘lib_borrowings’)->result_array();
      echo $count['number'];

I think that should work. Also, please use the [ code ] tags when posting code.


How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-16-2009

[eluser]Thorpe Obazee[/eluser]
[quote author="sovandy" date="1245228132"]I have tested it,but it doesn't work.
$this->db->select('COUNT(lib_books.boo_id)AS number', FALSE);
$this->db->join('lib_books', 'lib_books.boo_id = lib_borrowings.boo_id', 'inner');
$this->db->group_by('lib_books.boo_id');
$count=$this->db->get('lib_borrowings');
echo $count;
I echo $count is just want to know what it return by that function.
it shows me the error:

A PHP Error was encountered

Severity: 4096

Message: Object of class CI_DB_mysql_result could not be converted to string

Filename: models/book_model.php

Line Number: 142[/quote]

read the user_guide


How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-17-2009

[eluser]Phil Sturgeon[/eluser]
Why do a GROUP BY just to get a count in? This should do the same thing.

Code:
$this->db->join('lib_books', 'lib_books.boo_id = lib_borrowings.boo_id', 'inner');
$count=$this->db->count_all_results('lib_borrowings');
echo $count;



How to convert from Mysql query to Codeigniter syntax? - El Forum - 06-17-2009

[eluser]Thorpe Obazee[/eluser]
[quote author="Phil Sturgeon" date="1245246172"]Why do a GROUP BY just to get a count in? This should do the same thing.

Code:
$this->db->join('lib_books', 'lib_books.boo_id = lib_borrowings.boo_id', 'inner');
$count=$this->db->count_all_results('lib_borrowings');
echo $count;
[/quote]

I believe he was trying to get some more information than but in the end, he actually didn't.