Welcome Guest, Not a member yet? Register   Sign In
MYSQL / Query Builder Class help
#1

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
Reply
#2

(This post was last modified: 08-05-2017, 08:54 PM by jarmen_kell.)

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
Reply
#3

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 Builder
If 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 
Reply
#4

(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
Reply
#5

(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 Builder
If 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
Reply
#6

(This post was last modified: 08-06-2017, 11:22 AM by neoraj3.)

(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 Builder
If 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; ?>
Reply
#7

(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.
Reply
#8

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(); 
Reply
#9

(This post was last modified: 08-13-2017, 07:01 AM by neoraj3.)

(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 Confused )

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>';




?>
Reply
#10

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 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB