CodeIgniter Forums
A query in multiple tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: A query in multiple tables (/showthread.php?tid=12005)

A query in multiple tables - El Forum - 10-01-2008

[eluser]earlyriser[/eluser]
I have 3 tables
WINES: id, name, year.
WINE_COMMENTS: id, user_id, wine_id, rating.
USER: id, name.

I would like to have a view with the next information

------------------------------------
This is user.name page

(list withSmile
wines.name
wine_comments.rating
-------------------------------------

But I don't know how to make a query for this, because the query needs to know:
1. Who is the logged user to load only his wines
2. Relate in some way the wines_comments.wine_id with the wine.id

My current code shows the user's wines ratings, but it shows the wine id only and not the name because I don't know how to make the query.

Code:
//CONTROLLER
    function comments()
    {
        $data['title']= "Comment Title";    
        
        $this->db->where('user_id', getUserProperty('id'));
        $data['query']= $this->db->get('wine_comments');
        $this->load->view('wine_comment.php', $data);
    }

Code:
//VIEW
<head>
<title> <?=$title; ?> </title>
</head>
<body>
<h1><b>&lt;?=getUserName()?&gt;'s wines</b></h1>

&lt;?php foreach($query->result() as $row): ?&gt;
<h3> &lt;?=$row->wine_id?&gt; </h3>
<p> &lt;?=$row->rating?&gt; </p>
<hr>
&lt;?php endforeach; ?&gt;

&lt;/body&gt;
&lt;/html&gt;

I'm starting with CI and PHP, please be patient.

A query in multiple tables - El Forum - 10-01-2008

[eluser]Sumon[/eluser]
change your controller as

Code:
function comments()
{
    $data['title']= "Comment Title";    

    $SqlInfo="select w.name, c.rating from wines w, wine_comments c where w.id=c.wine_id AND  c.user_id = $logged_in_user_id";
    $query = $this->db->query($SqlInfo);
    $data['query'] = $query->row_array();

    $this->load->view('wine_comment.php', $data);
}


A query in multiple tables - El Forum - 10-01-2008

[eluser]ray73864[/eluser]
or if you want to keep with the approach you were on, use:

Code:
$this->db->select('w.name, c.rating');
$this->db->from('wines w');
$this->db->where('user_id', getUserProperty('id'));
$this->db->join('wine_comments wc','w.id = wc.wine_id');

$data['query']= $this->db->get();

either of the 2 ways will work fine, just depends on which way you want to go.

A query in multiple tables - El Forum - 10-01-2008

[eluser]earlyriser[/eluser]
Thanks for the help Sumon, but I could not be able to make it right.
I copy/pasted the code and I get a PHP error and a DB error.

The PHP error is "Message: Undefined variable: logged_in_user_id"

The DB error is
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
select w.name, c.rating from wines w, wine_comments c where w.id=c.wine_id AND c.user_id =


Then I changed your $SqlInfo line for
Code:
$SqlInfo="select w.name, c.rating from wines w, wine_comments c where w.id=c.wine_id AND  c.user_id = getUserProperty('id')";
because I am using FreakAuth and that is its way to get the user id and then I gont only the DB error.

Any hints?

A query in multiple tables - El Forum - 10-01-2008

[eluser]earlyriser[/eluser]
ray73864: I used your approach and I got:
Error Number: 1109
Unknown table 'c' in field list
SELECT w.name, c.rating FROM (`wines` w) JOIN `wine_comments` wc ON w.id = wc.wine_id WHERE `user_id` = '2'

I wonder what w, c and wc does mean.

Thanks

A query in multiple tables - El Forum - 10-01-2008

[eluser]earlyriser[/eluser]
It works now. Thanks for your help guys. The final code is:
Code:
$this->db->select('w.name, wc.rating');
$this->db->from('wines w');
$this->db->where('user_id', getUserProperty('id'));
$this->db->join('wine_comments wc','w.id = wc.wine_id');

$data['query']= $this->db->get();
$this->load->view('wine_comment.php', $data);


A query in multiple tables - El Forum - 10-01-2008

[eluser]Jon L[/eluser]
w and wc are table aliases. syntax is
Code:
SELECT ... FROM table1 t1
-OR-
Code:
SELECT ... FROM table1 AS t1

Cheers

A query in multiple tables - El Forum - 10-01-2008

[eluser]earlyriser[/eluser]
Yes, I understood that later. What I found strange about this way is a line like this
$this->db->select('w.name, wc.rating');

before a line like this
$this->db->from('wines w');

But now I got it. Thanks Jon L.