+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: MYSQL / Query Builder Class help (/showthread.php?tid=68624)
MYSQL / Query Builder Class help - neoraj3 - 08-05-2017
Hi
I'm new to CI. I like MYSQL but I'm trying to adust to the "Query Builder" Class. I read some of the resources here Query Builder Class
How do I do a SELECT query that queries columns in 2 tables? eg SELECT t1.column1, table2.column2 from t1, t2 using Query Builder
Also If i dont want to use the Query Builder how do I rewrite the Model-View-Controller to use plain old MYSQL instead in the codeigniter News Section tutorial in this link ?
CI's News section example
Thanks
Neoraj3
RE: MYSQL / Query Builder Class help - jarmen_kell - 08-05-2017
Judging from the kind of help you're requesting now.
I suggest that you stop where you are right now,
and start learning basics of SELECT'ing queries.
because you're asking a questions about your query,
without even bothering to read the documentation.
or at least do some experiments for your sql query with CI's Query Builder,
and then ask here if you're really, really stuck
RE: MYSQL / Query Builder Class help - Wouter60 - 08-05-2017
Quote:How do I do a SELECT query that queries columns in 2 tables? eg SELECT t1.column1, table2.column2 from t1, t2 using Query BuilderIf you know how to do this in plain MySQL, you'll know that you need a JOIN statement.
Example in query builder, with tables "posts" and "comments":
PHP Code:
$this->db
->select ('t1.title, t2.comment_date')
->from('posts t1')
->join('comments t2','t2.post_id = t1.id')
->order_by('t2.comment_date','DESC');
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
To apply old style SQL, just do this:
PHP Code:
$sql = "SELECT * FROM posts ORDER BY id";
$records = $this->db->query($sql)->result();
//$records is an array of objects
RE: MYSQL / Query Builder Class help - neoraj3 - 08-06-2017
(08-05-2017, 08:53 PM)jarmen_kell Wrote: Judging from the kind of help you're requesting now.
I suggest that you stop where you are right now,
and start learning basics of SELECT'ing queries.
because you're asking a questions about your query,
without even bothering to read the documentation.
or at least do some experiments for your sql query with CI's Query Builder,
and then ask here if you're really, really stuck
Yes I do need to check the docs some more
RE: MYSQL / Query Builder Class help - neoraj3 - 08-06-2017
(08-05-2017, 11:45 PM)Wouter60 Wrote:Quote:How do I do a SELECT query that queries columns in 2 tables? eg SELECT t1.column1, table2.column2 from t1, t2 using Query BuilderIf you know how to do this in plain MySQL, you'll know that you need a JOIN statement.
Example in query builder, with tables "posts" and "comments":
PHP Code:$this->db
->select ('t1.title, t2.comment_date')
->from('posts t1')
->join('comments t2','t2.post_id = t1.id')
->order_by('t2.comment_date','DESC');
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
To apply old style SQL, just do this:
PHP Code:$sql = "SELECT * FROM posts ORDER BY id";
$records = $this->db->query($sql)->result();
//$records is an array of objects
Thanks very much Wouter60! I will try it
RE: MYSQL / Query Builder Class help - neoraj3 - 08-06-2017
(08-05-2017, 11:45 PM)Wouter60 Wrote:Quote:How do I do a SELECT query that queries columns in 2 tables? eg SELECT t1.column1, table2.column2 from t1, t2 using Query BuilderIf you know how to do this in plain MySQL, you'll know that you need a JOIN statement.
Example in query builder, with tables "posts" and "comments":
PHP Code:$this->db
->select ('t1.title, t2.comment_date')
->from('posts t1')
->join('comments t2','t2.post_id = t1.id')
->order_by('t2.comment_date','DESC');
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
To apply old style SQL, just do this:
PHP Code:$sql = "SELECT * FROM posts ORDER BY id";
$records = $this->db->query($sql)->result();
//$records is an array of objects
Ho Wouter60,
I took your advice so far. Can you check the MVC I have below and let know whats still wrong?
PHP Code:
MODEL::
<?php
public function get_news_stats($id){
$this->db->select('news.title, news_rating.rating');
$this->db->from('news');
$this->db->join('news_rating', 'news_rating.rating_id = news.id');
$this->db->where('id', $id);
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
}
?>
CONTROLLER::
<?php
public function index3($id)
{
$data['news_ratings2'] = $this->news_model->get_news_stats($id);
$data['title'] = 'NEWS STATS';/;
$this->load->view('templates/header', $data);
$this->load->view('news/index3', $data);
$this->load->view('templates/footer');
}
?>
VIEW::
<?php foreach ($news_ratings2 as $x): ?>
<?php echo $x['rating_id']; ?>
<?php echo $x['id']; ?>
<?php endforeach; ?>RE: MYSQL / Query Builder Class help - neoraj3 - 08-06-2017
(08-05-2017, 08:53 PM)jarmen_kell Wrote: Judging from the kind of help you're requesting now.
I suggest that you stop where you are right now,
and start learning basics of SELECT'ing queries.
because you're asking a questions about your query,
without even bothering to read the documentation.
or at least do some experiments for your sql query with CI's Query Builder,
and then ask here if you're really, really stuck
Thanks
I started looking at the Query Builder section but I have issues figuring out how to code the Controller and View to display. I have not coded in a while.
RE: MYSQL / Query Builder Class help - Wouter60 - 08-06-2017
Quote:Ho Wouter60,
I took your advice so far. Can you check the MVC I have below and let know whats still wrong?
What error message(s) do you get?
If you want to know how Query Builder has generated the query, put this piece of code before the return section of the function in your model (temporarily):
PHP Code:
echo $this->db->last_query();
die();
RE: MYSQL / Query Builder Class help - neoraj3 - 08-13-2017
(08-06-2017, 11:13 PM)Wouter60 Wrote:Quote:Ho Wouter60,
I took your advice so far. Can you check the MVC I have below and let know whats still wrong?
What error message(s) do you get?
If you want to know how Query Builder has generated the query, put this piece of code before the return section of the function in your model (temporarily):
PHP Code:echo $this->db->last_query();
die();
Wouter60,
Thanks for all the help again but let me close this thread one time. Based on other commenters on this post i have some more reading to do. But basically i was able to figure out how to do the Join using query() and writing the actually MySql.
I also did not need to write any code in the Model to access the data in the database (I hope this isnt a bad practice
)Basically this was my code for the INNER JOIN (my naming conventions are a bit weird but this is because i havent coded in a while...was more interested in figuring out how to do PHP_MYSQL using CI's MVC framework):
CONTROLLER::
PHP Code:
public function index4()
{
$query= $this->db->query('SELECT news_rating.rating, news.title
FROM news
INNER JOIN news_rating ON news.id = news_rating.rating_id');
$data['data2'] = $query->row();
$data['data3'] = $query->row_array();
$this->load->view('news/index4', $data);
}
VIEW::
PHP Code:
<h2> $this->db->row() PROPERLY</h2>
<?php
echo $data2->title .'<br>';
echo $data2->rating .'<br>';
?>
<h2> $this->db->row_array() as an array</h2>
<?php
echo $data3['title'] .'<br>';
echo $data3['rating'] .'<br>';
?>RE: MYSQL / Query Builder Class help - Wouter60 - 08-13-2017
The documentation about models starts with this remark:
Models are optionally available for those who want to use a more traditional MVC approach.
MVC stands for model - view - controller.
In CodeIgniter, models are optional, although they often bring a lot of benefits, especially if you need to perform the same database operations from different controllers.
The $this->db->query(...) function is OK also. I prefer the Query Builder, especially when I have to use where() clauses that depend on different situations.
Example in plain SQL:
PHP Code:
$sql = 'SELECT * FROM posts';
if ($condition1 == TRUE) {
$sql .= ' WHERE date >= "' . $date1 . '"';
}
if ($condition2 == TRUE) {
if ($condition1 == TRUE) {
$sql .= ' AND status="A"';
}
else {
$sql .= ' WHERE status="A"';
}
}
$query = $this->db->query($sql);
The same with Query Builder:
PHP Code:
if ($condition1) $this->db->where('date >=' , $date1);
if ($condition2) $this->db->where('status', 'A');
$query = $this->db->get('posts');
Do you notice the difference? Query Builder is definitely better here, way shorter and easier to understand.
The way you let CI return the result(s) (as objects or as arrays), is totally up to you. In most cases, I use objects, because it's easier. Compare $post->title to $post['title']. There is no good or bad here. Use the method that you find most convenient.
Remember this:
PHP Code:
$query->row(); //returns only one record, as an object
$query->row_array(); //returns only one record, as an array
$query->result(); //returns an array of (multiple) records; each record is an object
$query->result_array(); //returns an array of (multiple) records; each record is an array